Database Trigger and Workspace Topic Design Volume 07 - Advanced Application Architecture
Trigger function CREATE OR REPLACE FUNCTION public.broadcast_task_changes() RETURNS trigger SECURITY DEFINER SET search_path = '' LANGUAGE plpgsql AS $$ DECLARE project_id uuid; workspace_id uuid; BEGIN project_id := CASE WHEN TG_OP = 'DELETE' THEN OLD."projectId" ELSE NEW."projectId" END;
SELECT p."workspaceId" INTO workspace_id FROM public."Project" p WHERE p.id = project_id;
IF workspace_id IS NOT NULL THEN PERFORM realtime.broadcast_changes( 'workspace:' || workspace_id::text || ':tasks', TG_OP, TG_OP, TG_TABLE_NAME, TG_TABLE_SCHEMA, NEW, OLD ); END IF;
RETURN NULL; END; $$;
Attach the trigger DROP TRIGGER IF EXISTS task_realtime_broadcast ON public."Task";
CREATE TRIGGER task_realtime_broadcast AFTER INSERT OR UPDATE OR DELETE ON public."Task" FOR EACH ROW EXECUTE FUNCTION public.broadcast_task_changes();
Why use an AFTER trigger • The event describes a database change that has actually been accepted.
• The browser should not be notified about a write that later fails.
• The topic is derived from persisted Task -> Project -> Workspace relationships, not browser input.
Production note If Workspace lookup becomes a hot path, measure it before denormalizing. A later schema could store workspaceId directly on Task, but Phase 26 does not change the data model merely for convenience.
Authentication answers who the request represents. Treating decoded or client-supplied identity as trusted is a security boundary failure.
Restate the goal of “Database Trigger and Workspace Topic Design” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: credentials/session → token → verification → trusted identity → protected operation. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: Realtime task updates that merge into TanStack Query state with scoped subscriptions and reconnect behavior.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
const token = readBearerToken(request);
const claims = await verifier.verify(token);
request.user = {
id: claims.sub,
email: claims.email,
};Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Inspect whether a token exists, whether it is expired, which issuer/audience signed it, and what the API verifier expects.
Trust only verified server claims; never repair identity by accepting a client-sent user id.
Without copying the final source, create a second small scenario that applies “Database Trigger and Workspace Topic Design” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Deduplicate events that echo the current user's own mutation.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Duplicate optimistic + realtime updates
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.