The audit trail — append-only, enforced by the database

Code

The table, the trigger, and the actor model

Three files as tabs: the migration, the queries, and the actor model in Go. 00006_audit_events.sql → audit.sql → audit.go.

1. db/migrations/00006_audit_events.sql (new) — the audit_events table, its index, and the append-only enforcement: a plpgsql function that RAISEs, and triggers wiring it to BEFORE UPDATE OR DELETE (per row) and BEFORE TRUNCATE (per statement). Note seq BIGINT GENERATED ALWAYS AS IDENTITY and the FK-free document_id / actor_* columns.

2. internal/db/queries/audit.sql (new) — two queries: CreateAuditEvent (insert one row) and ListAuditEventsByDocument (ORDER BY seq). Run sqlc generate.

3. internal/handlers/audit.go (new) — the Go side of the actor model:

the evt* event-type constants (one per state change),
auditActor and its constructors actorUser / actorSigner / actorSystem / actorAnonymous — each sets actor_type and the frozen Label,
audit(ctx, q, docID, type, message, actor) — writes one row. It takes a *db.Queries, so you pass a transaction-bound q (via WithTx) to record the event atomically with the state change, or h.Queries for best-effort read-path events,
auditBestEffort — for page-view events that must never fail the user's request (it logs on error instead of returning it).

func (h *Handlers) audit(ctx context.Context, q *db.Queries, docID pgtype.UUID, eventType, message string, a auditActor) error {
	return q.CreateAuditEvent(ctx, db.CreateAuditEventParams{
		DocumentID: docID, EventType: eventType, Message: message,
		ActorType: a.Type, ActorUserID: a.UserID, ActorSignerID: a.SignerID, ActorLabel: a.Label,
	})
}

Verify — reseed to run the migration, then try to break the append-only rule directly in psql:

$ psql "$DATABASE_URL" -c "insert into audit_events (document_id, event_type, message, actor_type, actor_label)
                           values (gen_random_uuid(), 'test', 'hi', 'system', 'system');"
INSERT 0 1
$ psql "$DATABASE_URL" -c "update audit_events set message='changed';"
ERROR:  audit_events is append-only: UPDATE is not permitted
$ psql "$DATABASE_URL" -c "delete from audit_events;"
ERROR:  audit_events is append-only: DELETE is not permitted

Inserting works; updating and deleting are refused by the database, loudly — not by application code you have to remember to write. That is the guarantee. And notice you now cannot delete your own test row — which is exactly the point. To clear it, run the migration's down (which drops the whole table) and migrate up again.

00006_audit_events.sql CREATE

Create this file in db/migrations/

-- +goose Up
-- +goose StatementBegin
-- Append-only audit trail. Every state change lands exactly one row here.
--
-- WHO — the actor problem. An actor is EITHER a registered user (the owner) OR a
-- signer (accountless, identified only by their invite) OR the system (automatic
-- transitions) OR anonymous (a rejected token). We model that honestly instead of
-- forcing a user_id everywhere:
--   * actor_type    — the discriminator
--   * actor_user_id / actor_signer_id — nullable, plain UUIDs (NO foreign keys)
--   * actor_label   — the human identity FROZEN at event time
-- The ids are FK-free on purpose: an audit row must be self-contained and outlive
-- whatever it references. actor_label is what the UI shows, so the trail stays
-- readable with no joins even after the user/signer/document is gone.
--
-- document_id is likewise a plain UUID, NOT a foreign key: when a draft is
-- deleted its audit rows MUST survive. A cascade would erase the evidence; a
-- restrict would block the delete. FK-free keeps the rows and their document_id.
--
-- message is a frozen, plain-language sentence composed at write time. The UI
-- renders it verbatim (never raw JSON), and it can't drift if display code changes.
CREATE TABLE audit_events (
    -- Monotonic sequence: several events can share a transaction (e.g. signed +
    -- completed), so now() ties. seq gives a stable total order.
    seq             BIGINT      GENERATED ALWAYS AS IDENTITY,
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id     UUID        NOT NULL,
    event_type      TEXT        NOT NULL,
    message         TEXT        NOT NULL,
    actor_type      TEXT        NOT NULL CHECK (actor_type IN ('user', 'signer', 'system', 'anonymous')),
    actor_user_id   UUID,
    actor_signer_id UUID,
    actor_label     TEXT        NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_events_document ON audit_events (document_id, seq);
-- +goose StatementEnd

-- +goose StatementBegin
-- Enforce append-only IN THE DATABASE. We use a trigger rather than REVOKE
-- because the app connects as a superuser in dev and superusers bypass table
-- grants — the trigger's RAISE applies to everyone. It also fails loudly with an
-- error, unlike a rewrite RULE which would silently turn UPDATE/DELETE into a
-- no-op. (Honest limit: a superuser could still DROP this trigger; true
-- immutability against a hostile DBA needs off-box log shipping. This stops the
-- application — and ordinary roles — from ever rewriting history.)
CREATE FUNCTION audit_events_append_only() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'audit_events is append-only: % is not permitted', TG_OP;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER audit_events_no_update_delete
    BEFORE UPDATE OR DELETE ON audit_events
    FOR EACH ROW EXECUTE FUNCTION audit_events_append_only();
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER audit_events_no_truncate
    BEFORE TRUNCATE ON audit_events
    FOR EACH STATEMENT EXECUTE FUNCTION audit_events_append_only();
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE audit_events;
DROP FUNCTION IF EXISTS audit_events_append_only();
-- +goose StatementEnd

audit.sql CREATE

Create this file in internal/db/queries/

-- name: CreateAuditEvent :exec
INSERT INTO audit_events (
    document_id, event_type, message, actor_type, actor_user_id, actor_signer_id, actor_label
) VALUES ($1, $2, $3, $4, $5, $6, $7);

-- name: ListAuditEventsByDocument :many
SELECT * FROM audit_events WHERE document_id = $1 ORDER BY seq;

audit.go CREATE

Create this file in internal/handlers/

package handlers

import (
	"context"

	"github.com/jackc/pgx/v5/pgtype"

	"github.com/programuoki/signflow/internal/db"
)

// Event type constants — one per state change we record.
const (
	evtUploaded  = "document.uploaded"
	evtSent      = "document.sent"
	evtViewed    = "signer.viewed"
	evtSigned    = "document.signed"
	evtCompleted = "document.completed"
	evtDeleted   = "document.deleted"
	evtBadToken  = "signer.bad_token"
	evtTamper    = "document.tamper_detected"
)

// auditActor is the polymorphic actor for an audit event: exactly one of user /
// signer, or neither for system/anonymous. Label is the human identity frozen
// into the row.
type auditActor struct {
	Type     string
	UserID   pgtype.UUID
	SignerID pgtype.UUID
	Label    string
}

func actorUser(u db.User) auditActor {
	return auditActor{Type: "user", UserID: u.ID, Label: u.Email}
}
func actorSigner(s db.Signer) auditActor {
	return auditActor{Type: "signer", SignerID: s.ID, Label: s.Email}
}
func actorSystem() auditActor {
	return auditActor{Type: "system", Label: "system"}
}
func actorAnonymous() auditActor {
	return auditActor{Type: "anonymous", Label: "anonymous"}
}

// audit writes one event. Pass a transaction-bound *db.Queries (via WithTx) to
// record the event atomically with the state change it describes; pass h.Queries
// for best-effort read-path events (viewed, tamper). Returns the DB error so tx
// callers can roll back on failure.
func (h *Handlers) audit(ctx context.Context, q *db.Queries, docID pgtype.UUID, eventType, message string, a auditActor) error {
	return q.CreateAuditEvent(ctx, db.CreateAuditEventParams{
		DocumentID:    docID,
		EventType:     eventType,
		Message:       message,
		ActorType:     a.Type,
		ActorUserID:   a.UserID,
		ActorSignerID: a.SignerID,
		ActorLabel:    a.Label,
	})
}

// auditBestEffort records a read-path event and only logs on failure — used where
// the event must not fail the user's request (page views).
func (h *Handlers) auditBestEffort(ctx context.Context, docID pgtype.UUID, eventType, message string, a auditActor) {
	if err := h.audit(ctx, h.Queries, docID, eventType, message, a); err != nil {
		h.Log.Error("audit write", "err", err, "event", eventType)
	}
}

// shortHash abbreviates a hex hash for audit messages.
func shortHash(hash string) string {
	if len(hash) <= 16 {
		return hash
	}
	return hash[:8] + "…" + hash[len(hash)-8:]
}