AUD · Audit log

One trigger, app.audit_row, writes audit_log for the tables worth watching: settings, legal entities, pools, bank accounts, memberships, roles and overrides, pricing groups, charge types, vehicles, drivers, driver documents and platform accounts, background checks, pool transfers, rentals, contracts, inspections, damages, work orders, insurance claims, gate passes and approvals, charges, payments, allocations, adjustments and debt letters. Logins are in Supabase's own auth log. Code AUD.

Not yet a rule

These questions are open in #74. Each gets a rule with a new ID once it is answered.

AUD open 1 · Rows with no pool

Answered questions

DOC-Q3 (updates that move only updated_at): they are logged. R-AUD-01 stands as written: every update writes a row, and such a row lists no changed field (R-AUD-03). It records that someone saved the row.
DOC-Q4 (other document numbers): masked too. A new rule, R-AUD-11: every driver document's number is masked, not only the KTP's.

Rules

R-AUD-01 · Every insert, update and delete on a watched table writes one audit row

Status: planned
Example: given a watched table such as vehicles, when a car is inserted and then updated twice, then the car has three audit rows. An update that changes nothing but updated_at still writes one, with no changed field.
Refusal: none.
Who: every writer, jobs included.
Source: [Ref] Database rules and conventions, Audit log; D9 (P6). The update case is DOC-Q3.

R-AUD-02 · An audit row keeps the old row, the new row and the names of the changed fields

Status: planned
Example: given a car whose color changes from silver to black, when its audit row is read, then old_data has silver, new_data has black and changed_fields is {color}.
Refusal: none.
Who: every watched table.
Source: [Ref] Database rules and conventions, Audit log.

R-AUD-03 · updated_at is never listed as a changed field

Status: planned
Example: given a car whose color changes, when its audit row is read, then changed_fields is {color}, without updated_at.
Refusal: none.
Who: every watched table.
Source: [Ref] Database rules and conventions, Audit log; the M0 acceptance checks.

R-AUD-04 · An audit row records who made the change

Status: planned
Example: given Deka updates a car, when its audit row is read, then actor_id is Deka's profile id. A job's change has no actor.
Refusal: none.
Who: every watched table.
Source: [Ref] Database rules and conventions, Audit log.

R-AUD-05 · An audit row's source is app, unless the writer marked it cron or system

Status: planned
Example: given the daily rent job sets app.source to cron before it writes, when its charges' audit rows are read, then source is cron; a change from the web app reads app.
Refusal: none.
Who: every writer.
Source: [Ref] Database rules and conventions, Audit log.

R-AUD-06 · NIK, phone and account numbers are masked before they're stored in the audit log

Status: planned
Example: given a driver whose phone changes from +6281234567890, when the audit row is read, then neither old_data nor new_data holds the number in clear.
Refusal: none.
Who: every watched table.
Source: D9 (P6); the M0 acceptance checks.

R-AUD-07 · An audit row records the pool of its row, its car or its driver

Status: planned
Example: given a payment, which has no pool, by a driver at PML, when its audit row is read, then pool_id is PML.
Refusal: none.
Who: every watched table.
Source: D9 (P6); [Ref] Database rules and conventions, Audit log.

R-AUD-08 · Reading audit rows needs audit_log:read at their pool

Status: planned
Example: given a viewer scoped to SBY, when he reads audit_log, then the history of PML's cars doesn't come back.
Refusal: none: hidden.
Who: every system role holds audit_log:read, scoped by its pools.
Source: D9 (P6); the M0 acceptance checks.

R-AUD-09 · Nobody can write, change or delete audit rows directly

Status: planned
Example: given a super_admin, when she deletes an audit row or inserts one, then nothing changes.
Refusal: none: insufficient_privilege (42501) on insert; no effect on update or delete.
Who: every app user. Only the audit trigger writes.
Source: [Ref] Database rules and conventions, Audit log; [Ref] Roles, policy matrix.

R-AUD-10 · GPS positions and append-only event tables aren't audited

Status: planned
Example: given a GPS position or a queue event is inserted, when audit_log is read, then no row for it exists.
Refusal: none.
Who: gps_positions and the event tables.
Source: [Ref] Database rules and conventions, Audit log.

R-AUD-11 · Every driver document's number is masked in the audit log

Status: planned
Example: given a driver's SIM A number is corrected, when the audit row is read, then doc_number is masked in old_data and new_data, as it is for a KTP, whose number is the NIK.
Refusal: none.
Who: driver_documents.
Source: D9 (P6: the NIK is masked, and a KTP's doc_number is the NIK); D15 (personal data). Added for DOC-Q4.