🏛️ Database Schema & Double-Entry Ledger

This document details the PostgreSQL 16 relational data model, financial chart of accounts, and asynchronous transactional outbox implementation powering the engine[cite: 3, 6].

🗺️ Relational Schema Model

erDiagram
    COMPLEXES ||--o{ UNITS : contains
    COMPLEXES ||--o{ ACCOUNTS : defines
    COMPLEXES ||--o{ VAULTS : manages
    UNITS ||--o{ ASSESSMENTS : billed_to
    ASSESSMENTS ||--o{ PAYMENT_INTENTS : settled_by
    PAYMENT_INTENTS ||--o{ LEDGER_ENTRIES : records
    PAYMENT_INTENTS ||--o{ OUTBOX_EVENTS : triggers
    ACCOUNTS ||--o{ LEDGER_ENTRIES : debits_credits
    VAULTS ||--o{ EXPENSES : pays

1. Database Extensions & Custom Enums

The engine uses pgcrypto for UUIDv4 key generation and strict PostgreSQL enum types to prevent invalid domain states[cite: 3]:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TYPE payment_status AS ENUM (
    'CREATED',
    'PROCESSING',
    'SETTLED',
    'FAILED',
    'RECONCILED',
    'REFUNDED'
);

CREATE TYPE payment_method AS ENUM (
    'CREDIT_DEBIT_CARD',
    'FAST_EFT'
);

CREATE TYPE assessment_status AS ENUM (
    'UNPAID',
    'PAID',
    'CANCELLED'
);

CREATE TYPE account_type AS ENUM (
    'ASSET',
    'LIABILITY',
    'REVENUE',
    'EXPENSE'
);

2. Core Entities & Multi-Tenant Tables

Residential Complexes (`complexes`)

Top-level multi-tenant container holding site metadata, tax identifiers, and merchant settlement accounts[cite: 3]:

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY DEFAULT gen_random_uuid()`	Unique complex identifier[cite: 3].
`name`	`VARCHAR(255)`	`NOT NULL`	Registered residence or complex name[cite: 3].
`tax_number`	`VARCHAR(50)`	`NULL`	Legal tax identification number[cite: 3].
`sub_merchant_id`	`VARCHAR(100)`	`NULL`	Payment gateway sub-merchant identifier[cite: 3].
`payout_iban`	`VARCHAR(34)`	`NOT NULL`	IBAN designated for settlement payouts[cite: 3].
`created_at`	`TIMESTAMPTZ`	`DEFAULT NOW()`	Registration timestamp[cite: 3].
`updated_at`	`TIMESTAMPTZ`	`DEFAULT NOW()`	Record modification timestamp[cite: 3].

Residential Units (`units`)

Apartments, residences, or commercial units within a complex[cite: 3]:

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY DEFAULT gen_random_uuid()`	Unique unit identifier[cite: 3].
`complex_id`	`UUID`	`NOT NULL REFERENCES complexes(id)`	Parent complex reference[cite: 3].
`block_name`	`VARCHAR(50)`	`NULL`	Block designation (e.g., `A`, `D`)[cite: 3, 5].
`unit_number`	`VARCHAR(20)`	`NOT NULL`	Unit / Apartment number (e.g., `12`, `13`)[cite: 3, 5].
`resident_name`	`VARCHAR(150)`	`NULL`	Primary tenant or owner name[cite: 3, 5].
`resident_phone`	`VARCHAR(20)`	`NULL`	Contact phone number[cite: 3].
`resident_email`	`VARCHAR(255)`	`NULL`	Digital invoice and receipt target email[cite: 3].

Unique Constraint: UNIQUE(complex_id, block_name, unit_number) guarantees no duplicate units exist inside the same property[cite: 3].

Monthly Assessments (`assessments`)

Periodic dues, maintenance fees, and capital improvement charges billed to specific units[cite: 3, 4]:

CREATE TABLE assessments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE RESTRICT,
    period VARCHAR(7) NOT NULL, -- Format: 'YYYY-MM' (e.g., '2026-08')
    base_amount NUMERIC(12, 2) NOT NULL CHECK (base_amount > 0),
    status assessment_status DEFAULT 'UNPAID',
    due_date DATE NOT NULL,
    description TEXT,
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(unit_id, period, description)
);

3. Payment Intents & Gateway Mapping

The payment_intents table tracks the payment session lifecycle, idempotency tokens, gateway transaction identifiers, and fee splits[cite: 3]:

CREATE TABLE payment_intents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE RESTRICT,
    idempotency_key VARCHAR(64) UNIQUE NOT NULL,
    merchant_oid VARCHAR(64) UNIQUE NOT NULL,
    method payment_method NOT NULL DEFAULT 'CREDIT_DEBIT_CARD',
    base_amount NUMERIC(12, 2) NOT NULL,
    service_fee NUMERIC(12, 2) NOT NULL,
    service_fee_tax NUMERIC(12, 2) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    status payment_status DEFAULT 'CREATED',
    gateway_reference VARCHAR(100),
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    settled_at TIMESTAMPTZ
);

CREATE INDEX idx_payment_intents_oid ON payment_intents(merchant_oid);

4. Double-Entry Bookkeeping Ledger

To maintain mathematical auditing and eliminate arbitrary balance edits, financial events use immutable balanced debit and credit entries[cite: 3, 6].

Chart of Accounts (`accounts`)

CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    complex_id UUID REFERENCES complexes(id),
    type account_type NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);

Immutable Ledger Entries (`ledger_entries`)

CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_intent_id UUID NOT NULL REFERENCES payment_intents(id),
    debit_account_id UUID NOT NULL REFERENCES accounts(id),
    credit_account_id UUID NOT NULL REFERENCES accounts(id),
    amount NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_ledger_intent ON ledger_entries(payment_intent_id);

Standard Accounting Transaction Pattern

For a dues collection of ₺1,500.00 with a ₺40.50 + ₺8.10 KDV platform service fee:

``` 1. Payment Clearing (Asset) DEBIT ₺1,548.60 └── Resident Assessment (Asset) CREDIT ₺1,500.00 └── Platform Fee Revenue (Revenue) CREDIT ₺40.50 └── VAT Payable (Liability) CREDIT ₺8.10