Stage 7 · Master
Phase 20 — Production Readiness
Audit Logs
Make every privileged action produce a tamper-evident, append-only record that survives even an attacker who gains hoa_maintenance_owner — and ship a copy off the primary database before that attacker can reach it.
The Security lesson split every service's database login into owner, migrator, and application roles so a stolen credential cannot escalate. Audit logging answers the question that split does not: once any privileged action does happen — a role rotation, a fee waived, a resident suspended — is there a record of exactly who did it, when, and in what order, that the actor themselves cannot quietly edit afterward? An audit table with UPDATE and DELETE still granted is not an audit log; it is a table an attacker with write access can rewrite to remove their own tracks.
Enforce Append-Only With Grants, Not Application Discipline
Relying on 'the application never issues an UPDATE against this table' is the same mistake the Security lesson's original FindByID made: it works until a future author, a hotfix, or a compromised credential does the one thing the convention assumed nobody would. Revoking UPDATE and DELETE at the database layer makes append-only true regardless of what application code — or an attacker holding hoa_maintenance_app_1 — attempts.
CREATE TABLE maintenance.audit_log (
id bigserial PRIMARY KEY,
organization_id uuid NOT NULL,
actor_user_id uuid NOT NULL,
action text NOT NULL,
resource_type text NOT NULL,
resource_id uuid NOT NULL,
before_state jsonb,
after_state jsonb,
occurred_at timestamptz NOT NULL DEFAULT now(),
prev_hash bytea NOT NULL,
entry_hash bytea NOT NULL
);
CREATE INDEX audit_log_org_occurred_idx
ON maintenance.audit_log (organization_id, occurred_at DESC);
-- Every row's hash covers the previous row's hash, forming a chain: an
-- attacker who edits or deletes any historical row breaks every hash
-- computed after it, which the verification job below detects.
CREATE OR REPLACE FUNCTION maintenance.compute_audit_entry_hash(
p_prev_hash bytea,
p_organization_id uuid,
p_actor_user_id uuid,
p_action text,
p_resource_type text,
p_resource_id uuid,
p_before_state jsonb,
p_after_state jsonb,
p_occurred_at timestamptz
) RETURNS bytea AS $$
SELECT digest(
coalesce(p_prev_hash, '\x00'::bytea) ||
convert_to(
p_organization_id::text || '|' || p_actor_user_id::text || '|' ||
p_action || '|' || p_resource_type || '|' || p_resource_id::text || '|' ||
coalesce(p_before_state::text, '') || '|' ||
coalesce(p_after_state::text, '') || '|' ||
p_occurred_at::text,
'utf8'
),
'sha256'
);
$$ LANGUAGE sql IMMUTABLE;
-- Revoked explicitly and immediately after creation — append-only is a
-- database-layer guarantee here, not an application convention.
REVOKE UPDATE, DELETE ON maintenance.audit_log FROM PUBLIC;
GRANT SELECT, INSERT ON maintenance.audit_log TO hoa_maintenance_app;
GRANT USAGE, SELECT ON SEQUENCE maintenance.audit_log_id_seq TO hoa_maintenance_app;
REVOKE UPDATE, DELETE runs against hoa_maintenance_app — the group hoa_maintenance_app_1 belongs to — so the running service, exactly the identity a stolen credential would authenticate as, physically cannot modify or erase a past entry, independent of any application-level check.
DROP FUNCTION IF EXISTS maintenance.compute_audit_entry_hash(bytea, uuid, uuid, text, text, uuid, jsonb, jsonb, timestamptz);
DROP TABLE IF EXISTS maintenance.audit_log;
Dropping the table implicitly drops audit_log_org_occurred_idx and the REVOKE/GRANT state along with it; the function is dropped explicitly first since it is a separate object the table does not own.
Write Every Privileged Action Through One Function, Not Ad Hoc Inserts
A hash chain only protects entries that were computed correctly and in order — a handler that inserts a row without calling the hashing function, or that computes the hash from stale data, produces a chain that looks intact but was never actually verifying anything. Centralizing every write through one Go function removes that failure mode by construction.
package audit
import (
"context"
"encoding/json"
"fmt"
"github.com/hoa-platform/backend/pkg/tenant"
"github.com/jackc/pgx/v5"
)
type Entry struct {
ActorUserID string
Action string
ResourceType string
ResourceID string
Before any
After any
}
// Record is the only sanctioned way any handler writes an audit entry.
// It reads the previous entry's hash inside the same transaction the
// caller is already in, so a concurrent writer cannot race it into
// computing two entries against the same prev_hash.
func Record(ctx context.Context, tx pgx.Tx, tenantID tenant.ID, entry Entry) error {
var prevHash []byte
err := tx.QueryRow(ctx, `
SELECT entry_hash FROM maintenance.audit_log
WHERE organization_id = $1
ORDER BY id DESC
LIMIT 1
FOR UPDATE`,
tenantID.String(),
).Scan(&prevHash)
if err != nil && err != pgx.ErrNoRows {
return fmt.Errorf("read previous audit hash: %w", err)
}
beforeJSON, err := json.Marshal(entry.Before)
if err != nil {
return fmt.Errorf("marshal before state: %w", err)
}
afterJSON, err := json.Marshal(entry.After)
if err != nil {
return fmt.Errorf("marshal after state: %w", err)
}
const insert = `
INSERT INTO maintenance.audit_log (
organization_id, actor_user_id, action, resource_type,
resource_id, before_state, after_state, prev_hash, entry_hash
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8,
maintenance.compute_audit_entry_hash($8, $1, $2, $3, $4, $5, $6, $7, now())
)`
if _, err := tx.Exec(ctx, insert,
tenantID.String(), entry.ActorUserID, entry.Action, entry.ResourceType,
entry.ResourceID, beforeJSON, afterJSON, prevHash,
); err != nil {
return fmt.Errorf("insert audit entry: %w", err)
}
return nil
}FOR UPDATE on the read of the previous hash forces concurrent privileged actions in the same tenant to serialize through this one row-lock, so two simultaneous writes can never both compute their entry against the same prev_hash and silently fork the chain.
Storing structured JSONB for both states — not a human-written sentence like 'waived late fee' — is what lets a later investigation reconstruct exactly what changed, field by field, and lets the compliance export in the next section produce a diff automatically instead of relying on whoever wrote the log line to have described it completely and accurately under pressure.
Copy the Chain Off the Primary Before Verifying It
A hash chain that only ever lives in maintenance.charges's own database is still one DROP TABLE CASCADE away — executed by whoever holds hoa_maintenance_owner, not the DML-only app role — from disappearing entirely along with the evidence it protected. Streaming every entry to an independent, append-only destination the primary database cannot delete from is what makes the audit trail survive a compromise of the database itself, not just a compromise of the application role.
package export
import (
"context"
"encoding/json"
"fmt"
"github.com/segmentio/kafka-go"
)
// AuditRecord mirrors maintenance.audit_log exactly — including the
// hash chain fields — because the offsite copy exists to let an
// investigator verify the chain independently of whether the primary
// database can still be trusted.
type AuditRecord struct {
ID int64 `json:"id"`
OrganizationID string `json:"organization_id"`
Action string `json:"action"`
ResourceType string `json:"resource_type"`
ResourceID string `json:"resource_id"`
OccurredAt string `json:"occurred_at"`
PrevHash string `json:"prev_hash"`
EntryHash string `json:"entry_hash"`
}
type Publisher struct {
writer *kafka.Writer
}
func NewPublisher(brokers []string) *Publisher {
return &Publisher{writer: &kafka.Writer{
Addr: kafka.TCP(brokers...),
Topic: "hoa.audit.maintenance",
Balancer: &kafka.Hash{}, // by organization_id: preserves per-tenant order
RequiredAcks: kafka.RequireAll,
}}
}
// Publish is called by a logical-replication consumer (Debezium)
// reading maintenance.audit_log's WAL stream, never by the request
// path that wrote the row — publishing failure must never block or
// roll back the transaction that recorded the audit entry.
func (p *Publisher) Publish(ctx context.Context, record AuditRecord) error {
payload, err := json.Marshal(record)
if err != nil {
return fmt.Errorf("marshal audit record: %w", err)
}
return p.writer.WriteMessages(ctx, kafka.Message{
Key: []byte(record.OrganizationID),
Value: payload,
})
}kafka.Hash keyed on organization_id keeps one tenant's entries in a single partition, so the offsite copy preserves the same per-tenant order the hash chain depends on — a chain verified out of order would report false tampering that never happened.
Running the verifier against maintenance.audit_log in the primary database only proves the rows currently there are internally consistent with each other — it says nothing about whether a row was deleted and the chain recomputed to hide it, because whoever had the access to tamper with rows had the access to recompute forward from that point too. Verifying against the independently streamed Kafka copy is what actually catches that: the offsite copy was already written before any later tampering in the primary could occur, so a mismatch between the two is the tamper signal, not the primary's internal consistency alone.
Applied exercise
Add audit coverage for a role rotation, end to end
The Security lesson's credential rotation drill — creating hoa_maintenance_app_2 and retiring hoa_maintenance_app_1 — currently happens with no audit trail at all: it is a psql session run by an operator, not a call through audit.Record.
- Design the resource_type and action values that should represent a credential rotation in maintenance.audit_log, and state what belongs in before_state and after_state for this specific action.
- Write the Go call site (which existing rotation step it wraps, and in which transaction) that invokes audit.Record for this action.
- Explain why this entry must be written by hoa_maintenance_migrator's transaction rather than by hoa_maintenance_app_1, given that the rotation itself is a migrator-privileged operation.
- Write the verifier invocation that would confirm this new entry extends the existing hash chain rather than starting a new one.
Deliverable
A short schema note for the new audit action, the Go call site wrapping the rotation step, a written justification for which role writes the entry, and the verifier command used to confirm chain continuity.
Completion checks
- The proposed before/after state captures which application role generation changed, not a free-text description.
- The justification correctly reasons about which role must hold INSERT rights on audit_log for this specific write.
- The verifier command targets the offsite Kafka copy, not the primary database, consistent with this lesson's verification pattern.
Why is UPDATE, DELETE revoked on maintenance.audit_log at the database layer instead of simply never writing application code that issues those statements?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.