Database Integrity

Use this reference when a migration must enforce database invariants: counters, balances, latest pointers, append-only history, lifecycle states, quotas, protected deletes, immutable ownership fields, or trigger-maintained columns.

DDL belongs in a migration. Use SQL constraints for row-local invariants, unique indexes for uniqueness, foreign keys for references, and triggers only when the rule depends on transitions, related rows, or server-maintained derived state.

Choose the Smallest Database Primitive

Need	Prefer
Required field or valid range	`NOT NULL` / `CHECK`
Unique active value	partial unique index
Parent-child reference	foreign key plus index on the referencing column
Immutable owner or tenant	`BEFORE UPDATE` guard trigger
Append-only history	revoke client `UPDATE`/`DELETE`, plus optional guard trigger
Counter, balance, latest pointer, current status	trusted trigger-maintained derived field
Cross-row state transition	trigger or SQL function with clear transition checks

Server-Maintained Derived Fields

Derived fields include comment_count, balance_cents, latest_revision_id, current_status, last_event_at, and similar values maintained by database logic. Design them so normal client writes still work, but direct client edits to the derived value do not.

Required shape:

A legal client can create the parent row with defaults, NULL, or zero for server-maintained fields.
A legal client can create the child/event row that should update the parent.
The trigger updates the derived parent field.
A client cannot directly update the derived field.
Guard triggers must not block the trusted maintenance path.

Bad: Guard Blocks Its Own Maintenance Trigger

CREATE OR REPLACE FUNCTION public.protect_post_fields()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.comment_count IS DISTINCT FROM OLD.comment_count THEN
    RAISE EXCEPTION 'comment_count is server maintained';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER protect_post_fields
BEFORE UPDATE ON public.posts
FOR EACH ROW EXECUTE FUNCTION public.protect_post_fields();

CREATE OR REPLACE FUNCTION public.bump_comment_count()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE public.posts
  SET comment_count = comment_count + 1
  WHERE id = NEW.post_id;
  RETURN NEW;
END;
$$;

The child insert fires bump_comment_count, which updates posts, which fires protect_post_fields, which rejects the legitimate maintenance update.

Good: Restrict Client Update Surface, Let Trigger Maintain

CREATE TABLE public.posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  comment_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE public.comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
  author_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.comments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "owners can create posts"
ON public.posts FOR INSERT TO authenticated
WITH CHECK (owner_id = (SELECT auth.uid()));

CREATE POLICY "owners can edit post title"
ON public.posts FOR UPDATE TO authenticated
USING (owner_id = (SELECT auth.uid()))
WITH CHECK (owner_id = (SELECT auth.uid()));

GRANT SELECT, INSERT ON public.posts TO authenticated;
REVOKE UPDATE ON public.posts FROM authenticated;
GRANT UPDATE (title) ON public.posts TO authenticated;

CREATE OR REPLACE FUNCTION public.bump_comment_count()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, pg_temp
AS $$
BEGIN
  UPDATE public.posts
  SET comment_count = comment_count + 1
  WHERE id = NEW.post_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER comments_bump_post_count
AFTER INSERT ON public.comments
FOR EACH ROW EXECUTE FUNCTION public.bump_comment_count();

Here the client has no column privilege to update comment_count directly, but the trusted trigger function can maintain it.

Legal Insert Payloads

InsForge gives runtime roles broad default DML privileges on public tables so RLS can decide row access. For integrity rules that narrow writes, explicitly REVOKE broad privileges before adding column-level or operation-specific GRANTs.

Column-level grants can accidentally block legitimate API payloads. Before using column-level INSERT grants, list every column a normal SDK/REST caller may send.

Avoid this when callers may send balance_cents: 0:

GRANT INSERT (id, owner_id, name) ON public.accounts TO authenticated;

Prefer allowing the legal create payload and protecting later mutation:

GRANT SELECT, INSERT ON public.accounts TO authenticated;
REVOKE UPDATE ON public.accounts FROM authenticated;
GRANT UPDATE (name) ON public.accounts TO authenticated;

The same rule applies to latest_revision_id = NULL, comment_count = 0, current_status = 'draft', and other server-maintained initial values.

Immutable Fields and Append-Only Tables

Guard fields that must never change after creation: owner_id, tenant_id, business identifiers, immutable slugs, or ledger account IDs.

CREATE OR REPLACE FUNCTION public.prevent_owner_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.owner_id IS DISTINCT FROM OLD.owner_id THEN
    RAISE EXCEPTION 'owner_id cannot be changed';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER prevent_owner_change
BEFORE UPDATE ON public.documents
FOR EACH ROW EXECUTE FUNCTION public.prevent_owner_change();

For append-only rows such as revisions, ledger entries, audit events, and claims:

REVOKE UPDATE, DELETE ON public.ledger_entries FROM authenticated;
GRANT SELECT, INSERT ON public.ledger_entries TO authenticated;

Add trigger guards only if privileged or future grants might otherwise mutate history.

Latest Pointer and History Pattern

For document revisions or status history, keep history append-only and maintain a latest pointer on the parent.

CREATE OR REPLACE FUNCTION public.set_latest_revision()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, pg_temp
AS $$
BEGIN
  UPDATE public.documents
  SET latest_revision_id = NEW.id
  WHERE id = NEW.document_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER revisions_set_latest
AFTER INSERT ON public.document_revisions
FOR EACH ROW EXECUTE FUNCTION public.set_latest_revision();

Do not add a parent guard that rejects every latest_revision_id change unless it also allows this trusted transition. The simpler pattern is to prevent client updates to that column with privileges and let the trigger maintain it.

Self-Check Before Finishing

Can a legal parent insert pass with default, NULL, or zero derived values?
Can a legal child/event insert pass?
Does the child/event insert update the parent derived field?
Is direct client mutation of derived fields blocked?
Are immutable owner, tenant, and business identity fields protected?
Are append-only child/history rows protected from update and delete?
Do trigger functions that must bypass runtime privileges use SECURITY DEFINER with SET search_path = pg_catalog, public, pg_temp and schema-qualify references such as public.documents?
Do RLS helpers that query RLS-enabled tables use SECURITY DEFINER and SET search_path = pg_catalog, public, pg_temp, then schema-qualify references such as public.team_members and auth.uid()?
Are foreign keys and columns used by guards, RLS, and lookups indexed where the table can grow?

7.7 KiB Raw Permalink Blame History