T-HUB/docs/design/design-merged.md

Merged Design — Group Review & Signoff for Posts

Status: design for implementation (merge of three proposals + user decisions v2, pending audit) Date: 2026-10-07 (v2: connection-scoped approval delegation added per user decision) Inputs: runs/wp5/proposal-opus.md (rigor/defense-in-depth), runs/wp5/proposal-sol.md (submission lifecycle), runs/wp5/proposal-glm.md (minimal surface), spec runs/wp5/spec-review-workflow.md, user decisions 2026-10-07

Provenance legend: [OP], [SOL], [GLM] mark which proposal a decision comes from; [ALL] = all three converged; [MERGE] = this document's synthesis.

---

1. Executive summary — what this design is

1. [OP] The foundation is fixed first. Eight pre-existing defects (§2) are repaired in a prerequisite migration because the review feature multiplies each of them. Most critically: the scheduler currently publishes without checking draft state, and the audit hash does not cover the diff field (so "snapshot hash in the audit log" would not be tamper-evident).
2. [ALL] Group roles are a column on group_members — member | editor | reviewer, mutually exclusive by construction. The existing epoch trigger fires on UPDATE, so role changes invalidate sessions for free.
3. [GLM+OP] Approvals attach to immutable version rows with a database-computed hash. post_versions.content_hash is a Postgres generated column — it can never disagree with the content [GLM]. Votes copy the hash they approve; the scheduler publishes the approved version's bytes, never the mutable draft [OP]. [AUDIT #24] The defence order is explicit: §6.6 full-reset is the primary defence; the §4.2 schedule-time hash check is a backstop whose refusal is a 409 STALE_VERSION directing the author to re-submit — never a silent second path, never an approved-but-unschedulable draft.
4. [SOL+GLM] The review cycle is the unit of approval. One review_cycles row per (draft, round) pins version, group (nullable + group_name snapshot [AUDIT #16]), quorum, deadline, and status. Quorum config (workspace default + group override) is snapshotted at submit — config changes never move goalposts mid-review [ALL].
5. [ALL] A single deny is a veto — cycle closes denied, draft → needs_changes (replaces today's terminal rejected, unlocking resubmit). Deny asks for a reason in the UI (optional but suggested [USER v2]; recorded in thread + audit when given). Any content edit supersedes the cycle; resubmit always starts at 0/N.
6. [GLM] Chat is polling first (4 s cursor endpoint), over [SOL]'s unified event log: chat messages and system events are rows in one review_events table. Schema is transport-agnostic; SSE doorbells [OP] are a later upgrade.
7. [GLM] Deadlocks are refused at the door — with one defined exception [AUDIT #17]: submit runs an eligibility preflight ("not enough eligible reviewers (0) for quorum (2)") and 400s when quorum ≥ 2 is unreachable; when N=1 and 0 eligible reviewers exist, the cycle is still created, directly in blocked (blocked-at-submit), so the admin override always has a cycle to act on and quorum 1 never silently self-approves a solo author. A distinct blocked state handles mid-review reviewer-pool shrinkage. Admin override is a separate, reason-required action that writes no vote row [SOL+OP].
8. [GLM] published is the archive; moderation is flags, not states. Hide/delete are admin-only soft flags; the scheduler's scheduled→published write needs zero changes.
9. [USER v2] Approval authority is per-account (connection), chosen when the account is added. Every connection declares one of two approval modes: system — approvals flow through the group/workspace reviewer chain (the default), or delegated — the account's owner (whoever added/controls it) names the specific people who approve what goes out from that account. This scales to hundreds of accounts, each with its own approver pool. Quorum size and admin control remain group settings (§3.4).

New states: primed [SOL], needs_changes [ALL], expired [ALL], blocked [GLM]. The state machine is enforced twice: CAS UPDATEs in the service and a DB legal-transition trigger (defense in depth) [ALL].

---

2. Prerequisite fixes (WP5.0) — from [OP], all verified real

#	Where	Defect	Fix
D1	apps/scheduler/src/activities/publish.activity.ts:136-141	claim() joins drafts but never checks d.state; publishes any pending schedule row's d.post	claim requires state='scheduled' (plus the pending scheduled_publications row); publish post_versions.post of approved_version_id (post-011)
D2	publish.activity.ts:79	claim runs outside try; "not pending" throw retries forever (no retry limit)	move inside try; use non-retryable application error
D3	001_tenants.sql:92-98	audit_hash excludes diff — values recorded in diff are not tamper-evident	migration 010: hash v2 covers diff; verify both versions
D4	publishing.service.ts:136+	audit written after commit — crash window loses audit rows	same-tx audit_append via withTenant client (groups pattern, groups.service.ts:123-133)
D5	publishing.controller.ts:113-129 + rbac.guard.ts:64-74	schedule takes connectionId from body; RequireGroupAccess only checks :connectionId params	validate body connection against group scope via userCanUseConnection - kept as the API-level guard; 4.1(3) adds the composite FK + seal-time trigger as the DB backstop [SECURITY #9]
D6	realtime/gateway.ts:20-29	epoch compared against raw bigint string (always "stale"); membership query omits status='active'	normalize to text compare; add status filter (kept even though we don't build WS now)
D7	api-client.ts:90 + guard	any epoch bump 401s every user and the client drops the token — no re-issue	POST /auth/reissue: re-read live authz, mint fresh token; client retries once on epoch-401
D8	002_rbac_seed.sql:9,25,35	publication.delete seeded to Owner/Admin/Publisher, unenforced	do not reuse it for moderation; new dedicated perms (§3)

Migrations: 010_audit_hash_v2.sql (D3: hash_version column, audit_hash_v2 covering diff, advisory-lock append serialization, dual-version audit_verify_chain) — full SQL in proposal-opus.md §2.2.

---

3. Roles & permissions (R2)

3.1 Group roles [ALL]

ALTER TABLE group_members
  ADD COLUMN review_role text NOT NULL DEFAULT 'member'
    CHECK (review_role IN ('member', 'editor', 'reviewer'));

• member: author drafts, chat in review threads.
• editor: edit any group post's content; submit for review; cannot approve/deny/publish.
• reviewer: approve/deny (reason suggested, optional [USER v2]); cannot edit content.
• Mutually exclusive single column = structural separation of duties. Role changes bump the authz epoch (trigger already fires on UPDATE, 008_groups.sql:79-80); the service returns a {token} replacement for the caller (existing pattern, groups.service.ts:226-269) [ALL].
• Workspace roles remain the floor: any review role still needs draft.view via active membership.

3.2 New permissions [MERGE]

permission	granted to	purpose
review.manage	Owner, Admin	set workspace/group quorum, stale_after
approval.override	Owner, Admin	reason-required override of a blocked/expired cycle (writes no vote)
moderation.hide	Owner, Admin	hide a post (flag)
moderation.delete	Owner, Admin	soft-delete a post (flag)

Author exclusion is structural: eligibility SQL excludes the author, every row in draft_contributors [OP], and (for override) any contributor. Sealed-by also excluded [OP].

3.4 Group settings [USER v2]

Groups carry the review settings (writable by review.manage holders): quorum (review_approvals_required, NULL = inherit workspace default) and admin control (review_admin_control: whether Owner/Admin may exempt their own posts from review or override a stuck cycle in this group).

3.5 Connection approval modes [USER v2 — per-account delegation]

Every account (connection) declares how its approvals work, set/changed by the connection's controller (its owner / whoever added it) or workspace admins:

• system (default): the group's reviewer chain approves (§3.1).
• delegated: the controller names a dedicated approver pool for that account (connection_approvers). Posts targeting this account need signoff from that pool — same quorum N from the group settings, same structural author/contributor exclusion. Group reviewers do not count for a delegated account unless also named in its pool.
• Scale target: hundreds of connections, each with its own few approvers — "100 of accounts, and there are people per account who actually approve what goes out."
• Admin override/exempt still applies per the group's review_admin_control, regardless of mode.

3.3 Vote visibility [USER v2 — supersedes the merged blind default]

Fully transparent by default. Everyone in the group sees the complete vote ledger during review: who approved, who denied, who is yet to vote ("2/3 — Ana ✓, Ben ✓, Chi pending"). Deny reasons (when given) are visible inline in the thread. No server-side identity masking. A blind_votes group setting remains available for teams that want blind review later, but transparency is the default posture: [USER] "votes etc super transparent".

---

4. Data model — 011_review_workflow.sql

(Numbering: 009_video.sql already exists [OP, GLM caught this].)

4.1 Tables

-- (1) group roles — §3.1
-- (2) quorum config [GLM]
CREATE TABLE workspace_review_settings (
  workspace_id uuid PRIMARY KEY REFERENCES workspaces(id) ON DELETE CASCADE,
  approvals_required int NOT NULL DEFAULT 1 CHECK (approvals_required BETWEEN 1 AND 5),  -- [AUDIT #23] 0 is refused: no transition closes a required=0 cycle (deadlock-by-config)
  stale_after_days int NOT NULL DEFAULT 90 CHECK (stale_after_days BETWEEN 1 AND 3650),
  blind_votes boolean NOT NULL DEFAULT true,
  updated_by uuid REFERENCES users(id),
  updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE groups
  ADD COLUMN review_approvals_required int CHECK (review_approvals_required BETWEEN 1 AND 5),  -- [AUDIT #23] same floor as the workspace default
  ADD COLUMN review_blind_votes boolean,        -- NULL = inherit workspace default
  ADD COLUMN review_admin_control boolean NOT NULL DEFAULT true;  -- [USER v2]
-- Precedence resolved ONCE at submit: group row ?? workspace row ?? built-in default(1)
-- and snapshotted onto the cycle (§6).

-- (2b) per-account approval delegation [USER v2]
ALTER TABLE connections
  ADD COLUMN approval_mode text NOT NULL DEFAULT 'system'
    CHECK (approval_mode IN ('system','delegated'));
-- [AUDIT #9/#13] composite-FK target on connections: must exist before any FK that
--   references connections(id, workspace_id) (connection_approvers, post_versions)
ALTER TABLE connections ADD CONSTRAINT connections_id_ws_uk UNIQUE (id, workspace_id);

CREATE TABLE connection_approvers (
  workspace_id uuid NOT NULL,
  connection_id uuid NOT NULL,
  user_id uuid NOT NULL REFERENCES users(id),
  assigned_by uuid NOT NULL REFERENCES users(id),
  assigned_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (connection_id, user_id),
  FOREIGN KEY (connection_id, workspace_id) REFERENCES connections(id, workspace_id) ON DELETE CASCADE
);
-- Assignments: assigner must be the connection controller or hold connection.manage;
-- approver must hold an active workspace membership. Changes bump the authz epoch
-- (trigger, mirroring group_members) and revoke the removed approver's live votes.

-- (2c) [AUDIT #5] composite-FK target on drafts: must exist before any FK that
--   references drafts(id, workspace_id) (post_versions, draft_contributors,
--   review_cycles, review_events below)
ALTER TABLE drafts ADD CONSTRAINT drafts_id_ws_uk UNIQUE (id, workspace_id);

-- (3) immutable versions [GLM hash mechanics + SOL target pinning]
CREATE TABLE post_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL,
  draft_id uuid NOT NULL,
  version int NOT NULL CHECK (version >= 1),
  post jsonb NOT NULL,
  connection_id uuid,                            -- sealed target; NOT NULL when draft is group-scoped [SOL]
  content_hash text GENERATED ALWAYS AS (encode(digest(post::text, 'sha256'), 'hex')) STORED,
  seal_kind text NOT NULL CHECK (seal_kind IN ('submit','edit','reopen','migrated')),
  sealed_by uuid NOT NULL REFERENCES users(id),
  sealed_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (draft_id, version),
  UNIQUE (id, draft_id, workspace_id),
  FOREIGN KEY (draft_id, workspace_id) REFERENCES drafts(id, workspace_id) ON DELETE CASCADE,
  FOREIGN KEY (connection_id, workspace_id) REFERENCES connections(id, workspace_id) ON DELETE RESTRICT  -- [SECURITY #9, AUDIT #13] composite FK: same-workspace target, DB-enforced; RESTRICT so a connection referenced by a sealed version cannot be deleted out from under it (connections are soft-disabled instead)
);
-- [AUDIT #11] Single contract, stated once: versions are update-immutable and
--   cascade-deletable ONLY via `drafts` — the trigger rejects UPDATE outright and
--   rejects any DELETE whose root is not the drafts cascade (pg_trigger_depth() check);
-- BEFORE INSERT trigger requires connection_id when the draft has group_id [SOL].
-- [SECURITY #9] Seal-time guard (BEFORE INSERT - a database invariant; D5 stays as the
--   API-level guard): when connection_id IS NOT NULL the connection must belong to the
--   SAME workspace (composite FK above) AND be in scope for the draft's group:
--   drafts.group_id IS NULL OR EXISTS (SELECT 1 FROM group_connections gc
--     WHERE gc.connection_id = NEW.connection_id AND gc.group_id = drafts.group_id).
--   Any mismatch raises; a version is never sealed against a cross-tenant or
--   out-of-scope connection.

-- (4) contributors [OP]
CREATE TABLE draft_contributors (
  workspace_id uuid NOT NULL,
  draft_id uuid NOT NULL,
  user_id uuid NOT NULL REFERENCES users(id),
  first_at timestamptz NOT NULL DEFAULT now(),
  last_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (draft_id, user_id),
  FOREIGN KEY (draft_id, workspace_id) REFERENCES drafts(id, workspace_id) ON DELETE CASCADE
);
-- [AUDIT #10] Append-only and immutable: a BEFORE INSERT/UPDATE/DELETE trigger
--   rejects UPDATE and DELETE outright (contributor rows are history; last_at is
--   never rewritten), with the sole exception of the drafts cascade
--   (pg_trigger_depth() check). INSERT is the only legal mutation and it is
--   mandatory: every seal (submit/edit/reopen) inserts rows for the sealer and any
--   editor recorded since the last seal, in the same transaction — a post_versions
--   seal trigger fails the tx if the sealer has no draft_contributors row.

-- (5) review cycles [SOL shape, GLM snapshot semantics]
CREATE TABLE review_cycles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL,
  draft_id uuid NOT NULL,
  group_id uuid,                                 -- [AUDIT #16] nullable: group delete SET NULL, mirroring drafts
  group_name text,                               -- [AUDIT #16] group name snapshot at submit: audit trail survives group deletion
  round int NOT NULL CHECK (round >= 1),
  version_id uuid NOT NULL,
  required_approvals smallint NOT NULL CHECK (required_approvals BETWEEN 1 AND 5),  -- [AUDIT #23] snapshot inherits the same 1..5 floor
  policy_source text NOT NULL CHECK (policy_source IN ('group','workspace','default')),
  approval_source text NOT NULL DEFAULT 'group' CHECK (approval_source IN ('group','connection')),  -- [USER v2]
  blind_votes boolean NOT NULL,
  publish_at timestamptz,                        -- declared schedule ("primed to go out")
  deadline_at timestamptz NOT NULL,              -- LEAST(publish_at, submitted_at + stale_after)
  status text NOT NULL CHECK (status IN
    ('primed','in_review','approved','denied','superseded','expired','blocked','overridden')),
  submitted_by uuid NOT NULL REFERENCES users(id),
  submitted_at timestamptz NOT NULL DEFAULT now(),
  closed_at timestamptz,
  closed_reason text,
  UNIQUE (draft_id, round),
  UNIQUE (id, workspace_id),                     -- [AUDIT #5] composite-FK target for review_votes / review_overrides / review_events
  FOREIGN KEY (draft_id, workspace_id) REFERENCES drafts(id, workspace_id) ON DELETE CASCADE,
  FOREIGN KEY (group_id, workspace_id) REFERENCES groups(id, workspace_id)
    ON DELETE SET NULL (group_id),               -- [AUDIT #16] group deletion detaches the cycle; reference kept as history via group_name
  FOREIGN KEY (version_id, draft_id, workspace_id)
    REFERENCES post_versions(id, draft_id, workspace_id)
);

-- (6) votes [SOL constraints + OP hash binding]
CREATE TABLE review_votes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL,
  cycle_id uuid NOT NULL,
  voter_user_id uuid NOT NULL REFERENCES users(id),
  decision text NOT NULL CHECK (decision IN ('approve','deny')),
  reason text,                                   -- NOT NULL enforced for deny by CHECK
  snapshot_sha256 text NOT NULL CHECK (snapshot_sha256 ~ '^[0-9a-f]{64}$'),
  cast_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz,                        -- reviewer removed from group mid-review
  revoked_reason text,
  source text NOT NULL DEFAULT 'live' CHECK (source IN ('live','migrated')),
  UNIQUE (cycle_id, voter_user_id),
  CHECK (reason IS NULL OR btrim(reason) <> ''),   -- [USER v2] deny reason optional (UI-suggested); never stored blank
  CHECK (revoked_at IS NULL OR (revoked_reason IS NOT NULL AND btrim(revoked_reason) <> '')),
  FOREIGN KEY (cycle_id, workspace_id) REFERENCES review_cycles(id, workspace_id)
    ON DELETE CASCADE                            -- [AUDIT #1] cycle-scoped history, no independent lifecycle: draft→cycle cascade must not be blocked
);
-- [AUDIT #11] Same single contract as versions and events: update-immutable, cascade-deletable
--   only via `drafts` — the trigger rejects UPDATE of everything except the revoke fields
--   (set only by the revocation trigger) and rejects any DELETE whose root is not the
--   drafts cascade (pg_trigger_depth() check) [SOL].

-- (7) overrides [SOL: separate table, one per cycle]
CREATE TABLE review_overrides (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL,
  cycle_id uuid NOT NULL,
  admin_user_id uuid NOT NULL REFERENCES users(id),
  reason text NOT NULL CHECK (btrim(reason) <> ''),
  snapshot_sha256 text NOT NULL CHECK (snapshot_sha256 ~ '^[0-9a-f]{64}$'),  -- [AUDIT #19] same format constraint as review_votes
  at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (cycle_id),
  FOREIGN KEY (cycle_id, workspace_id) REFERENCES review_cycles(id, workspace_id)
    ON DELETE CASCADE                            -- [AUDIT #1] cycle-scoped history, no independent lifecycle: draft→cycle cascade must not be blocked
);
-- append-only; BEFORE INSERT trigger: admin is Owner/Admin, NOT a contributor, cycle is blocked/expired,
--   AND [AUDIT #19] NEW.snapshot_sha256 = (SELECT content_hash FROM post_versions WHERE id = cycle.version_id)
--   — an override can only ratify the exact content the cycle pinned. The transition trigger's
--   override-row requirement (4.2 enforce_review_transition) is the second layer.

-- (8) unified event log = chat + system events [SOL]
CREATE TABLE review_events (
  workspace_id uuid NOT NULL,
  draft_id uuid NOT NULL,
  seq bigint NOT NULL CHECK (seq > 0),           -- gapless per draft, allocated under draft row lock
  cycle_id uuid,
  kind text NOT NULL CHECK (kind IN
    ('message','submitted','edited','approved','denied','vote_revoked','quorum_met',
     'quorum_lost','needs_changes','blocked','unblocked','scheduled','expired',
     'published','hidden','unhidden','deleted','override')),
  actor_user_id uuid REFERENCES users(id),       -- NULL = system
  body text,                                     -- required for kind='message'
  data jsonb NOT NULL DEFAULT '{}',
  CHECK (kind <> 'message' OR (body IS NOT NULL AND btrim(body) <> '')),  -- [AUDIT #18] message body must be non-empty
  CHECK (octet_length(coalesce(body, '')) <= 8192),                      -- [AUDIT #18] bounded body: 8 KiB hard cap
  CHECK (octet_length(data::text) <= 8192),                              -- [AUDIT #18] bounded event payload: 8 KiB hard cap
  at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (draft_id, seq),
  FOREIGN KEY (draft_id, workspace_id) REFERENCES drafts(id, workspace_id) ON DELETE CASCADE,
  FOREIGN KEY (cycle_id, workspace_id) REFERENCES review_cycles(id, workspace_id)  -- [AUDIT #20, #22] nullable composite FK to review_cycles(id, workspace_id), action consistent with #1/#2; system events carry the cycle they narrate (feasible via the UNIQUE (id, workspace_id) from #5); [AUDIT #22] the cycle-narrating kinds (denied/override/quorum_met/etc.) can never be orphaned from their cycle
);
-- [AUDIT #11] Same single contract as versions and votes: update-immutable, cascade-deletable
--   only via `drafts` — UPDATE rejected; DELETE rejected unless its root is the drafts
--   cascade (pg_trigger_depth() check); kind='message' requires non-empty body.
-- [AUDIT #18] Size caps are table CHECKs, not conventions: body ≤ 8192 bytes (non-empty when
--   kind='message') and data ≤ 8192 bytes — oversize writes are refused by the database.
-- [SECURITY #7] review_events policy (RLS IN ADDITION to tenant_isolation; also a
--   mandatory service-layer precondition, see 4.2):
--   SELECT ... USING: caller holds draft.view AND (drafts.group_id IS NULL OR EXISTS (
--     SELECT 1 FROM group_members gm WHERE gm.group_id = drafts.group_id
--       AND gm.user_id = current_setting('app.user_id')::uuid AND gm.status = 'active'))
--   INSERT of kind='message' WITH CHECK: that same predicate AND an active
--     group_members row with review_role IN ('member','editor','reviewer') for
--     drafts.group_id; system kinds are inserted only via the SECURITY DEFINER
--     service path. Fails closed: no membership => zero rows on read, refused write.

-- (9) drafts columns
ALTER TABLE drafts
  ADD COLUMN group_id uuid,
  ADD COLUMN revision int NOT NULL DEFAULT 0,    -- optimistic concurrency, bumped on save
  ADD COLUMN current_version_id uuid,
  ADD COLUMN approved_version_id uuid,
  ADD COLUMN event_seq bigint NOT NULL DEFAULT 0,
  ADD COLUMN hidden_at timestamptz, ADD COLUMN hidden_by uuid REFERENCES users(id),
  ADD COLUMN hidden_reason text,
  ADD COLUMN deleted_at timestamptz, ADD COLUMN deleted_by uuid REFERENCES users(id),
  ADD COLUMN deleted_reason text,
  ADD CONSTRAINT drafts_group_fk FOREIGN KEY (group_id, workspace_id)
    REFERENCES groups(id, workspace_id) ON DELETE SET NULL (group_id),
  ADD CONSTRAINT drafts_curver_fk FOREIGN KEY (current_version_id, id, workspace_id)
    REFERENCES post_versions(id, draft_id, workspace_id),
  ADD CONSTRAINT drafts_appver_fk FOREIGN KEY (approved_version_id, id, workspace_id)
    REFERENCES post_versions(id, draft_id, workspace_id),
  ADD CONSTRAINT drafts_appver_state CHECK (                          -- [AUDIT #21] a live approval pointer exists only in approval-or-later states
    approved_version_id IS NULL OR state IN ('approved','scheduled','published')),
  ADD CONSTRAINT drafts_hidden_consistent CHECK (
    (hidden_at IS NULL AND hidden_by IS NULL AND hidden_reason IS NULL)
    OR (hidden_at IS NOT NULL AND hidden_by IS NOT NULL AND btrim(coalesce(hidden_reason,'')) <> '')),
  ADD CONSTRAINT drafts_deleted_consistent CHECK (
    (deleted_at IS NULL AND deleted_by IS NULL AND deleted_reason IS NULL)
    OR (deleted_at IS NOT NULL AND deleted_by IS NOT NULL AND btrim(coalesce(deleted_reason,'')) <> ''));

-- state set: drop 006 CHECK, install
CHECK (state IN ('editing','primed','in_review','needs_changes','blocked',
                 'approved','scheduled','published','expired'))

Every new table: denormalized workspace_id + composite FKs, reject_workspace_change trigger, ENABLE+FORCE ROW LEVEL SECURITY with the exact tenant_isolation NULLIF policy, GRANT … TO sp_app — shapes copied verbatim from 006/008 [ALL].

4.2 Key SQL functions (SECURITY DEFINER, search_path pinned, sp_app-only [OP conventions])

• review_events access contract (binding): enforced as an RLS policy in addition to tenant_isolation and re-checked as a mandatory service-layer precondition on every read/write of the table. Read requires draft.view AND (drafts.group_id IS NULL OR an active group_members row for drafts.group_id). Writes of kind='message' additionally require review_role IN ('member','editor','reviewer') in that group. Fails closed: missing membership/permission yields no rows (read) or an exception (write). The GET /publishing/drafts/:id/events endpoint inherits exactly this predicate - never a generic RbacGuard + RLS passthrough. [AUDIT #18] The same endpoint serves reads only through the bounded-page contract of §7 (limit default 100 / max 500 with next_cursor) — there is no unbounded read of the event log.

• Moderation visibility contract (binding) [AUDIT #15]: every draft read endpoint — list, detail, versions, diffs, events — applies drafts.hidden_at IS NULL AND drafts.deleted_at IS NULL unless the caller holds moderation.hide (hidden rows) / moderation.delete (deleted rows). Enforced as an RLS policy on drafts (and, via the parent draft, on post_versions/review_events) plus a shared repository filter used by every query path — never only a UI/archive filter. Fails closed: a non-admin direct request for a hidden/deleted draft, its versions, diffs or events yields 404/no rows.

• review_eligible_reviewers(draft uuid, workspace uuid) → set of user ids: [USER v2] if the draft's connection is delegated, its connection_approvers; else group role reviewer — always ∩ active membership ∩ NOT author ∩ NOT contributor ∩ NOT sealer of current version. One function used by the service, the vote trigger, and the quorum count [OP: single source of truth]. [SECURITY #8] The body starts with fail-closed tenant validation: ASSERT the target draft's workspace_id equals the workspace argument (and, when invoked for a session, that current_setting('app.workspace_id') IS NOT NULL and equals it); mismatch raises immediately - no rows, no cross-tenant leak.

• review_valid_approval_count(cycle uuid, workspace uuid) → count of live (not revoked) approve votes from eligible reviewers whose snapshot_sha256 matches the cycle's version hash [SOL]. [SECURITY #8] Same contract: the body first asserts the cycle's workspace_id equals the workspace argument (and matches current_setting('app.workspace_id') when session-scoped). The single-UUID signatures are removed; every caller passes both arguments.

• review_vote_eligible() trigger: refuses votes unless cycle in_review, voter eligible (re-checked live), snapshot hash matches [SOL+OP]. [AUDIT #14] The cycle-state and live-eligibility checks apply only when NEW.source = 'live' (refusal condition is ... AND NEW.source = 'live'), so source='migrated' backfill rows (§11) — inserted into already-approved cycles, possibly by a legacy reviewer who is now author/contributor or lacks the reviewer role — bypass them; the snapshot-hash match and all CHECK constraints are still enforced for every row. Only the 011 migration role may insert source='migrated' (sp_app inserts with source <> 'live' are refused).

• enforce_review_transition() trigger on drafts: the legal-pairs table in §5; refuses post/group_id changes outside editing/needs_changes; refuses approved without a closed quorum or an override row [AUDIT #19] whose snapshot_sha256 equals the cycle version's content_hash — this override-row requirement is the second layer on top of the 4.1(7) override insert trigger; refuses scheduled unless drafts.post hash equals the approved version's [SOL] — [AUDIT #24] §6.6 full-reset is the primary defence (an edit after approval supersedes the cycle before scheduling can even be attempted); this hash check is the backstop for any gap in it, and its refusal surfaces as 409 STALE_VERSION directing the user to re-submit — no approved-but-unschedulable state without a recovery transition. [AUDIT #12] → expired is legal only from the pre-publication set primed|in_review|blocked|approved — the trigger refuses scheduled → expired and published → expired. [SECURITY #9] It also refuses scheduled unless the approved version's connection_id satisfies the 4.1(3) seal-time guard (same workspace, in scope for drafts.group_id) - re-validated at the transition, not only at seal time.

• assign_event_seq() trigger: event_seq := event_seq + 1 under the draft row lock; gapless per draft [OP]. [AUDIT #26] Global lock order (binding for every path that touches review state): authz rows (group_members / connection_approvers) → draft row → cycle row → votes/events. The vote path takes the draft row FOR UPDATE (seq + quorum + deadline check) before the cycle row FOR UPDATE and only then inserts the vote/event; the membership-change path takes authz rows → draft → cycle → votes/events in the same order. No path may lock a cycle row before its draft row.

• review_due_expiries() sweeper discovery, mirroring publishing_due_schedules() (006:79-85) [OP]. [SECURITY #8] It takes no row argument, so it runs strictly under the caller's tenant context: every candidate cycle must satisfy workspace_id = current_setting('app.workspace_id')::uuid; a NULL/empty setting selects zero rows (fail-closed), never all tenants.

• Vote revocation on membership/role change: trigger on group_members UPDATE/DELETE revokes that user's live votes on open cycles in the group (sets revoked_at/reason), then recount — quorum already met stays met [OP, fixed lock order: authz rows → draft → cycle → votes/events — the [AUDIT #26] global order above, identical to the vote path].

---

5. State machine (deliverable 3)

from → to	fired by	guard
editing → primed	author/editor stages (choose group + connection + optional publish_at)	group has ≥ N eligible reviewers (preflight, 400 if not) — solo exception [AUDIT #17]: N=1 with 0 eligible reviewers still primes (workspace approval.grant holders are the fallback pool, and if that is empty too, submit lands blocked-at-submit) [GLM]
needs_changes → primed	author re-stages after edits	same preflight
expired → primed	author re-primes with new schedule	same preflight
primed → in_review	submit (seals version n, opens cycle)	version sealed; policy snapshotted; deadline = LEAST(publish_at, now+stale_after); same tx inserts draft_contributors rows for the sealer + any editor since the last seal [AUDIT #10, seal trigger enforces]
in_review → approved	quorum_met (Nth valid approve, same tx)	row lock + clock_timestamp() < deadline_at else → expired [ALL]
in_review → needs_changes	deny (veto)	reason optional, UI prompts for it [USER v2]
in_review → editing	author withdraws for edits	cycle superseded
in_review → blocked	recount after reviewer removal	eligibility < required [GLM]
approved → scheduled	quorum tx (auto, if publish_at future)	same tx inserts scheduled_publications, starts workflow with startDelay [ALL]
approved → editing	late edit	pending schedule cancelled in same tx; cycle superseded; loud dismissal event; [AUDIT #21] approved_version_id = NULL is set in the same tx as the supersede — between supersede and the next approval no reader (D1's claim check included) can dereference a stale approval
scheduled → approved	cancelSchedule (existing)	unchanged from 006
scheduled → published	scheduler activity	D1 fix: claim requires state='scheduled' AND a pending scheduled_publications row, and publishes post_versions.post of approved_version_id
published → (none)	archive = filtered read of published	no state change [GLM]
primed, in_review, blocked, approved → expired	sweeper/vote-time check when deadline passes	pre-publication (deadline-bearing) states only — the explicit set `primed
blocked → in_review	reviewer added (auto-recount)	eligibility ≥ required again [GLM]
blocked/expired → approved	admin override	reason mandatory; not a contributor; cycle closed overridden [SOL+OP]

Service returns 400/403/409 with precise codes (REVIEW_EXPIRED, QUORUM_UNREACHABLE, STALE_VERSION, VOTE_INELIGIBLE); the DB trigger is the backstop, not the error surface [SOL].

---

6. Quorum engine semantics (deliverable 5)

1. Config: groups.review_approvals_required ?? workspace_review_settings.approvals_required ?? 1, resolved once at submit, stored on the cycle with policy_source. Default 1 preserves today's behavior for solo workspaces as a blocked-at-submit cycle, never a self-approval [AUDIT #17]: with N=1 and 0 eligible reviewers the cycle is created in blocked and is resolved by a fallback reviewer (below) or the admin override. ≥2 turns on structural four-eyes [GLM]. Admin-only setters (review.manage). [AUDIT #23] required is bounded to 1–5 at every layer (workspace §4.1(2), group §4.1(2), cycle snapshot §4.1(5)): required=0 has no closing rule — no transition fires quorum_met without a vote and submit-time auto-approve is deliberately not defined — so it is refused by CHECK at the door, exactly the deadlock-by-config class §6.8 exists to refuse.
2. Parallel any-N-of-eligible [ALL]. No tiers (noted as future). Eligibility pool [USER v2]: if the draft's target connection is delegated, the pool is its connection_approvers (minus author/contributors); otherwise the group's reviewers. Solo fallback [AUDIT #17]: when that pool is empty, workspace approval.grant holders (Owner/Admin/Approver, minus author/contributors) become eligible; if they are empty too, submit lands blocked-at-submit with the admin override as the only exit. Resolved at submit and snapshotted on the cycle (approval_source); mode changes affect future cycles only.
3. Vote rules: one live vote per reviewer per cycle (UNIQUE); decision immutable after cast — a reviewer may not flip; they may deny later if they first approved? No: one vote total, immutable. To change course, the deny path is a new cycle after withdrawal — keep it strict [MERGE, strictest of the three].
4. Deny = veto [ALL]: cycle closes denied, draft needs_changes [USER v2: deny reason optional but suggested — the UI asks "why?" and records it in chat + audit when given; this supersedes the spec's mandatory-reason rule]. Sibling approvals remain as history; count for nothing (cycle-scoped tally).
5. Staleness: every vote copies the cycle's version hash; the vote trigger rejects a hash mismatch (409 STALE_VERSION — can't happen given full-reset, backstop only) [SOL].
6. Edit = full reset [ALL]: any content/target change after seal supersedes the cycle, cancels any pending schedule in the same tx, seals version n+1 on resubmit, and emits a edited event listing dismissed approvers with a diff link (loud dismissal [OP]). [AUDIT #10] Same-tx contributor contract (binding): every seal (submit/edit/reopen) inserts draft_contributors rows for the sealer and for any editor recorded since the last seal, in the seal transaction itself; the post_versions seal trigger fails the transaction if the sealer is absent — eligibility can never miss a contributor, and existing rows are immutable (§4.1(4)).
7. Scheduled-time race [ALL]: quorum handler, holding the draft row lock, checks clock_timestamp() < publish_at; if true → schedule in same tx; else → expired. Submissions require publish_at ≥ now() + 120 s (min lead). Correctness never depends on sweeper latency.
8. Deadlock [GLM]: preflight at priming (§5) + blocked state on mid-review shrinkage + override exit (§5). blocked ≠ needs_changes: "edit the post" is wrong advice when the problem is staffing.

---

7. Review chat & delivery (deliverable 6)

• One table, both kinds [SOL]: review_events — message rows are human chat; everything else is system events rendered inline (submitted, votes fully attributed — transparent ledger, reasons shown when given [USER v2] — state changes, overrides, moderation).
• Transport = polling [GLM]: GET /publishing/drafts/:id/events?after=<seq>&limit=<n> while a detail view is open (4 s); queue lists refresh on action or 15 s. Every poll rides the existing epoch-fenced RbacGuard + RLS, i.e. the 4.2 review_events policy (read = draft.view AND (ungrouped draft OR active group_members row for drafts.group_id); posting a message additionally requires review_role IN ('member','editor','reviewer') in that group) — revocation latency = poll interval, no new credential surface (EventSource can't send our Bearer header).
• Bounded pages [AUDIT #18]: limit defaults to 100 and is clamped to 1–500; each response returns at most limit events plus next_cursor = the seq of the last row returned (omitted when the page is not full). Clients loop ?after=<next_cursor> until no cursor is present, so a 4 s poll — or a backlog catch-up — always returns a bounded page (≤ 500 events), never an unbounded response.
• Ordering is structural: gapless seq allocated under the draft row lock; the cursor is a total order; lost/duplicated polls are harmless (?after=).
• Upgrade path: SSE doorbells + LISTEN/NOTIFY [OP] later; identical after=<seq> query, no schema change.
• Approve/Deny buttons live in the chat surface (R5): approve = one click; deny = reason modal (required).

---

8. UI plan (deliverable 7)

1. Review queue (replaces the /approvals stub): virtualized column list (reuse components/streams/column.tsx pattern, @tanstack/react-virtual, j/k navigation) of drafts in primed/in_review/needs_changes/blocked/expired; state chips (STATE_TONE extended); quorum progress ("2/3 ✓"); filters: needs-my-review, mine, needs-changes, expiring soon, blocked.
2. Post detail (builds on Compose patterns): rendered post preview + version diff (v n-1 → n), side chat/event stream with vote buttons, quorum tracker, schedule info, contributor list.
3. Compose additions: "Prime for review" action (group + connection + optional schedule), replacing the current self-approve chain when quorum ≥ 2.
4. Archive: filtered read of published (chat history preserved, read-only), admin sees hidden posts with a badge + unhide/delete actions.
5. Team/group screens: review_role selector on group members; group settings (quorum, admin control) [USER v2].
6. Connections screen additions [USER v2]: per-account approval settings — mode picker (system vs delegated to named approvers), approver pool editor for delegated accounts, shown on the connection detail; the queue groups/filters by account so a delegated approver sees "awaiting my signoff" across the accounts they own.

---

9. Edge cases (deliverable 8) — each with its resolution

1. Reviewer leaves mid-review → votes revoked by trigger (reason), recount; met quorums stay met; unmet → blocked [OP lock order: authz rows → draft → cycle → votes/events — global order [AUDIT #26], shared with the vote path so a concurrent vote cannot invert draft/cycle locks].
2. Deadline race (vote vs expiry) → both take FOR UPDATE on the draft; vote-time expiry check; loser sees REVIEW_EXPIRED [ALL].
3. Sole reviewer is the author → with quorum ≥ 2: preflight 400 at priming. With quorum 1: prime/submit succeeds — the pool falls back to workspace approval.grant holders [AUDIT #17], and if that pool is empty too the cycle is created blocked-at-submit awaiting admin override; never enters an unresolvable state [GLM].
4. Admin who contributed tries to override → refused structurally (contributors table) [OP].
5. Quorum met after publish_at → expired, nothing scheduled, author notified, re-prime path [ALL].
6. Concurrent Nth and (N+1)th vote → both under row lock; second lands as history after cycle closed (no double schedule: UNIQUE(draft_id) on scheduled_publications backstop).
7. Epoch bump mid-vote (role change logs reviewer out) → POST /auth/reissue + client single retry (D7 fix) [OP].
8. Group deleted while cycles open → ON DELETE SET NULL (group_id) frees the draft and sets review_cycles.group_id to NULL the same way [AUDIT #16] — each cycle keeps its group_name snapshot so the audit trail stays readable, and superseding a cycle preserves the reference as history rather than deleting it; open cycles superseded by group-delete guard trigger; audit event [OP].
9. Workspace owner cleanup deletes drafts → cascades flow (versions, cycles, votes, events) under the single §4.1 contract — [AUDIT #1] review_votes and review_overrides reference review_cycles with ON DELETE CASCADE, so drafts → cycles → votes/overrides is never blocked by a NO ACTION FK: events/versions/votes are update-immutable and cascade-deletable only via drafts; direct DELETEs are refused by the trigger [GLM, AUDIT #11].
10. Hidden post with open cycle → hide is metadata only; cycle continues (admins act with full information) [MERGE].
11. Connection approval mode switched mid-review [USER v2] → the cycle snapshotted approval_source at submit; open cycles keep their pool, new cycles use the new mode. Mode change emits a thread event for visibility.
12. Delegated pool shrinks below N (approver removed/leaves) → same blocked handling as group reviewers: recount trigger revokes their live votes, blocked if quorum unreachable, admin override or pool restore resolves.
13. Preflight for delegated accounts counts the connection pool, not group reviewers: "Account X needs 2 approvals; pool has 1" at priming.

---

10. Audit events (deliverable 9) — all through audit_append, same-tx (D4 fixed)

review.primed, review.submitted (version hash, policy snapshot, approval_source), review.vote_cast (decision; reason when provided; snapshot hash), review.vote_revoked, review.quorum_met, review.denied, review.cycle_superseded, review.blocked, review.unblocked, review.overridden (admin, reason), review.expired, review.hidden/unhidden/deleted (moderation, reason), review.policy_changed, review.connection_delegation_changed (mode/pool changes [USER v2]). Scheduler keeps publication.published/failed as sp_scheduler. Hash v2 (010) makes every diff value — snapshot hashes, reasons, policy before/after — tamper-evident [OP].

---

11. Migration & backfill (deliverable: Q9) — 011, per-tenant set_config before guard triggers

legacy state	new state	cycle	votes
editing	editing	none	none
in_review	needs_changes	none (author re-primes)	none
rejected	needs_changes	none	none
approved/scheduled/published	unchanged	backfilled cycle required=1, status=approved, v1 from current post	one synthetic approve vote (source='migrated') by legacy reviewer_user_id where present — exempt from the live cycle-state/eligibility checks of review_vote_eligible() via its NEW.source = 'live' guard (§4.2), hash still verified [AUDIT #14]
(none)	—	—	legacy deny reasons recovered from audit_log.diff->>'reason' on draft.rejected, else migration note

reviewer_user_id becomes read-only/deprecated; dropped in a later 012 once web stops reading it. Legacy self-approved drafts are kept as history and counted in a review.migrated audit row [OP].

---

12. Phased implementation (deliverable 10)

• WP5.0 — Foundation fixes (010): D1–D8. Gate: existing suites green + new regression tests for claim-state-check and audit hash v2.
• WP5.1 — Schema (011): tables, triggers, functions, RLS, backfill. Gate: migration up/down on dev DB; audit_verify_chain() true post-backfill.
• WP5.2 — Service + API: priming/submit/vote/override/moderation endpoints, quorum engine, event log writes; every draft read endpoint (list, detail, versions, diffs, events) applies the §4.2 moderation visibility contract. Gate: integration tests (RLS, transitions, quorum, races) on live Postgres, including admin vs non-admin reads of hidden/deleted drafts across all five read endpoints.
• WP5.3 — Scheduler contract: claim eligibility + approved-version publishing + expiry sweeper. Gate:Temporal tests incl. deadline race.
• WP5.4 — Web: queue, detail+chat, compose changes, archive, role/quorum admin. Gate: E2E on real API; visual QA.
• WP5.5 — Hardening: lock-order verification, load test on event seq allocation, docs.

13. Test plan sketch (deliverable 11) — the 10 that catch the worst

1. Claim refuses any draft not in state='scheduled' with a pending scheduled_publications row, and publishes post_versions.post of approved_version_id (D1). 2. Audit verify passes with v1+v2 rows. 3. Author/contributor vote → 403. 4. Deny without reason succeeds; deny with reason records it in thread + audit [USER v2]. 5. Nth vote under lock schedules exactly once. 6. Vote at deadline → expired, not scheduled. 7. Edit after approval cancels schedule + supersedes cycle same-tx. 8. Reviewer removal revokes votes; met quorum survives. 9. Preflight blocks solo-author quorum-2 priming. 10. Migration backfill: verify chain + counts + resubmit path from legacy rejected. 11. Delegated account: group reviewer's vote → 403; pool member's vote counts [USER v2]. 12. Mode switch mid-review: open cycle keeps its pool [USER v2]. 13. Delegated pool shrink → blocked + override path [USER v2]. 14. Transition-table test [AUDIT #12]: enforce_review_transition() refuses scheduled → expired and published → expired; → expired succeeds from each of primed, in_review, blocked, approved. 15. Seal/contributor test [AUDIT #10]: a seal transaction that does not insert a draft_contributors row for the sealer fails; UPDATE/DELETE of an existing contributor row fails outside the drafts cascade. 16. Edit-after-approval-then-schedule [AUDIT #24]: both paths — (a) primary: an edit after approval supersedes the cycle and cancels the pending schedule in the same tx (draft back to editing, approved_version_id = NULL); (b) backstop: approved → scheduled while the drafts.post hash no longer equals the approved version's is refused with 409 STALE_VERSION and the UI directs the author to re-submit. 17. Draft-delete cascade [AUDIT #1]: under the real 011 migration, deleting a draft that has cast votes (live and revoked) and an overridden cycle with a review_overrides row succeeds and removes its cycles, votes, overrides, events and versions; a direct DELETE of a vote/override row is still refused.

14. Confidence & risks

• Highest confidence: schema shape, single-veto deny, full-reset staleness, cycle-snapshotted quorum (all three proposals converged independently).
• Least sure: (a) gapless seq allocation under extreme concurrency — measure contention in WP5.5; (b) blind-vote redaction completeness (audit page leak accepted, documented); (c) D7 reissue flow client behavior across all screens.
• Prototype first: WP5.0 + WP5.1 on a copy of dev data, run tests 1, 5, 6, 8 — everything whose failure would be silent.