# Spec: Audit Logging of KED/GSIS Service Calls (sync variant)

## 1. One table = one outbound call

Each row is created **before** the call (because the transaction/protocol numbers are embedded in the outbound request) and **updated** after it returns. One row = one HTTP exchange with KED.

```sql
CREATE TABLE ked_audit_records (
    id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- who / what
    user_id                UUID,                       -- your internal user, if the call is user-scoped
    audit_user_id          TEXT,                       -- ID or username of the user who initiated the action
    integration_name       TEXT NOT NULL,              -- 'E9', 'TaxRegistry', 'Deddie', 'GovGate', ...
    action_name            TEXT NOT NULL,              --  the methodName 'searchInfoByAfmElectrSupp', 'getInfoBySupply', ...
    request_id             TEXT NOT NULL,              -- X-Request-ID of the inbound request → join to log store

    -- KED audit header (generated here, embedded in the outbound message, persisted verbatim)
    audit_transaction_id   TEXT NOT NULL,              -- nextval('audit_tx_seq_<service>_<action>')
    audit_transaction_date TIMESTAMPTZ NOT NULL DEFAULT now(),
    audit_unit             TEXT NOT NULL,              -- ministry unit code from config
    audit_protocol         TEXT NOT NULL,              -- '<nextval(audit_protocol_seq)>/DD-MM-YYYY'
    audit_user_ip          TEXT NOT NULL,              -- requester IP (captured at entry)

    -- request (from job_calls.input)
    input                  JSONB CHECK (jsonb_typeof(input) = 'object'),

    -- outcome (from job_calls.status + observability, written after the call)
    outcome                TEXT CHECK (outcome IN ('SUCCESS','ERROR','LOST')),  -- NULL = still in flight
    http_status            INTEGER,
    error_code             TEXT,                       -- GSIS errorRecord code (transport OR business error)
    error_message          TEXT,                       -- truncated, e.g. 512 chars
    duration_ms            INTEGER CHECK (duration_ms >= 0),
    completed_at           TIMESTAMPTZ,

    created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE NULLS NOT DISTINCT (integration_name, action_name, audit_transaction_id)
);

CREATE INDEX ON ked_audit_records (user_id);
CREATE INDEX ON ked_audit_records (integration_name, action_name, created_at);
CREATE INDEX ked_audit_records_request_id_idx ON ked_audit_records (request_id);
CREATE INDEX ked_audit_records_created_brin ON ked_audit_records USING BRIN (created_at);
CREATE INDEX ked_audit_records_inflight_idx ON ked_audit_records (created_at)
    WHERE outcome IS NULL;                                -- find calls that never returned
```

## 2. Sequences (unchanged — KED requires them)

```sql
CREATE SEQUENCE audit_tx_seq_e9_searchInfoByAfmElectrSupp;   -- one per service+action
CREATE SEQUENCE audit_protocol_seq;                          -- one global
```

- `audit_transaction_id` = `nextval` of the per-action sequence; `audit_protocol` = `nextval(global) || '/' || to_char(now(),'DD-MM-YYYY')`; `audit_unit` from config.
- Centralize the `action → sequence` mapping in one registry (enum/table) and verify at startup that every registered action has its sequence — katoikia got bitten twice when actions were added/renamed without their sequence (`V17`, `V20` migrations).

## 3. Write lifecycle (synchronous)

```
1. resolve context        user, afm, requester IP, request_id
2. IN ONE TRANSACTION:     nextval both sequences → INSERT row (input, KED header, outcome NULL)
3. call KED                embed the 6 audit-header values in the outbound request;
                           MDC: extService, extOperation, requestId, afm
4. UPDATE the row          outcome='SUCCESS'|'ERROR', http_status, error_code (GSIS errorRecord!),
                           error_message, duration_ms, completed_at
5. log full response       INFO + marker (EXTERNAL_RESPONSE) with afm/requestId → log store
```

- **Still write-before-call**, even though you're synchronous: timeouts, dropped connections, and process kills mid-call still consume KED quota. KED's counters will include those attempts; your ledger must too. Rows stuck at `outcome IS NULL` past a threshold = lost calls (partial index above makes them a trivial query).
- Keep the INSERT and the UPDATE as **separate transactions** — the ledger records the attempt, not the result.
- Remember: GSIS REST services return HTTP 200 with an `errorRecord` body for business failures. Store that code in `error_code` and drive your flow from it, never from `http_status`.

## 4. Retention & operations

- This table is now your only DB evidence — **do not put raw response payloads in it** (size + personal data). Payloads live in the log store, joined via `request_id`; katoikia's practice of logging the full response at INFO with a stable marker is what made any payload recoverable years later.
- Decide the retention horizon up front (government audit requirement). At meaningful volume, partition by month (`PARTITION BY RANGE (created_at)`): retention becomes `DETACH`/`DROP PARTITION`. Trade-off: the UNIQUE constraint must then include `created_at` (no global unique across partitions) — decide whether tamper-evidence or cheap retention wins; you can keep uniqueness *approximately* enforced via the sequence itself (values come from a sequence, so duplicates can only be deliberate re-inserts).
- Everything stays append + one outcome update; no other writes.

## 5. Katoikia lessons already baked in above

`TEXT` instead of `VARCHAR(n)` (IP column was widened twice); no `ON DELETE CASCADE` from users (audit evidence must survive user deletion — keep `audit_user_id`  as the durable identifier); outcome/error columns present in the DB (so "how many E2 quota errors this month?" is plain SQL, not a Log Analytics query); `jsonb_typeof(input)='object'` guard.