Soft Deletion Audit Trail in Go/Postgres: 5 Tips (2026)

I was in the middle of a release that added a brand‑new discount engine. Two minutes after the deploy, our support inbox lit up: a customer claimed they were charged for a plan they’d cancelled a week ago. The UI showed the invoice as “deleted”, but the accounting backend still summed the amount into March’s revenue. When I dug into the DB I found a row with `deleted_at` set, yet the monthly roll‑up query ignored the soft‑delete filter because it was written in raw SQL. The result? **$500 k of disputed revenue** that took three days of frantic back‑and‑forth with the finance team to untangle. That night taught me two things: 1️⃣ soft deletion must be baked into every query, not bolted on later, and 2️⃣ an immutable audit trail is the only way to prove what really happened when things go sideways.

How to implement soft deletion & audit trails for billing records in Go/Postgres 2024‑2026

Implement soft deletion for billing records using a `deleted_at` TIMESTAMPTZ column and a default query scope. Create an immutable audit trail via PostgreSQL triggers (or a Go service layer) that logs events to a separate table with user context and a RFC 4189 timestamp. This ensures data recovery, compliance, and historical tracking without permanent data loss.

—

Why Soft Deletion & Audit Trails Are Non‑Negotiable for Billing

The legal and compliance imperative

Financial regulators in the US, EU, and APAC have zero tolerance for data gaps. GDPR/CCPA bite when you can’t prove when a record was altered, who did it, and why. An immutable log satisfies the “who‑what‑when‑why” requirement without exposing raw PII, because you can redact fields at read‑time.

Recovering from data corruption and human error

A rogue `UPDATE` can erase a line item. With a `deleted_at` flag you can resurrect the row instantly; the audit table tells you the exact payload before the change. In one of my previous jobs a mis‑typed migration script set `amount = NULL` on 2 M rows—recovering took 3 h because we had an audit log to replay.

Understanding business intelligence and historical analysis

Revenue forecasts, churn models, and audit‑ready financial statements all need a “as‑of” view. Soft deletes preserve the state at any point in time, while the audit table lets you reconstruct the exact timeline for any invoice.

—

Architecting the Core Data Models (Go Structs & Postgres DDL)

Designing the soft‑delete‑ready billing record table

-- sql/schema.sql
-- Postgres 16
CREATE TABLE billing_invoice (
    id               BIGSERIAL PRIMARY KEY,
    customer_id      BIGINT NOT NULL,
    amount_cents     BIGINT NOT NULL,
    currency         TEXT   NOT NULL,
    period_start     DATE   NOT NULL,
    period_end       DATE   NOT NULL,
    status           TEXT   NOT NULL CHECK (status IN ('draft','open','paid','void')),
    metadata         JSONB  DEFAULT '{}'::jsonb,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at       TIMESTAMPTZ,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT
);

*Why `deleted_at` instead of a status enum?* A nullable timestamp records **when** the row vanished, which is required for retention policies. A status column forces you to add a new enum value whenever you need a new soft‑delete semantics, and you still lose the precise timestamp.

Creating the separate, immutable audit event table (RFC 4189)

-- sql/audit.sql
CREATE TABLE audit_event (
    id            BIGSERIAL PRIMARY KEY,
    table_name    TEXT NOT NULL,
    row_id        BIGINT NOT NULL,
    operation     TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
    changed_by    TEXT NOT NULL,               -- e.g. JWT sub or service name
    changed_at    TIMESTAMPTZ NOT NULL,        -- RFC 4189 compliant
    payload       JSONB NOT NULL,              -- full row after change
    context       JSONB,                       -- request ID, IP, etc.
    CONSTRAINT uq_audit UNIQUE (table_name, row_id, changed_at, operation)
);

Option	JSONB	Structured columns
Flexibility	Can store any schema version	Strong typing, easier query plans
Storage	Slightly larger (type metadata)	Compact for fixed schema
Indexing	Need GIN on JSONB for search	B‑Tree on columns

I favor JSONB for audit because billing schemas evolve: new discount fields, tax regimes, and you don’t want to migrate the audit table every quarter.

JSONB vs. structured columns: trade‑offs for 2026 use cases

*If you need ad‑hoc forensic queries* – GIN indexes on `payload ->> ‘status’` are fast enough for sub‑second scans on 200 M rows. *If you run nightly aggregates* – extracting a few columns into a materialized view can be cheaper than parsing JSON every run.

—

Implementing the Soft Delete Pattern in Go (Context & Goroutine‑Safe)

Creating a reusable GORM soft‑delete hook

// go/models/invoice.go
// go 1.22
package models

import (
    "context"
    "time"

    "gorm.io/gorm"
    "gorm.io/plugin/soft_delete"
)

type Invoice struct {
    ID           int64                `gorm:"primaryKey"`
    CustomerID   int64                `gorm:"not null;index"`
    AmountCents  int64                `gorm:"not null"`
    Currency     string               `gorm:"size:3;not null"`
    PeriodStart  time.Time            `gorm:"type:date;not null"`
    PeriodEnd    time.Time            `gorm:"type:date;not null"`
    Status       string               `gorm:"type:text;not null;check:status IN ('draft','open','paid','void')"`
    Metadata     map[string]any       `gorm:"type:jsonb;default:'{}'"`
    CreatedAt    time.Time            `gorm:"autoCreateTime"`
    UpdatedAt    time.Time            `gorm:"autoUpdateTime"`
    DeletedAt    soft_delete.DeletedAt `gorm:"softDelete:flag"` // GORM uses a flag; we want timestamp
}

GORM’s built‑in soft delete uses a tinyint flag. To get a timestamp we replace it with our own hook:

// go/db/softdelete.go
// go 1.22
package db

import (
    "context"
    "time"

    "gorm.io/gorm"
)

// SoftDelete sets DeletedAt = now() instead of hard delete.
func SoftDelete(ctx context.Context, db *gorm.DB, model any) error {
    tx := db.WithContext(ctx).Model(model).Update("deleted_at", time.Now().UTC())
    return tx.Error
}

**My take:** I stopped using GORM’s default soft‑delete flag years ago because the flag hides the deletion instant. A real timestamp gives you legal proof and lets you schedule permanent purges.

Handling cascading soft deletes across related records

func DeleteInvoiceCascade(ctx context.Context, db *gorm.DB, invoiceID int64) error {
    return db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // 1️⃣ soft‑delete line items
        if err := SoftDelete(ctx, tx.Where("invoice_id = ?", invoiceID), &LineItem{}); err != nil {
            return err
        }
        // 2️⃣ soft‑delete the invoice itself
        if err := SoftDelete(ctx, tx.Where("id = ?", invoiceID), &Invoice{}); err != nil {
            return err
        }
        return nil
    })
}

Because `FOREIGN KEY … ON DELETE RESTRICT` blocks hard deletes, the cascade is safe: we only ever set `deleted_at` on children after verifying the parent is being removed.

Building safe, filtered queries to exclude deleted data

func ListActiveInvoices(ctx context.Context, db *gorm.DB, custID int64) ([]Invoice, error) {
    var invoices []Invoice
    // Default scope: only rows where deleted_at IS NULL
    err := db.WithContext(ctx).
        Where("customer_id = ? AND deleted_at IS NULL", custID).
        Find(&invoices).Error
    return invoices, err
}

**Tip:** Add a partial index to make the `WHERE deleted_at IS NULL` predicate cheap.

CREATE INDEX idx_invoice_active ON billing_invoice (customer_id)
WHERE deleted_at IS NULL;

—

Building the Immutable Audit Trail with Postgres Triggers

Leveraging PostgreSQL trigger functions (CREATE FUNCTION) for reliability

-- sql/audit_trigger.sql
CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $func$
DECLARE
    payload JSONB;
BEGIN
    IF TG_OP = 'DELETE' THEN
        payload := to_jsonb(OLD);
    ELSE
        payload := to_jsonb(NEW);
    END IF;

    INSERT INTO audit_event (
        table_name,
        row_id,
        operation,
        changed_by,
        changed_at,
        payload,
        context
    ) VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        current_setting('app.current_user', true),
        now() AT TIME ZONE 'UTC',               -- RFC 4189 format
        payload,
        jsonb_build_object(
            'request_id', current_setting('app.request_id', true),
            'ip', current_setting('app.client_ip', true)
        )
    );
    RETURN NULL;  -- AFTER trigger, result ignored
END;
$func$;

Attach it to the billing table:

CREATE TRIGGER trg_audit_invoice
AFTER INSERT OR UPDATE OR DELETE ON billing_invoice
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

**Why an AFTER trigger?** An AFTER trigger sees the final row state. If a transaction rolls back, the audit insert is automatically discarded, keeping the log perfectly in sync.

Auditing the who, what, when, and why: storing context

We push request‑level data via `SET LOCAL` right before each query:

func WithAuditContext(ctx context.Context, db *pgxpool.Pool, fn func(tx pgx.Tx) error) error {
    requestID := ctx.Value("request_id").(string)
    user := ctx.Value("user").(string)
    clientIP := ctx.Value("ip").(string)

    return db.BeginTx(ctx, pgx.TxOptions{}, func(tx pgx.Tx) error {
        // Set session vars visible to the trigger
        _, err := tx.Exec(ctx,
            "SET LOCAL app.request_id = $1, app.current_user = $2, app.client_ip = $3",
            requestID, user, clientIP)
        if err != nil {
            return err
        }
        return fn(tx)
    })
}

With this pattern the trigger has everything it needs without pulling from the application table.

Implementing optimistic concurrency control to prevent audit gaps

If two services update the same invoice concurrently, we want only one audit row per logical change. Use a version column:

ALTER TABLE billing_invoice ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
CREATE UNIQUE INDEX uq_invoice_version ON billing_invoice (id, version);

In Go:

func UpdateInvoiceOptimistically(ctx context.Context, db *gorm.DB, inv *Invoice) error {
    return db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // Increment version atomically
        result := tx.Model(&Invoice{}).
            Where("id = ? AND version = ?", inv.ID, inv.Version).
            Updates(map[string]any{
                "amount_cents": inv.AmountCents,
                "status":       inv.Status,
                "version":      gorm.Expr("version + 1"),
                "updated_at":   time.Now(),
            })
        if result.RowsAffected == 0 {
            return fmt.Errorf("concurrent update detected for invoice %d", inv.ID)
        }
        return result.Error
    })
}

If the `UPDATE` fails, the transaction aborts before the trigger fires, so no orphan audit row appears.

—

Production‑Grade Error Handling & Data Integrity (What Most Guides Miss)

Transaction rollbacks and partial audit failure recovery

A common pitfall is calling `tx.Commit()` **after** the audit write succeeds but ignoring a later error in the main payload. The fix is to wrap **both** actions in the same transaction and **never** commit until the audit insertion returns without error.

func CreateInvoice(ctx context.Context, db *pgxpool.Pool, inv Invoice) error {
    return WithAuditContext(ctx, db, func(tx pgx.Tx) error {
        // 1️⃣ insert invoice
        _, err := tx.Exec(ctx,
            `INSERT INTO billing_invoice (customer_id, amount_cents, currency,
                                         period_start, period_end, status, metadata)
             VALUES ($1,$2,$3,$4,$5,$6,$7)`,
            inv.CustomerID, inv.AmountCents, inv.Currency,
            inv.PeriodStart, inv.PeriodEnd, inv.Status, inv.Metadata)
        if err != nil {
            return err
        }
        // 2️⃣ no extra work; audit trigger fires automatically
        return nil
    })
}

If the trigger fails (e.g., audit table is offline), the whole transaction rolls back, and Sentry captures the error.

Implementing idempotent audit writes for retry logic

Network blips can cause the client to retry the HTTP request. If the second attempt reaches the DB after the first succeeded, you’ll get a duplicate `UNIQUE` violation on `uq_audit`. Make the trigger **idempotent** by using the `ON CONFLICT DO NOTHING` clause inside the trigger function:

INSERT INTO audit_event (...)
VALUES (...)
ON CONFLICT (table_name, row_id, changed_at, operation) DO NOTHING;