Audit trigger waves 5b and 5c

​

What this wave attached

​

The finding that shaped every deferral: org_col has no join

organization_id = ${query.organizationId}

​

Per-table decision

table                                | decision | wave | sens       | cols  | visible  | org       | user?  | B/row    | lat us       | class   | rows/org/d  | MB/day    | GB/yr    | GB/7yr
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ledger_items                         | ATTACH   | 5b   | OPEN       |    24 |       24 | direct    | no     |      933 | 42.5-61.2    | MEDIUM  |          15 |       7.0 |     2.55 |     17.9
transactions                         | ATTACH   | 5b   | OPEN       |    31 |       28 | direct    | no     |     1129 | 51.4-74.0    | MEDIUM  |          10 |       5.6 |     2.06 |     14.4
crew_offers                          | ATTACH   | 5b   | OPEN       |    23 |       23 | direct    | no     |      901 | 41.0-59.1    | MEDIUM  |          12 |       5.4 |     1.97 |     13.8
crew_offer_days                      | ATTACH   | 5c   | OPEN       |    11 |       11 | parent-1  | no     |      626 | 43.0-55.5    | MEDIUM  |          20 |       6.3 |     2.28 |     16.0
crew_offer_recipients                | ATTACH   | 5c   | OPEN       |    12 |       12 | parent-1  | yes    |      659 | 44.5-57.7    | HIGH    |          80 |      26.4 |     9.62 |     67.3
crew_offer_recipient_days            | DEFER    | -    | OPEN       |     9 |        9 | parent-2  | no     |      590 | 62.9-74.7    | HIGH    |         150 |      44.2 |    16.15 |    113.0
Event                                | DEFER    | -    | OPEN       |    41 |       41 | direct    | no     |     1616 | 73.6-106.0   | HIGH    |          60 |      48.5 |    17.70 |    123.9
Invitation                           | ATTACH   | 5c   | SENSITIVE  |    20 |       17 | parent-1  | yes    |      734 | 47.9-62.6    | MEDIUM  |          40 |      14.7 |     5.36 |     37.5
EventMember                          | ATTACH   | 5c   | OPEN       |    11 |       11 | parent-1  | no     |      727 | 47.6-62.2    | MEDIUM  |          35 |      12.7 |     4.64 |     32.5
organization_documents               | ATTACH   | 5b   | SENSITIVE  |    11 |        9 | direct    | no     |      582 | 26.5-38.2    | LOW     |           2 |       0.6 |     0.21 |      1.5
availability_request_recipient_days  | DEFER    | -    | OPEN       |     8 |        8 | parent-2  | no     |      589 | 62.8-74.7    | HIGH    |         100 |      29.5 |    10.76 |     75.3

Assumed active orgs: 500
Retention window: 7 years
Fixed audit_log row overhead (derived from the 1098 B / 16-column wave 5a baseline): 384 B

ATTACHED tables (8): 28.71 GB/year, 200.9 GB over 7 years
  wave 5b (4): 6.80 GB/year, 47.6 GB over 7 years
  wave 5c (4): 21.90 GB/year, 153.3 GB over 7 years
DEFERRED tables (3), if attached as-is: 44.60 GB/year, 312.2 GB over 7 years
  +crew_offer_recipient_days: +16.15 GB/year, +113.0 GB over 7 years
  +Event: +17.70 GB/year, +123.9 GB over 7 years
  +availability_request_recipient_days: +10.76 GB/year, +75.3 GB over 7 years

​

Attached

• ledger_items — organization_id resolves directly. Every column is financial evidence (OPEN, denyColumns: []); the wave 5b integration test asserts an INSERT and an UPDATE both produce AUTO rows with redacted_fields: [] — nothing here is silently hidden.
• transactions — organization_id resolves directly. Denies cleo_contact_id, cleo_funding_source_id, cleo_submit_claim_token (the identity-linkage / bearer-credential split documented in auditClassification.ts’s file header); keeps every Stripe/Cleo reconciliation id.
• crew_offers — organization_id resolves directly. Money (fee) and access (who is offered a shift) risk, moderate assumed write rate.
• organization_documents — organization_id resolves directly. SENSITIVE, allow-list model; blob_name denied so an audit row can never hand out a live Azure Blob path. The wave 5b integration test asserts blob_name is absent from before_state/after_state on both INSERT and UPDATE and appears in redacted_fields.

​

Deferred in wave 5b — no organization_id column (resolved in 5c)

SELECT organization_id FROM crew_offers WHERE id = ($1 ->> 'offer_id')

SELECT co.organization_id
  FROM crew_offer_days cd
  JOIN crew_offers co ON co.id = cd.offer_id
 WHERE cd.id = ($1 ->> 'offer_day_id')

​

Deferred — Event

​

Wave 5c: parent-resolved organization

• Config. audit.table_config gains org_parent_path text[] — flattened (fk_col, parent_table, parent_key) triples, one per hop, child first — and org_parent_col, the org column on the last hop’s table. The manifest field is orgParent in src/db/auditClassification.ts ({ hops: [{ foreignKey, table, key }], orgColumn }), and the seed generator emits both columns (20260923140100_audit_table_config_seed).
• Compile, don’t store SQL. Unlike scope_sql, the config never carries free-form SQL. audit.org_parent_sql(table, path, org_col) validates every identifier against pg_catalog and compiles one statement, e.g. SELECT (h1.organization_id)::text FROM public.crew_offers h1 WHERE h1.id = $1::text, or a join for two hops.
• Baked into TG_ARGV. audit.sync_triggers() compiles the path once and passes the first-hop fk column and the compiled lookup as two extra trigger arguments (12 → 14). The insert/update logging path still reads no config and no catalog per row; a 12-argument trigger is still read correctly (no lookup).
• Ownership order. A direct org_col wins; otherwise the parent lookup; otherwise the transaction-local cascade snapshot; otherwise the existing user_col fallback. audit_log_owner_chk still sees at most one owner.

​

Fail-closed rules

​

What wave 5c attached, and the redaction review

• crew_offer_recipients → crew_offers.organization_id. OPEN; notes is the crew member’s own reply text, already allow-listed; no contact data on the row.
• crew_offer_days → crew_offers.organization_id. OPEN; per-day fee and call-window overrides are money + schedule evidence. times_changed_at stays ignored.
• Invitation → Event."organizationId". SENSITIVE allow-list; phoneNumber, email and shortCode (a bearer capability for SMS replies) are redacted. The integration test asserts none of the three values appears anywhere in the AUTO row.
• EventMember → Event."organizationId". OPEN; special_instructions stays allowed per its manifest note.

​

Still deferred: a storage decision, not an ownership one

​

Measured overhead (private database, wave 5c)

​

The exact prod query for every deferred table

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup
FROM pg_stat_user_tables
WHERE relname IN (
  'crew_offer_recipient_days',
  'availability_request_recipient_days',
  'Event',
  -- attached in wave 5c; keep them in the query to validate the forecast
  'crew_offer_days',
  'crew_offer_recipients',
  'Invitation',
  'EventMember'
);

SELECT
  avg(pg_column_size(t.*)) AS avg_row_bytes,
  count(*) AS sampled_rows
FROM (SELECT * FROM "Event" ORDER BY random() LIMIT 5000) t;

SELECT pg_size_pretty(pg_total_relation_size('"Event"'));

​

Modeling notes and assumptions

• Baseline. Wave 5a measured ~1,098 B/row and +50–72 µs/write on a 16-column table (see src/db/migrations/20260825210000_audit_trigger_floor/migration.sql’s header). The script decomposes that into a fixed audit_log row overhead (~384 B — the ~25 non-payload columns: ids, actor fields, timestamps, request metadata) plus a payload component modeled from a synthetic 16-column reference mix (4 uuid, 5 short string, 3 datetime, 2 enum, 2 decimal). Every candidate table’s payload is then computed the same way from its own real columns (via scripts/schema/parsePrismaSchema.ts) and added to that fixed overhead.
• Per-type byte constants (UUID_COLUMN_BYTES, SHORT_STRING_COLUMN_BYTES, LONG_STRING_COLUMN_BYTES for @db.Text columns, DATETIME_COLUMN_BYTES, DATE_COLUMN_BYTES for @db.Date, DECIMAL_COLUMN_BYTES, ENUM_VALUE_OVERHEAD_BYTES plus each enum’s real average value-name length) are estimates of the JSON-serialized value width to_jsonb writes into before_state/after_state, plus a JSONB key/punctuation overhead per column keyed on the column’s real name length.
• Redaction-aware payload. Only columns the trigger actually writes count toward payload width: for OPEN tables that is allowColumns + ignoreColumns (deny/mask excluded); for SENSITIVE tables it is allowColumns only (everything else, including ignoreColumns, is redacted). This mirrors audit.log_change()’s v_hidden computation exactly.
• INSERT/DELETE vs. UPDATE. audit.log_change() writes the full (non-hidden) row on INSERT/DELETE, one-sided, and a diff of only the changed (non-hidden) columns on UPDATE — but BOTH before_state and after_state for those changed columns, i.e. UPDATE pays for two sides of a smaller set. The script assumes an UPDATE changes max(2, round(25% × visible columns)) columns on average — a guess, not a measurement; a table whose typical edit touches many more or fewer fields than that will forecast wrong in that direction.
• Write mix. Blended bytes/row assumes 5% INSERT / 90% UPDATE / 5% DELETE across all writes, applied uniformly. Real mixes vary by table (organization_documents is INSERT-heavy; crew_offer_recipient_days is almost entirely UPDATE) — this is a simplification stated once rather than guessed per table.
• Write rate. WRITE_RATE_ASSUMPTIONS in the script states an assumed AUTO-rows-per-org-per-day figure per table — already net of the noise killer (an UPDATE touching only ignoreColumns writes zero rows), since there is no prod gross-write-rate figure to discount from. These are judgment calls grounded in each table’s role (staffing fan-out is high-churn; document uploads are rare), not measurements.
• Org count. 500 active organizations, platform-wide. Not sourced from anywhere — substitute the real figure and re-run.
• Retention. 7 years flat (GB/year × 7), not modeling org-count growth over the window. A growing platform will retain more than this number by year 7; a shrinking or flat one will retain about this much.
• Latency. Scaled linearly off the wave 5a baseline by the ratio of blended bytes/row to the 1,098 B baseline. This is the weakest part of the model — the wave 5a latency figure folds in trigger dispatch overhead that does not scale with payload size at all, so the true latency curve is flatter than this model assumes, especially for the narrower deferred tables.

​

The kill switch

UPDATE audit.table_config SET enabled = false WHERE table_name = 'ledger_items';
SELECT * FROM audit.sync_triggers();

UPDATE audit.table_config SET enabled = true WHERE table_name = 'ledger_items';
SELECT * FROM audit.sync_triggers(ARRAY['ledger_items']);

​

Verifying these waves

cd api-events
npx tsx scripts/audit/generateTableConfigSeed.ts --check
npx vitest run src/db/auditClassification.test.ts
npx vitest run --config vitest.integration.config.ts src/db/auditTriggers.integration.test.ts src/db/auditClassification.integration.test.ts

​

The endpoint coverage gate is now call-graph level

​

Event cascade ownership

​

Migration notes

• 20260923140000_audit_org_parent_lookup is replay-safe: columns use ADD COLUMN IF NOT EXISTS, and every function (audit.org_parent_sql, audit.audit_log_change, audit.sync_triggers) is CREATE OR REPLACE. sync_triggers() rebuilds each attached relation’s trigger with the resolved parent lookup baked into its arguments, so the trigger reads no config per row.
• 20260923140200_audit_trigger_wave_5c only calls audit.sync_triggers(), which drops and recreates per relation, so replaying it converges on the same attached set.
• Kill switch for any attached table, no deploy: UPDATE audit.table_config SET enabled = false WHERE table_name = '<table>'; SELECT * FROM audit.sync_triggers();