Ta yaya za ka tsara audit log don shekaru na traceability?

Question

IT Interview Practice · Accepted Answer

Mai da shi **append-only da immutable**, ka haɗa kowane entry zuwa na baya da **hash** domin tampering ya zama mai ganuwa, **partition da time** domin old data ya tsufa cikin arha, kuma ka archive sealed partitions zuwa **WORM object storage** (write-once) da retention policy — kada ka taɓa bar `UPDATE`/`DELETE` a kan live records.

```text
service ──emit event──▶ append-only writer
                             │  hash = H(prev_hash || payload)
                             ▼
                     audit_2026_07 (hot partition)  ◀── query by actor/time
                             │ month sealed
                             ▼
                     WORM object store (S3 Object Lock)  ── retention: 7y
                             │
                     periodic anchor: publish latest hash externally
```

## Append-only & tamper-evidence

Dukan darajar audit log ita ce za ka iya **dogaro da ita daga baya**, don haka schema dole ya hana mutation. Ba app `INSERT` kawai — babu `UPDATE`/`DELETE` — kuma ka tilasta shi a matakin DB role, ba code kawai ba. A saman haka, gina **hash chain**: kowace row tana adana `hash = SHA256(prev_hash || this_row)`. Canza kowace historical row kuma kowane hash na gaba yana karyewa, don haka tampering *mai ganuwa* ne ko da wanda ke da DB access. Lokaci-lokaci **anchor** hash na ƙarshe wani wuri da ba ka sarrafa ba (notary, public ledger, signed email) domin ko admin da ke sake rubuta dukan chain ba zai iya daidaita anchored value ba.

## Time partitioning & retention

Logs na shekaru da yawa suna girma ba tare da iyaka ba, don haka **partition da time** (misali monthly range partitions). Fa'idodi: queries suna filter da partition, kuma murkushe old data shi ne metadata `DETACH`/`DROP PARTITION` — nan take, ba giant `DELETE` da ke kumbura table ba. Sealed partitions suna matsawa zuwa **WORM storage** — S3 Object Lock a *compliance mode* yana hana sharewa a zahiri har zuwa retention date, yana biyan buƙatun kamar SOX/HIPAA. Ajiye recent months hot a Postgres don fast queries; duk abin da ya tsufa cold ne a object storage, query a buƙata.

## Ra'ayin hira

Bincike shi ne ko ka fahimci "audit log" yana nufin **immutability da provability**, ba "table da timestamp kawai ba". Mai kyau: append-only table. Mai kyau sosai: append-only *da DB role ke tilasta shi*, hash chain da external anchoring, time partitioning don retention, WORM don compliance. Tarkon gama gari: adana audit rows a cikin same mutable app table (bug ko malicious DBA na iya sake rubuta history a shiru) — auditors za su ƙi shi.

## Sulhu & hanyoyin gazawa

- **Hash chain yana serialise writes** — kowane entry yana buƙatar hash na baya, don haka naive design yana bottleneck. Batch entries ko chain per-shard kuma ka cross-link lokaci-lokaci.
- **Storage yana girma har abada**; ba tare da partitioning + tiering zuwa cheap object storage ba, cost da query latency suna fashewa.
- **WORM compliance mode ba mai juyawa ba ne** — retention setting mara kyau yana kulle data (ko yana bar shi ya ƙare) ba tare da undo ba; gwada a governance mode da farko.
- **Clock skew / out-of-order events** suna ɓata time-based reasoning — rikodin event time da ingest time duka, kuma kada ka taɓa dogaro da client clocks don ordering.

Append-only & tamper-evidence

Time partitioning & retention

Ra'ayin hira

Sulhu & hanyoyin gazawa

Tambayoyi masu alaƙa