Ifanye iwe append-only na immutable, unganisha kila entry na iliyotangulia kwa hash ili uharibifu ugundulike, partition kwa time ili data ya zamani izeeke kwa bei nafuu, na hifadhi partitions zilizofungwa kwenye WORM object storage (write-once) yenye retention policy — kamwe usiruhusu UPDATE/ kwenye live records.
DELETEservice ──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
Thamani yote ya audit log ni kwamba unaweza kuiamini baadaye, hivyo schema lazima ikataze mutation. Ipe app INSERT pekee — hakuna UPDATE/DELETE — na itekeleze kwenye kiwango cha DB role, si tu katika code. Zaidi ya hapo, jenga hash chain: kila row huhifadhi hash = SHA256(prev_hash || this_row). Badilisha row yoyote ya kihistoria na kila hash inayofuata huvunjika, hivyo uharibifu hugundulika hata na mtu mwenye DB access. Mara kwa mara anchor hash ya mwisho mahali usipodhibiti (notary, public ledger, signed email) ili hata admin anayeandika upya chain nzima asiweze kulinganisha thamani iliyoanchoriwa.
Logs za miaka mingi hukua bila mwisho, hivyo partition kwa time (mfano monthly range partitions). Faida: queries huchuja kwa partition, na kustaafisha data ya zamani ni metadata DETACH/DROP PARTITION — papo hapo, si DELETE kubwa inayovimbisha jedwali. Partitions zilizofungwa huhamia WORM storage — S3 Object Lock katika compliance mode huzuia kimwili deletion hadi retention date, ikitosheleza kanuni kama SOX/HIPAA. Weka miezi ya hivi karibuni hot katika Postgres kwa queries za haraka; kila kitu cha zamani ni cold katika object storage, kinachoqueryiwa on demand.
Uchunguzi ni kama unaelewa kuwa "audit log" humaanisha immutability na provability, si tu "jedwali lenye timestamp". Nzuri: append-only table. Bora sana: append-only iliyotekelezwa kwa DB role, hash chain yenye external anchoring, time partitioning kwa retention, WORM kwa compliance. Mtego wa kawaida: kuhifadhi audit rows katika app table ile ile inayobadilika (bug au DBA mwovu anaweza kuandika upya history kimya) — auditors watakataa.
Maktaba ya maswali ya mahojiano ya IT yenye majibu ya kina — kutoka Junior hadi Senior.
Changia