fred
6a49de55bf
ci/woodpecker/pr/ci Pipeline was canceled
- hierarchy_audit_events: append-only semantic events (actor, verb, target snapshot with root-first parent chain), deletion-safe linkage (no FK into class tables), CHECK-enforced verb/target-kind/transfer shape, unique idempotency key, generated-always seq for per-target ordering, causation self-FK RESTRICT; hierarchy_outbox: dedicated same-transaction outbox (unique event_id, status enum, claim-by-CAS lifecycle) — migration 0019 - gateway HierarchyAuditRepository: same-tx append with idempotent replay (canonical-JSON compare, jsonb key order independent), snapshot builder, per-target history, outbox claim/complete/release; HierarchyModule exposes no routes yet (command family lands in M4-1b-ii after contract 2 merges) - dual-leg schema witnesses (PGlite + real PG) for every constraint above; repository §6.4 witnesses: atomic commit, rollback residue-free, replay, conflicting-key refusal, events survive target deletion, CAS races - §6.3(a) route-inventory baseline: enumerates the AppModule route surface, pins zero hierarchy mutation routes until M4-1b-ii - writer-coverage scan perimeter widened from <pkg>/src to the full package tree (S1 residual; widened set measured free of all trigger tokens)
40 lines
3.0 KiB
SQL
40 lines
3.0 KiB
SQL
CREATE TYPE "public"."hierarchy_outbox_status" AS ENUM('pending', 'processing', 'delivered');--> statement-breakpoint
|
|
CREATE TABLE "hierarchy_audit_events" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"seq" bigint GENERATED ALWAYS AS IDENTITY (sequence name "hierarchy_audit_events_seq_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
|
|
"actor_id" text NOT NULL,
|
|
"verb" text NOT NULL,
|
|
"target_kind" text NOT NULL,
|
|
"target_id" uuid NOT NULL,
|
|
"target_snapshot" jsonb NOT NULL,
|
|
"transfer_from" jsonb,
|
|
"transfer_to" jsonb,
|
|
"correlation_id" text NOT NULL,
|
|
"causation_id" uuid,
|
|
"idempotency_key" text NOT NULL,
|
|
"occurred_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "hierarchy_audit_events_verb_check" CHECK (verb IN ('create', 'rename', 'transfer', 'delete', 'grant_create', 'grant_change', 'grant_revoke')),
|
|
CONSTRAINT "hierarchy_audit_events_target_kind_check" CHECK (target_kind IN ('company', 'estate', 'platform_project', 'grant')),
|
|
CONSTRAINT "hierarchy_audit_events_transfer_check" CHECK ((verb = 'transfer') = (transfer_from IS NOT NULL AND transfer_to IS NOT NULL))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "hierarchy_outbox" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"event_id" uuid NOT NULL,
|
|
"idempotency_key" text NOT NULL,
|
|
"correlation_id" text NOT NULL,
|
|
"status" "hierarchy_outbox_status" DEFAULT 'pending' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"delivered_at" timestamp with time zone
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "hierarchy_audit_events" ADD CONSTRAINT "hierarchy_audit_events_causation_id_hierarchy_audit_events_id_fk" FOREIGN KEY ("causation_id") REFERENCES "public"."hierarchy_audit_events"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "hierarchy_outbox" ADD CONSTRAINT "hierarchy_outbox_event_id_hierarchy_audit_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."hierarchy_audit_events"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "hierarchy_audit_events_idempotency_idx" ON "hierarchy_audit_events" USING btree ("idempotency_key");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "hierarchy_audit_events_seq_idx" ON "hierarchy_audit_events" USING btree ("seq");--> statement-breakpoint
|
|
CREATE INDEX "hierarchy_audit_events_target_seq_idx" ON "hierarchy_audit_events" USING btree ("target_id","seq");--> statement-breakpoint
|
|
CREATE INDEX "hierarchy_audit_events_correlation_idx" ON "hierarchy_audit_events" USING btree ("correlation_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "hierarchy_outbox_event_idx" ON "hierarchy_outbox" USING btree ("event_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "hierarchy_outbox_idempotency_idx" ON "hierarchy_outbox" USING btree ("idempotency_key");--> statement-breakpoint
|
|
CREATE INDEX "hierarchy_outbox_status_created_idx" ON "hierarchy_outbox" USING btree ("status","created_at"); |