Database Schema
Core Tables
companies
Multi-tenant root table.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| company_code | VARCHAR(20) | Unique code |
| company_name | VARCHAR(255) | Display name |
| base_currency | VARCHAR(3) | Functional currency |
| fiscal_year_start_month | INTEGER | FY start month |
users
System users.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| VARCHAR(255) | Unique email | |
| password_hash | VARCHAR(255) | Bcrypt hash |
| is_active | BOOLEAN | Soft delete |
company_users
User-company membership with roles.
| Column | Type | Description |
|---|---|---|
| company_id | UUID | FK to companies |
| user_id | UUID | FK to users |
| role | VARCHAR(20) | ADMIN, ACCOUNTANT, etc. |
chart_of_accounts
Chart of accounts with hierarchy.
| Column | Type | Description |
|---|---|---|
| company_id | UUID | FK to companies |
| code | VARCHAR(20) | Account code (numeric) |
| name | VARCHAR(255) | Account name |
| account_type | VARCHAR(20) | ASSET, LIABILITY, etc. |
| normal_balance | VARCHAR(10) | DEBIT or CREDIT |
| parent_id | UUID | Self-ref for hierarchy |
| is_cash_equivalent | BOOLEAN | For cash flow statement |
journal_entries
Journal entry headers.
| Column | Type | Description |
|---|---|---|
| company_id | UUID | FK to companies |
| journal_number | VARCHAR(50) | Auto-generated |
| journal_date | DATE | Transaction date |
| status | VARCHAR(20) | DRAFT, POSTED, etc. |
| total_debit | DECIMAL(19,4) | Must equal total_credit |
| total_credit | DECIMAL(19,4) | Must equal total_debit |
| fiscal_period_id | UUID | FK to fiscal_periods |
journal_lines
Journal entry line items.
| Column | Type | Description |
|---|---|---|
| journal_entry_id | UUID | FK to journal_entries |
| account_id | UUID | FK to chart_of_accounts |
| debit_amount | DECIMAL(19,4) | Debit (0 if credit) |
| credit_amount | DECIMAL(19,4) | Credit (0 if debit) |
| cost_center_id | UUID | For P&L accounts |
fiscal_periods
Monthly/quarterly periods.
| Column | Type | Description |
|---|---|---|
| company_id | UUID | FK to companies |
| fiscal_year | INTEGER | Year |
| period_number | INTEGER | 1-12 |
| status | VARCHAR(20) | OPEN, CLOSED |
documents
Source document metadata.
| Column | Type | Description |
|---|---|---|
| company_id | UUID | FK to companies |
| filename | VARCHAR(255) | Original filename |
| sha256_hash | VARCHAR(64) | Content hash |
| storage_path | VARCHAR(500) | S3 path |
| journal_entry_id | UUID | Linked journal |
audit_log
Complete audit trail.
| Column | Type | Description |
|---|---|---|
| entity_type | VARCHAR(50) | journal_entry, account |
| entity_id | UUID | Related entity |
| action | VARCHAR(20) | CREATE, POST, REVERSE |
| old_values | JSONB | Previous state |
| new_values | JSONB | New state |
| changed_by | UUID | User who made change |