5.9 Database schema reference

This page is the first-cut DDL for the core entities across the platform. It is the schema an engineer would start with on day one — concrete column types, indexes, constraints, foreign keys.

The DDL is PostgreSQL 15+ flavoured. Adapt for managed-Postgres flavours (RDS, Aurora) as needed.

Each schema corresponds to a module from 5.2 Services and modules. Cross-schema access happens via service APIs, not direct joins.

Conventions

All tables have id BIGSERIAL PRIMARY KEY and created_at TIMESTAMPTZ NOT NULL DEFAULT now().
All mutable tables have updated_at TIMESTAMPTZ NOT NULL DEFAULT now() with a trigger.
All *_token columns hold opaque tokens; clear text lives in the PII vault.
All amounts are stored as NUMERIC(18, 2) in paise / rupees (we’ll standardise on rupees for clarity here).
All rates are NUMERIC(6, 4) (e.g., 0.1950 for 19.5%).
Money columns use NUMERIC, never FLOAT.
Timestamps are TIMESTAMPTZ (with timezone).
Soft-delete via deleted_at TIMESTAMPTZ NULL where applicable.
Audit triggers populate the audit_event table on every change.

Module: `borrower`

CREATE SCHEMA IF NOT EXISTS borrower;

CREATE TABLE borrower.borrower (
    id BIGSERIAL PRIMARY KEY,
    type TEXT NOT NULL CHECK (type IN (
        'individual','proprietorship','partnership','llp',
        'pvt_ltd','pub_ltd','huf','trust','society'
    )),
    display_name TEXT NOT NULL,
    primary_mobile_token TEXT NOT NULL,
    primary_email_token TEXT NULL,
    pan_token TEXT NULL,
    kyc_status TEXT NOT NULL DEFAULT 'pending'
        CHECK (kyc_status IN ('pending','in_progress','verified','rejected')),
    risk_category TEXT NOT NULL DEFAULT 'medium'
        CHECK (risk_category IN ('low','medium','high')),
    created_via_channel_id BIGINT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX borrower_pan_uq ON borrower.borrower(pan_token)
    WHERE pan_token IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX borrower_mobile_idx ON borrower.borrower(primary_mobile_token);

CREATE TABLE borrower.business_entity (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL REFERENCES borrower.borrower(id),
    legal_name TEXT NOT NULL,
    trade_name TEXT NULL,
    gstin_token TEXT NULL,
    udyam_no TEXT NULL,
    cin_or_llpin TEXT NULL,
    pan_token TEXT NOT NULL,
    date_of_incorporation DATE NULL,
    date_of_commencement DATE NULL,
    industry_nic_code TEXT NOT NULL,
    business_sub_category TEXT NULL,
    registered_address JSONB NOT NULL,
    principal_place_address JSONB NULL,
    operational_locations JSONB NULL,
    annual_turnover_self_declared NUMERIC(18,2) NULL,
    turnover_validated_from_gst NUMERIC(18,2) NULL,
    num_employees INTEGER NULL,
    business_vintage_years NUMERIC(4,1) NULL,
    working_capital_cycle_days_estimated INTEGER NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX business_entity_gstin_uq ON borrower.business_entity(gstin_token)
    WHERE gstin_token IS NOT NULL;
CREATE INDEX business_entity_borrower_idx ON borrower.business_entity(borrower_id);
CREATE INDEX business_entity_nic_idx ON borrower.business_entity(industry_nic_code);

CREATE TABLE borrower.promoter (
    id BIGSERIAL PRIMARY KEY,
    business_entity_id BIGINT NOT NULL REFERENCES borrower.business_entity(id),
    name TEXT NOT NULL,
    dob DATE NULL,
    gender TEXT NULL CHECK (gender IN ('M','F','O','U')),
    nationality TEXT NOT NULL DEFAULT 'IN',
    pan_token TEXT NULL,
    aadhaar_ref_token TEXT NULL,
    mobile_token TEXT NULL,
    email_token TEXT NULL,
    role TEXT NOT NULL CHECK (role IN ('director','partner','proprietor','authorised_signatory','karta','trustee')),
    shareholding_pct NUMERIC(5,2) NULL,
    permanent_address JSONB NULL,
    current_address JSONB NULL,
    kyc_status TEXT NOT NULL DEFAULT 'pending',
    kyc_method TEXT NULL,
    din_or_pin TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX promoter_entity_idx ON borrower.promoter(business_entity_id);
CREATE INDEX promoter_pan_idx ON borrower.promoter(pan_token);

CREATE TABLE borrower.beneficial_owner (
    id BIGSERIAL PRIMARY KEY,
    business_entity_id BIGINT NOT NULL REFERENCES borrower.business_entity(id),
    parent_bo_id BIGINT NULL REFERENCES borrower.beneficial_owner(id),
    bo_natural_person_promoter_id BIGINT NULL REFERENCES borrower.promoter(id),
    ownership_pct_direct NUMERIC(5,2) NOT NULL,
    ownership_pct_effective NUMERIC(5,2) NOT NULL,
    verification_status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX bo_entity_idx ON borrower.beneficial_owner(business_entity_id);

Module: `application`

CREATE SCHEMA IF NOT EXISTS application;

CREATE TABLE application.lead (
    id BIGSERIAL PRIMARY KEY,
    borrower_pan_token TEXT NULL,
    mobile_token TEXT NOT NULL,
    channel_id BIGINT NOT NULL,
    partner_id BIGINT NULL,
    sub_agent_id BIGINT NULL,
    campaign_id BIGINT NULL,
    utm_source TEXT NULL,
    utm_medium TEXT NULL,
    utm_campaign TEXT NULL,
    status TEXT NOT NULL DEFAULT 'new',
    stage TEXT NOT NULL DEFAULT 'lead',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX lead_mobile_idx ON application.lead(mobile_token);
CREATE INDEX lead_pan_idx ON application.lead(borrower_pan_token);

CREATE TABLE application.application (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    lead_id BIGINT NULL REFERENCES application.lead(id),
    status TEXT NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft','submitted','under_review','approved','declined','withdrawn','disbursed')),
    channel_id BIGINT NOT NULL,
    partner_id BIGINT NULL,
    sub_agent_id BIGINT NULL,
    loan_product_id BIGINT NOT NULL,
    loan_amount_sought NUMERIC(18,2) NOT NULL,
    tenure_sought INTEGER NOT NULL,
    purpose_code TEXT NOT NULL,
    decision_engine_decision_id BIGINT NULL,
    sanction_id BIGINT NULL,
    application_version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    submitted_at TIMESTAMPTZ NULL,
    decided_at TIMESTAMPTZ NULL
);
CREATE INDEX application_borrower_idx ON application.application(borrower_id);
CREATE INDEX application_status_idx ON application.application(status);
CREATE INDEX application_channel_idx ON application.application(channel_id, partner_id);

CREATE TABLE application.application_section_data (
    id BIGSERIAL PRIMARY KEY,
    application_id BIGINT NOT NULL REFERENCES application.application(id),
    section_name TEXT NOT NULL,
    data_json JSONB NOT NULL,
    last_saved_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (application_id, section_name)
);

CREATE TABLE application.existing_debt (
    id BIGSERIAL PRIMARY KEY,
    application_id BIGINT NOT NULL REFERENCES application.application(id),
    lender TEXT NOT NULL,
    outstanding NUMERIC(18,2) NOT NULL,
    emi NUMERIC(18,2) NULL,
    tenure INTEGER NULL,
    vintage_months INTEGER NULL,
    declared_or_bureau TEXT NOT NULL CHECK (declared_or_bureau IN ('declared','bureau','both'))
);

CREATE TABLE application.application_bank_account (
    id BIGSERIAL PRIMARY KEY,
    application_id BIGINT NOT NULL REFERENCES application.application(id),
    bank TEXT NOT NULL,
    ifsc TEXT NOT NULL,
    account_number_token TEXT NOT NULL,
    account_type TEXT NOT NULL,
    is_collection BOOLEAN NOT NULL DEFAULT false,
    is_disbursement BOOLEAN NOT NULL DEFAULT false,
    verified_via_penny_drop BOOLEAN NOT NULL DEFAULT false,
    penny_drop_at TIMESTAMPTZ NULL
);

CREATE TABLE application.consent (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    purpose TEXT NOT NULL,
    data_categories TEXT[] NOT NULL,
    granted_at TIMESTAMPTZ NOT NULL,
    expires_at TIMESTAMPTZ NULL,
    revoked_at TIMESTAMPTZ NULL,
    version_of_policy TEXT NOT NULL,
    ip TEXT NULL,
    device_fingerprint TEXT NULL,
    vendor TEXT NULL,
    vendor_consent_ref TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX consent_borrower_idx ON application.consent(borrower_id);
CREATE INDEX consent_purpose_idx ON application.consent(purpose);

Module: `kyc`

CREATE SCHEMA IF NOT EXISTS kyc;

CREATE TABLE kyc.kyc_record (
    id BIGSERIAL PRIMARY KEY,
    promoter_id BIGINT NOT NULL,
    method TEXT NOT NULL CHECK (method IN ('vcip','aadhaar_offline','digilocker','in_person')),
    ovd_type TEXT NULL,
    ovd_proof_id BIGINT NULL,
    ckyc_kin TEXT NULL,
    ckyc_uploaded_at TIMESTAMPTZ NULL,
    ckyc_status TEXT NULL,
    sanctions_screen_id BIGINT NULL,
    verified_at TIMESTAMPTZ NULL,
    next_refresh_due_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX kyc_promoter_idx ON kyc.kyc_record(promoter_id);
CREATE INDEX kyc_kin_idx ON kyc.kyc_record(ckyc_kin) WHERE ckyc_kin IS NOT NULL;

CREATE TABLE kyc.vcip_session (
    id BIGSERIAL PRIMARY KEY,
    kyc_record_id BIGINT NOT NULL REFERENCES kyc.kyc_record(id),
    vendor TEXT NOT NULL,
    started_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ NULL,
    outcome TEXT NULL,
    recording_uri TEXT NULL,
    geo_tag_lat NUMERIC(9,6) NULL,
    geo_tag_lon NUMERIC(9,6) NULL
);

CREATE TABLE kyc.sanctions_screen (
    id BIGSERIAL PRIMARY KEY,
    subject_type TEXT NOT NULL CHECK (subject_type IN ('person','entity')),
    subject_id BIGINT NOT NULL,
    vendor TEXT NOT NULL,
    screened_at TIMESTAMPTZ NOT NULL,
    hits JSONB NOT NULL DEFAULT '[]',
    disposition TEXT NULL CHECK (disposition IN ('clear','false_positive','refer','decline')),
    disposition_by BIGINT NULL,
    disposition_at TIMESTAMPTZ NULL
);

Module: `ingestion`

CREATE SCHEMA IF NOT EXISTS ingestion;

CREATE TABLE ingestion.data_pull (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    data_type TEXT NOT NULL CHECK (data_type IN ('bank','gst','mca','bureau','tally','aa','itr','udyam','invoice','marketplace')),
    vendor TEXT NOT NULL,
    consent_ref TEXT NULL,
    requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ NULL,
    status TEXT NOT NULL DEFAULT 'requested' CHECK (status IN ('requested','in_progress','completed','failed')),
    raw_object_uri TEXT NULL,
    parsed_object_uri TEXT NULL,
    error_message TEXT NULL
);
CREATE INDEX data_pull_borrower_idx ON ingestion.data_pull(borrower_id);
CREATE INDEX data_pull_type_idx ON ingestion.data_pull(data_type, status);

CREATE TABLE ingestion.bank_statement (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    account_ref TEXT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    parser_vendor TEXT NOT NULL,
    parser_version TEXT NOT NULL,
    parsed_metrics_json JSONB NOT NULL,
    transactions_object_uri TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ingestion.gst_report (
    id BIGSERIAL PRIMARY KEY,
    business_entity_id BIGINT NOT NULL,
    gstin TEXT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    pulled_at TIMESTAMPTZ NOT NULL,
    vendor TEXT NOT NULL,
    gstr1_metrics_json JSONB NULL,
    gstr3b_metrics_json JSONB NULL,
    gstr2a_metrics_json JSONB NULL
);

CREATE TABLE ingestion.bureau_report (
    id BIGSERIAL PRIMARY KEY,
    subject_type TEXT NOT NULL CHECK (subject_type IN ('person','entity')),
    subject_id BIGINT NOT NULL,
    cic TEXT NOT NULL CHECK (cic IN ('cibil','experian','equifax','crif')),
    report_type TEXT NOT NULL CHECK (report_type IN ('consumer','commercial','msme')),
    pulled_at TIMESTAMPTZ NOT NULL,
    score INTEGER NULL,
    parsed_object_uri TEXT NOT NULL,
    enquiry_id_at_cic TEXT NULL
);
CREATE INDEX bureau_subject_idx ON ingestion.bureau_report(subject_type, subject_id);

Module: `decisioning`

CREATE SCHEMA IF NOT EXISTS decisioning;

CREATE TABLE decisioning.policy (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    partner_id BIGINT NULL,
    version INTEGER NOT NULL,
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ NULL,
    rules_object_uri TEXT NOT NULL,
    created_by BIGINT NOT NULL,
    approved_by BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (product_id, partner_id, version)
);

CREATE TABLE decisioning.decision_run (
    id BIGSERIAL PRIMARY KEY,
    application_id BIGINT NOT NULL,
    policy_id BIGINT NOT NULL REFERENCES decisioning.policy(id),
    inputs_snapshot_uri TEXT NOT NULL,
    outputs_json JSONB NOT NULL,
    trace_uri TEXT NOT NULL,
    decision TEXT NOT NULL CHECK (decision IN ('approve','decline','refer','defer')),
    reason_codes TEXT[] NOT NULL,
    recommended_sanction_amount NUMERIC(18,2) NULL,
    recommended_tenure INTEGER NULL,
    recommended_rate NUMERIC(6,4) NULL,
    risk_grade TEXT NULL,
    started_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX decision_run_app_idx ON decisioning.decision_run(application_id);

CREATE TABLE decisioning.deviation (
    id BIGSERIAL PRIMARY KEY,
    decision_run_id BIGINT NOT NULL REFERENCES decisioning.decision_run(id),
    reason TEXT NOT NULL,
    requested_by BIGINT NOT NULL,
    requested_at TIMESTAMPTZ NOT NULL,
    approved_by BIGINT NULL,
    approved_at TIMESTAMPTZ NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','declined','withdrawn'))
);

Module: `lms` (event-sourced)

CREATE SCHEMA IF NOT EXISTS lms;

CREATE TABLE lms.loan_account (
    id BIGSERIAL PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    sanction_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    activated_at TIMESTAMPTZ NOT NULL,
    closed_at TIMESTAMPTZ NULL,
    current_outstanding NUMERIC(18,2) NOT NULL DEFAULT 0,
    current_classification TEXT NOT NULL DEFAULT 'standard',
    current_dpd_days INTEGER NOT NULL DEFAULT 0,
    last_event_at TIMESTAMPTZ NULL
);
CREATE INDEX loan_borrower_idx ON lms.loan_account(borrower_id);
CREATE INDEX loan_classification_idx ON lms.loan_account(current_classification);

CREATE TABLE lms.loan_event (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    event_type TEXT NOT NULL CHECK (event_type IN (
        'activate','disburse','accrual','repayment','allocation','charge',
        'bounce','classification_change','provision','restructure','writeoff','close'
    )),
    event_at TIMESTAMPTZ NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_payload_json JSONB NOT NULL,
    idempotency_key TEXT NOT NULL,
    UNIQUE (idempotency_key)
);
CREATE INDEX loan_event_loan_idx ON lms.loan_event(loan_id, event_at);
CREATE INDEX loan_event_type_idx ON lms.loan_event(event_type, event_at);

CREATE TABLE lms.schedule_line (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    due_date DATE NOT NULL,
    principal_due NUMERIC(18,2) NOT NULL,
    interest_due NUMERIC(18,2) NOT NULL,
    total_due NUMERIC(18,2) GENERATED ALWAYS AS (principal_due + interest_due) STORED,
    paid_principal NUMERIC(18,2) NOT NULL DEFAULT 0,
    paid_interest NUMERIC(18,2) NOT NULL DEFAULT 0,
    paid_at TIMESTAMPTZ NULL
);
CREATE INDEX schedule_loan_idx ON lms.schedule_line(loan_id, due_date);

CREATE TABLE lms.repayment (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    received_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(18,2) NOT NULL,
    source TEXT NOT NULL CHECK (source IN ('nach','enach','upi_autopay','bank_transfer','pg','qr','manual')),
    allocation_json JSONB NOT NULL,
    reconciled_with TEXT NULL,
    idempotency_key TEXT NOT NULL UNIQUE
);

CREATE TABLE lms.charge (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    type TEXT NOT NULL CHECK (type IN ('processing','servicing','late','bounce','penal','foreclosure','renewal','other')),
    amount NUMERIC(18,2) NOT NULL,
    levied_at TIMESTAMPTZ NOT NULL,
    gst_invoice_id BIGINT NULL
);

CREATE TABLE lms.classification_snapshot (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    snapshot_date DATE NOT NULL,
    class TEXT NOT NULL CHECK (class IN ('standard','sma_0','sma_1','sma_2','npa','sub','doubt','loss')),
    dpd_days INTEGER NOT NULL,
    principal_overdue NUMERIC(18,2) NOT NULL,
    interest_overdue NUMERIC(18,2) NOT NULL,
    UNIQUE (loan_id, snapshot_date)
);
CREATE INDEX classification_loan_idx ON lms.classification_snapshot(loan_id, snapshot_date DESC);
CREATE INDEX classification_class_idx ON lms.classification_snapshot(class, snapshot_date);

CREATE TABLE lms.provision (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    snapshot_date DATE NOT NULL,
    class TEXT NOT NULL,
    provision_amount NUMERIC(18,2) NOT NULL,
    UNIQUE (loan_id, snapshot_date)
);

Module: `colending`

CREATE SCHEMA IF NOT EXISTS colending;

CREATE TABLE colending.partner_lender (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    master_co_lending_agreement_ref TEXT NOT NULL,
    escrow_account_id BIGINT NOT NULL,
    mis_format_config JSONB NOT NULL DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','paused','exited')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE colending.partner_policy (
    id BIGSERIAL PRIMARY KEY,
    partner_lender_id BIGINT NOT NULL REFERENCES colending.partner_lender(id),
    version INTEGER NOT NULL,
    rules_object_uri TEXT NOT NULL,
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ NULL,
    UNIQUE (partner_lender_id, version)
);

CREATE TABLE colending.loan_allocation (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NOT NULL REFERENCES lms.loan_account(id),
    partner_lender_id BIGINT NOT NULL REFERENCES colending.partner_lender(id),
    originator_share_pct NUMERIC(5,2) NOT NULL,
    partner_share_pct NUMERIC(5,2) NOT NULL,
    originator_share_outstanding NUMERIC(18,2) NOT NULL DEFAULT 0,
    partner_share_outstanding NUMERIC(18,2) NOT NULL DEFAULT 0,
    booked_at TIMESTAMPTZ NOT NULL,
    UNIQUE (loan_id)
);
CREATE INDEX allocation_partner_idx ON colending.loan_allocation(partner_lender_id);

CREATE TABLE colending.dlg_agreement (
    id BIGSERIAL PRIMARY KEY,
    partner_lender_id BIGINT NOT NULL REFERENCES colending.partner_lender(id),
    pool_definition JSONB NOT NULL,
    dlg_amount NUMERIC(18,2) NOT NULL,
    instrument TEXT NOT NULL CHECK (instrument IN ('cash','fd','bg')),
    instrument_ref TEXT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    invocation_rules_json JSONB NOT NULL
);

CREATE TABLE colending.settlement (
    id BIGSERIAL PRIMARY KEY,
    partner_lender_id BIGINT NOT NULL REFERENCES colending.partner_lender(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    direction TEXT NOT NULL CHECK (direction IN ('to_partner','to_originator')),
    amount NUMERIC(18,2) NOT NULL,
    breakdown_json JSONB NOT NULL,
    executed_at TIMESTAMPTZ NULL,
    bank_ref TEXT NULL
);

Module: `accounting`

CREATE SCHEMA IF NOT EXISTS accounting;

CREATE TABLE accounting.gl_account (
    id BIGSERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('asset','liability','equity','income','expense'))
);

CREATE TABLE accounting.gl_posting (
    id BIGSERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    description TEXT NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    source_module TEXT NOT NULL,
    source_ref BIGINT NULL,
    entries_json JSONB NOT NULL
);
CREATE INDEX gl_posting_date_idx ON accounting.gl_posting(transaction_date);

CREATE TABLE accounting.gst_invoice (
    id BIGSERIAL PRIMARY KEY,
    loan_id BIGINT NULL,
    charge_id BIGINT NULL,
    borrower_id BIGINT NOT NULL,
    invoice_no TEXT NOT NULL UNIQUE,
    invoice_date DATE NOT NULL,
    taxable_value NUMERIC(18,2) NOT NULL,
    cgst NUMERIC(18,2) NOT NULL DEFAULT 0,
    sgst NUMERIC(18,2) NOT NULL DEFAULT 0,
    igst NUMERIC(18,2) NOT NULL DEFAULT 0,
    total NUMERIC(18,2) GENERATED ALWAYS AS (taxable_value + cgst + sgst + igst) STORED,
    hsn_code TEXT NOT NULL
);

Module: `audit`

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.audit_event (
    id BIGSERIAL PRIMARY KEY,
    actor_user_id BIGINT NULL,
    actor_service_id TEXT NULL,
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id TEXT NOT NULL,
    before_state_json JSONB NULL,
    after_state_json JSONB NULL,
    request_ip TEXT NULL,
    request_user_agent TEXT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
    prev_hash TEXT NOT NULL,
    this_hash TEXT NOT NULL
);
CREATE INDEX audit_resource_idx ON audit.audit_event(resource_type, resource_id);
CREATE INDEX audit_actor_idx ON audit.audit_event(actor_user_id, timestamp);
CREATE INDEX audit_time_idx ON audit.audit_event(timestamp);

Module: `admin`

CREATE SCHEMA IF NOT EXISTS admin;

CREATE TABLE admin.product (
    id BIGSERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE admin.role (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT NULL
);

CREATE TABLE admin.permission (
    id BIGSERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    description TEXT NULL
);

CREATE TABLE admin.role_permission (
    role_id BIGINT NOT NULL REFERENCES admin.role(id),
    permission_id BIGINT NOT NULL REFERENCES admin.permission(id),
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE admin.user (
    id BIGSERIAL PRIMARY KEY,
    sso_subject TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    display_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deactivated_at TIMESTAMPTZ NULL
);

CREATE TABLE admin.user_role (
    user_id BIGINT NOT NULL REFERENCES admin.user(id),
    role_id BIGINT NOT NULL REFERENCES admin.role(id),
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    assigned_by BIGINT NULL,
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE admin.feature_flag (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    enabled BOOLEAN NOT NULL DEFAULT false,
    rollout_pct NUMERIC(5,2) NULL,
    tenant_scope TEXT NULL,
    metadata_json JSONB NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Triggers and helpers

-- Generic updated_at trigger
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to every table with updated_at:
CREATE TRIGGER borrower_updated BEFORE UPDATE ON borrower.borrower
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ... repeat for every table with updated_at

-- Audit event hash chain trigger
CREATE OR REPLACE FUNCTION audit_event_hash()
RETURNS TRIGGER AS $$
DECLARE
    last_hash TEXT;
BEGIN
    SELECT this_hash INTO last_hash
      FROM audit.audit_event
      ORDER BY id DESC LIMIT 1;
    IF last_hash IS NULL THEN
        last_hash = repeat('0', 64);
    END IF;
    NEW.prev_hash = last_hash;
    NEW.this_hash = encode(
        digest(
            last_hash ||
            COALESCE(NEW.actor_user_id::text,'') ||
            NEW.action ||
            NEW.resource_type ||
            NEW.resource_id ||
            NEW.timestamp::text,
            'sha256'
        ),
        'hex'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_event_hash_chain BEFORE INSERT ON audit.audit_event
    FOR EACH ROW EXECUTE FUNCTION audit_event_hash();

Migration discipline

Migrations via Flyway (versioned: V0001__init.sql, V0002__add_kyc.sql, …).
Each migration covers a logical change; reviewable in PR.
Forward-only migrations preferred; rollback via new forward migration if needed.
Index creation in production uses CREATE INDEX CONCURRENTLY to avoid table lock.
Data migrations separated from schema migrations.
Test environment runs full migration history daily; failures are blockers.

What this DDL is and isn’t

It is the first-cut baseline — sufficient to launch with.
It is not optimised for any specific workload yet; production tuning comes after observing real traffic.
It does not include every table — see 5.3 Core data model for the conceptual entity list and add tables as needed for collections, documentation, mandates, settlements, vendor master.
It maps to the modular monolith pattern from 5.2 — one logical schema per module; cross-schema reads via service APIs only.

5.3 Core data model — conceptual entity catalogue.
5.6 Data platform and warehouse — how this OLTP feeds the warehouse via CDC.
5.7 Security, IAM, audit — PII tokenisation and audit-log integrity.

5.9 Database schema reference

Conventions

Module: borrower

Module: application

Module: kyc

Module: ingestion

Module: decisioning

Module: lms (event-sourced)

Module: colending

Module: accounting

Module: audit

Module: admin