erDiagram %% ===================== CORE LEDGER ===================== account_type { smallint id PK varchar name char normal_balance timestamptz created_at } accounts { bigint account_id PK varchar account_code varchar account_name bigint parent_account_id FK "self-referencing, recursive" smallint account_type_id FK boolean is_control_account boolean is_postable char currency_code varchar cash_flow_category boolean is_active timestamptz created_at } fiscal_year { int fiscal_year_id PK varchar year_name date start_date date end_date boolean is_closed timestamptz created_at } accounting_period { int period_id PK int fiscal_year_id FK smallint period_number date start_date date end_date integer status "PeriodStatus enum: Open=1,SoftClose=2,Locked=3,Closed=4 (converted from varchar 2026-07-18)" boolean lock_gl varchar lock_reason timestamptz created_at } period_lock_log { bigint log_id PK int period_id FK integer action "PeriodLockAction enum (converted from varchar 2026-07-18)" varchar module_locked varchar reason timestamptz created_at } journal_entry { bigint journal_id PK varchar journal_no int period_id FK date entry_date varchar journal_type varchar source_module varchar reference text narration "approved addition, Phase 1 build 2026-07-17 - not in the original ER diagram/design doc" boolean is_posted boolean is_reversed bigint reversal_of_id FK "self-referencing" integer entry_status "JournalEntryStatus enum: Draft=1,Posted=2,Reversed=3 (converted from varchar 2026-07-18)" varchar prepared_by timestamptz created_at } journal_entry_line { bigint line_id PK bigint journal_id FK bigint account_id FK numeric debit_amount numeric credit_amount char currency_code FK numeric exchange_rate numeric base_debit numeric base_credit bigint cost_center_id FK text memo timestamptz created_at } journal_correction { bigint correction_id PK bigint original_journal_id FK bigint correction_journal_id FK integer correction_type "JournalCorrectionType enum: FullReversal=1,PartialAdjustment=2 (converted from varchar 2026-07-18)" text reason varchar corrected_by timestamptz created_at } cost_center { bigint cost_center_id PK varchar code varchar name bigint parent_cost_center_id FK "self-referencing, recursive" boolean is_active timestamptz created_at } %% ===================== FIXED ASSETS ===================== fixed_asset { bigint asset_id PK varchar asset_code varchar asset_name bigint asset_account_id FK bigint accum_depr_account_id FK bigint depr_expense_account_id FK bigint gain_loss_account_id FK "added Phase 21/22 build 2026-07-18 - not in the original design doc; needed to post a balanced disposal entry" date acquisition_date numeric acquisition_cost numeric residual_value int useful_life_months integer depreciation_method "DepreciationMethod enum: StraightLine=1,ReducingBalance=2 (converted from varchar 2026-07-18)" numeric depreciation_rate_percent "nullable; added Phase 21/22 build 2026-07-18, required only when depreciation_method=ReducingBalance" integer status "FixedAssetStatus enum: Active=1,Disposed=2,FullyDepreciated=3 (converted from varchar 2026-07-18)" timestamptz created_at } depreciation_schedule { bigint schedule_id PK bigint asset_id FK int period_id FK numeric depreciation_amount numeric accumulated_depreciation numeric net_book_value bigint journal_id FK timestamptz created_at } asset_disposal { bigint disposal_id PK bigint asset_id FK date disposal_date numeric proceeds numeric net_book_value_at_disposal numeric gain_loss bigint journal_id FK timestamptz created_at } %% ===================== BANK & CASH ===================== bank_account { bigint bank_account_id PK varchar account_name varchar bank_name varchar account_number bigint gl_account_id FK char currency_code FK timestamptz created_at } bank_statement_line { bigint stmt_line_id PK bigint bank_account_id FK date txn_date varchar description numeric amount boolean is_reconciled bigint matched_journal_id FK timestamptz created_at } %% ===================== TAX CONFIGURATION ===================== tax_type { int tax_type_id PK varchar code varchar name timestamptz created_at } tax_code { bigint tax_code_id PK int tax_type_id FK varchar code varchar name numeric rate_percent integer calculation_basis "TaxCalculationBasis enum: Exclusive=1,Inclusive=2 (converted from varchar 2026-07-18)" integer applies_to "TaxAppliesTo enum: Sales=1,Purchase=2,Both=3 (converted from varchar 2026-07-18)" bigint output_tax_account_id FK bigint input_tax_account_id FK date effective_from date effective_to boolean is_active timestamptz created_at } %% ===================== CURRENCY CONFIGURATION ===================== currency { char currency_code PK varchar currency_name varchar symbol smallint decimal_places boolean is_base_currency boolean is_active timestamptz created_at } exchange_rate { bigint rate_id PK char currency_code FK date rate_date numeric rate_to_base varchar rate_source timestamptz created_at } currency_rounding_rule { char currency_code PK,FK varchar rounding_method bigint rounding_account_id FK timestamptz created_at } %% ===================== SYSTEM CONFIG ===================== system_config { varchar config_key PK varchar config_value text description boolean is_editable boolean is_locked timestamptz created_at } %% ===================== BUDGETING ===================== budget { bigint budget_id PK int fiscal_year_id FK varchar name timestamptz created_at } budget_line { bigint budget_line_id PK bigint budget_id FK bigint account_id FK int period_id FK bigint cost_center_id FK numeric budgeted_amount timestamptz created_at } %% ===================== MANUAL ENTRY / RECURRING ===================== recurring_journal_template { bigint template_id PK varchar template_name integer recurrence_pattern "RecurrencePattern enum: Monthly=1,Quarterly=2,Annual=3 (converted from varchar 2026-07-18)" date next_run_date date end_date boolean is_active timestamptz created_at } recurring_journal_template_line { bigint line_id PK bigint template_id FK bigint account_id FK numeric debit_amount numeric credit_amount timestamptz created_at } %% ===================== AUDIT & CHANGE TRACKING ===================== audit_log { bigint audit_log_id PK integer category "AuditCategory enum: Ledger=1,Administrative=2 - decided at first use 2026-07-18, never a varchar in this table" varchar entity_name varchar entity_id "loose reference, no FK - spans every table by design" integer action "AuditAction enum: Create=1,Update=2,Post=3,Reverse=4,Adjust=5,Lock=6,Unlock=7,SoftClose=8,Close=9" text description varchar performed_by timestamptz occurred_at } %% ===================== RELATIONSHIPS ===================== account_type ||--o{ accounts : "classifies" accounts ||--o{ accounts : "parent_account_id (recursive)" accounts ||--o{ journal_entry_line : "posted to" accounts ||--o{ fixed_asset : "asset/accum-depr/depr-expense/gain-loss accounts" accounts ||--o{ bank_account : "gl_account_id" accounts ||--o{ tax_code : "output/input tax account" accounts ||--o{ budget_line : "account_id" accounts ||--o{ recurring_journal_template_line : "account_id" accounts ||--o{ currency_rounding_rule : "rounding_account_id" fiscal_year ||--o{ accounting_period : "contains" fiscal_year ||--o{ budget : "budgeted for" accounting_period ||--o{ journal_entry : "posted within" accounting_period ||--o{ period_lock_log : "lock history" accounting_period ||--o{ depreciation_schedule : "period_id" accounting_period ||--o{ budget_line : "period_id" journal_entry ||--o{ journal_entry_line : "has lines" journal_entry ||--o{ journal_entry : "reversal_of_id (self-referencing)" journal_entry ||--o{ journal_correction : "original / correction" journal_entry ||--o| depreciation_schedule : "journal_id" journal_entry ||--o| asset_disposal : "journal_id" journal_entry ||--o| bank_statement_line : "matched_journal_id" cost_center ||--o{ cost_center : "parent_cost_center_id (recursive)" cost_center ||--o{ journal_entry_line : "tags" cost_center ||--o{ budget_line : "cost_center_id" fixed_asset ||--o{ depreciation_schedule : "scheduled" fixed_asset ||--o{ asset_disposal : "disposed via" bank_account ||--o{ bank_statement_line : "statement lines" tax_type ||--o{ tax_code : "defines" currency ||--o{ exchange_rate : "rated" currency ||--o| currency_rounding_rule : "rounding rule" currency ||--o{ accounts : "currency_code" currency ||--o{ bank_account : "currency_code" currency ||--o{ journal_entry_line : "currency_code" budget ||--o{ budget_line : "line items" recurring_journal_template ||--o{ recurring_journal_template_line : "line items"