System Architecture Specifications

[FIELD OPERATOR / MOBILE CLIENT]
      │
      ├──> (1) User Records Contribution (No Internet Required)
      │         │
      │         ▼
      ├──> [Embedded SQLite Database] (ACID Local Commit)
      │         │
      │         ├── Writes to: ledger_entries
      │         └── Appends to: sync_queue (status='PENDING')
      │
      ▼
[BACKGROUND SYNC WORKER]
      │
      ├──> (2) Detects Cellular Connectivity (2G/3G/4G/WiFi)
      ├──> (3) Batches Delta Entries with Device Monotonic Sequence
      │         │
      │         ▼ HTTPS / TLS 1.3
      ▼
[ASABIYYAH INGESTION EDGE API]
      │
      ├──> (4) Validates Cryptographic Device Token
      ├──> (5) Executes Idempotent Ingestion Transaction
      │         │
      │         ▼
      ├──> [Central PostgreSQL 16 Cluster]
      │         │
      │         └── Relational Constraint Check: SUM(debit) == SUM(credit)
      │
      ▼
(6) Returns ACK Packet -> Mobile Client Clears sync_queue

-- Relational Double-Entry Enforcement Table
CREATE TABLE ledger_entries (
    entry_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES transactions(id),
    account_id UUID NOT NULL REFERENCES accounts(id),
    entry_type VARCHAR(6) NOT NULL CHECK (entry_type IN ('DEBIT', 'CREDIT')),
    amount_kobo BIGINT NOT NULL CHECK (amount_kobo > 0),
    currency VARCHAR(3) NOT NULL DEFAULT 'NGN',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Invariant Enforcement: Sum of debits == Sum of credits
CREATE OR REPLACE FUNCTION verify_transaction_balance() 
RETURNS TRIGGER AS $$
DECLARE
    debit_sum BIGINT;
    credit_sum BIGINT;
BEGIN
    SELECT COALESCE(SUM(amount_kobo), 0) INTO debit_sum 
    FROM ledger_entries WHERE transaction_id = NEW.transaction_id AND entry_type = 'DEBIT';

    SELECT COALESCE(SUM(amount_kobo), 0) INTO credit_sum 
    FROM ledger_entries WHERE transaction_id = NEW.transaction_id AND entry_type = 'CREDIT';

    IF debit_sum != credit_sum THEN
        RAISE EXCEPTION 'Transaction imbalance: Debits (%) != Credits (%)', debit_sum, credit_sum;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;