# SPECIFICATIONS/04_DATABASE/01_TABLES.md

**PURPOSE:** Defines the schema for every table in the system — columns, types, and schema-level constraints. This file answers "what does the database physically look like?" — it contains zero application-level model behavior.

> **ON DELETE rules and conceptual relationships:** `04_RELATIONSHIPS_AND_CONSTRAINTS.md`
> **Index definitions:** `03_INDEXES.md`
> **Application-level model behavior and scopes:** `../../CONSTITUTIONS/04_DATABASE_COMMUNICATION.md`
> **Business rules governing these entities:** `../01_BUSINESS_CONTEXT/03_BUSINESS_RULES.md`

**Conventions (canonical specification):** `../../CONSTITUTIONS/04_DATABASE_COMMUNICATION.md` → Schema Conventions:
- IDs: `BIGSERIAL PRIMARY KEY`
- Timestamps: `TIMESTAMPTZ` via `timestamps()`
- Monetary values: `DECIMAL(10,2)`
- Status columns: `VARCHAR` (not PostgreSQL ENUMs)

---

## clients

**Purpose:** Unified human entity table (Customers, Agents, Investors)

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `name` | `VARCHAR(150)` | `NOT NULL` | Person's name |
| `phone` | `VARCHAR(20)` | `NOT NULL`, `UNIQUE` | Phone number |
| `description` | `TEXT` | `NULLABLE` | Account description |
| `avatar` | `VARCHAR(255)` | `NULLABLE` | Relative path to avatar image |
| `reference_number` | `VARCHAR(20)` | `UNIQUE`, `NULLABLE` | Document reference (e.g., `CUS-0000000001`) |
| `client_type_flags` | `JSONB` | `NOT NULL`, `DEFAULT '[]'` | Role flags: `["customer"]`, `["agent", "investor"]` |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Record creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Last update date |

> **Role inference and flag management rules:** `../01_BUSINESS_CONTEXT/03_BUSINESS_RULES.md` → BR-001-3 through BR-001-6
> **Immutability enforcement:** BR-001-1. How to enforce: `../../CONSTITUTIONS/04_DATABASE_COMMUNICATION.md`

---

## admins

**Purpose:** System administrator accounts (separate from clients for RBAC security)

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `name` | `VARCHAR(100)` | `NOT NULL` | Admin name |
| `email` | `VARCHAR(150)` | `NOT NULL`, `UNIQUE` | Login email |
| `password` | `VARCHAR(255)` | `NOT NULL` | Bcrypt hashed password |
| `avatar` | `VARCHAR(255)` | `NULLABLE` | Relative path to avatar image |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Last update date |

> **RBAC configuration:** `../../CONSTITUTIONS/00_CONSTITUTION_INDEX.md` → Security Mandates

---

## contracts

**Purpose:** Installment agreements

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `customer_id` | `BIGINT` | `NOT NULL`, `FK(clients.id)` | The buyer |
| `agent_id` | `BIGINT` | `NULLABLE`, `FK(clients.id)` | The referrer (optional) |
| `product_name` | `VARCHAR(255)` | `NOT NULL` | Product name |
| `product_description` | `TEXT` | `NULLABLE` | Product description |
| `purchase_amount` | `DECIMAL(10,2)` | `NOT NULL` | Raw product price |
| `initial_payment` | `DECIMAL(10,2)` | `NOT NULL` | Down payment (can be 0) |
| `profit_percentage` | `DECIMAL(5,2)` | `NOT NULL` | Profit margin added |
| `total_after_profit` | `DECIMAL(10,2)` | `NOT NULL` | Total with profit |
| `months` | `SMALLINT` | `NOT NULL` | Number of installments |
| `monthly_installment_amount` | `DECIMAL(10,2)` | `NOT NULL` | Pre-calculated installment amount |
| `start_date` | `DATE` | `NOT NULL` | Payment start date |
| `status` | `VARCHAR(20)` | `NOT NULL`, `DEFAULT 'draft'` | `draft`, `active`, `completed` |
| `signed_at` | `TIMESTAMPTZ` | `NULLABLE` | When signed (draft → active) |
| `reference_number` | `VARCHAR(20)` | `UNIQUE`, `NULLABLE` | Document reference (e.g., `CTR-0000000001`) |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Last update date |

**CHECK Constraints:**
```sql
CHECK (initial_payment >= 0 AND initial_payment <= purchase_amount)
CHECK (months >= 1 AND months <= 60)
```

> **State machine and lifecycle rules:** `../03_ARCHITECTURE/02_STATE_MACHINES.md`, BR-002-1 through BR-002-8

**Note on FK naming:** Uses `customer_id` and `agent_id` (not `client_id`) to reflect the role in this contract context, despite both pointing to the `clients` table.

---

## installments

**Purpose:** Pre-generated installment state table

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `contract_id` | `BIGINT` | `NOT NULL`, `FK(contracts.id)` | Parent contract |
| `installment_number` | `SMALLINT` | `NOT NULL` | Sequential order (1, 2, 3...) |
| `due_date` | `DATE` | `NOT NULL` | Due date (never changes) |
| `amount` | `DECIMAL(10,2)` | `NOT NULL` | Required amount |
| `status` | `VARCHAR(20)` | `NOT NULL`, `DEFAULT 'pending'` | `pending`, `paid`, `overdue` |
| `paid_amount` | `DECIMAL(10,2)` | `NOT NULL`, `DEFAULT 0` | Actually paid (0 or equals amount) |
| `paid_at` | `TIMESTAMPTZ` | `NULLABLE` | When paid |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Status change date |

> **Rules:** BR-003-1 through BR-003-5

---

## payments

**Purpose:** Financial ledger (current truth)

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `contract_id` | `BIGINT` | `NOT NULL`, `FK(contracts.id)` | Parent contract |
| `installment_id` | `BIGINT` | `NULLABLE`, `FK(installments.id)` | Explicit link — only for `installment_payment` type |
| `amount` | `DECIMAL(10,2)` | `NOT NULL` | Transaction amount |
| `payment_date` | `DATE` | `NOT NULL` | Transaction date |
| `type` | `VARCHAR(30)` | `NOT NULL` | `company_payout`, `initial_payment`, `installment_payment` |
| `notes` | `TEXT` | `NULLABLE` | Administrative notes |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Record date (used for LIFO ordering) |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Last modification |

> **Rules:** BR-004-1 through BR-004-9
> **Payment types explained:** `04_DECISION_RECORDS.md` → ADR-037

---

## payment_audit_logs

**Purpose:** Immutable narrative record of all financial events

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `contract_id` | `BIGINT` | `NOT NULL`, `FK(contracts.id)` | Parent contract |
| `payment_id` | `BIGINT` | `NULLABLE`, `FK(payments.id)` | Related payment (becomes NULL on deletion) |
| `action_type` | `VARCHAR(20)` | `NOT NULL` | `create`, `update`, `delete` |
| `narrative_text` | `TEXT` | `NOT NULL` | Self-contained narrative (independent of payments table) |
| `old_values` | `JSONB` | `NULLABLE` | Snapshot before update |
| `new_values` | `JSONB` | `NULLABLE` | Snapshot after update |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Record date |

**Note:** No `updated_at` column — records are immutable after creation.

> **Narrative independence rule:** BR-004-9
> **Narrative generation patterns:** `../05_ALGORITHMS/05_NARRATIVE_GENERATION.md`

---

## client_shares_logs

**Purpose:** Historical share movement tracking for investors

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `client_id` | `BIGINT` | `NOT NULL`, `FK(clients.id)` | Investor (client with investor flag) |
| `shares_count` | `INT` | `NOT NULL` | Always positive; type determines sign |
| `transaction_type` | `VARCHAR(20)` | `NOT NULL` | `deposit` or `withdraw` |
| `acquisition_date` | `TIMESTAMPTZ` | `NOT NULL` | When investor acquired the shares |
| `status` | `VARCHAR(20)` | `NOT NULL`, `DEFAULT 'active'` | `active`, `deleted` |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | System record date (used for 30-day lock) |
| `updated_at` | `TIMESTAMPTZ` | `NULLABLE` | Modification date (null until deleted) |

> **Rules:** BR-005-1 through BR-005-10
> **Table rename rationale:** `04_DECISION_RECORDS.md` → ADR-061

---

## notification_templates

**Purpose:** Text templates with dynamic variables

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Unique identifier |
| `type` | `VARCHAR(50)` | `NOT NULL`, `UNIQUE` | Template key: `overdue`, `due_soon_1day`, `due_later`, `paid_receipt` |
| `body_template` | `TEXT` | `NOT NULL` | Text with `{{variable}}` placeholders |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Last update date |

**Note:** Standalone table — no foreign key relationships to any other table.

---

## daily_contract_metrics

**Purpose:** Daily snapshot of contract signing counts for analytics KPIs. Populated by `RefreshDailyMetricsJob`.

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `snapshot_date` | `DATE` | `UNIQUE`, `NOT NULL` | The date being measured |
| `signed_contracts_count` | `INTEGER` | `NOT NULL`, `DEFAULT 0` | Number of contracts signed on this date |

**Note:** No `created_at`/`updated_at` — this is a time-series table, not an entity table.

---

## daily_investment_metrics

**Purpose:** Daily snapshot of active shares count for analytics KPIs. Populated by `RefreshDailyMetricsJob`.

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `snapshot_date` | `DATE` | `UNIQUE`, `NOT NULL` | The date being measured |
| `active_shares_count` | `INTEGER` | `NOT NULL`, `DEFAULT 0` | Net active shares balance at end of day |

**Note:** No `created_at`/`updated_at` — this is a time-series table, not an entity table.

---

## daily_collection_metrics

**Purpose:** Daily snapshot of collection and overdue metrics for analytics KPIs and charts. Populated by `RefreshDailyMetricsJob`.

| Column | Type | Constraints | Description |
|:---|:---|:---|:---|
| `snapshot_date` | `DATE` | `UNIQUE`, `NOT NULL` | The date being measured |
| `due_installments_count` | `INTEGER` | `NOT NULL`, `DEFAULT 0` | Installments due on this date |
| `on_time_collected_count` | `INTEGER` | `NOT NULL`, `DEFAULT 0` | Payments collected on or before due date |
| `overdue_days_sum` | `DECIMAL(12,2)` | `NOT NULL`, `DEFAULT 0` | Total days late for late payments |
| `late_payments_count` | `INTEGER` | `NOT NULL`, `DEFAULT 0` | Number of late payments |
| `total_collected_amount` | `DECIMAL(15,2)` | `NOT NULL`, `DEFAULT 0` | Total installment payments collected |
| `newly_overdue_amount` | `DECIMAL(15,2)` | `NOT NULL`, `DEFAULT 0` | Total amount that became overdue on this date |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Record creation date |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Record last update date |

> **Refresh mechanism:** `../03_ARCHITECTURE/03_PERFORMANCE_STRATEGY.md` → Scheduled Refresh Commands