# Review App — Concrete Pattern Reference

> **Purpose:** a precise, quotable technical reference to the review app
> (`/srv/apps/review`) so Volleyboard can **reimplement** (not copy-paste) its
> proven mechanics in the new Vite+React+TypeScript+Tailwind+FastAPI stack,
> without re-reading the original source. Every value, class, and function
> body below is transcribed verbatim from the source files listed in each
> section header.
>
> This is a **companion, not a duplicate**, of two other docs in this folder:
> `review-learnings.md` (the "why" — principles + volleyball metaphor) and
> `plan-v1.md` (the target design). This doc is the "what, exactly" — the
> literal strings, classes, SQL, and function bodies to port faithfully.
>
> Where the target stack (Postgres+SQLAlchemy/FastAPI/SSE) changes *where* a
> mechanism lives rather than *what* it does, a short *Port note* says so.
> Everything else here is a faithful transcription of the review app as it
> exists today (migrations `0001`–`0017`, components as of 2026-07-16).

**Contents**
1. [Vocabularies](#1-vocabularies) — every enum, exact colors, authoritative DB constraints
2. [Automations](#2-automations) — status↔review coupling, issue reassignment, notification triggers
3. [UX mechanics](#3-ux-mechanics) — inline-edit pills, StatChips, filters, mentions, image paste, bell
4. [Gotchas](#4-gotchas) — hard-won non-obvious details, quoted from code comments

---

## 1. VOCABULARIES

Source: `src/lib/types.ts`, `src/lib/status-styles.ts`, plus the authoritative
`CHECK` constraints chased forward through every migration that touches them
(vocab lists drift across migrations — see the callout in §1.2 — so the
constraint quoted here is always the **final, current** one, not necessarily
the first one you'd find grepping).

### 1.1 Page Status — `pages.status` (UI column: "Status")

```ts
// src/lib/types.ts
export const PAGE_STATUSES = [
  "open", "creating", "finalizing", "revision pending",
  "revising", "drafted", "completed",
] as const;
export type PageStatus = (typeof PAGE_STATUSES)[number];
```

Authoritative constraint (defined `0001_init.sql`, re-asserted unchanged by
`0003_status_review_vocab.sql`; never altered again):

```sql
alter table pages add constraint pages_status_check
  check (status in ('open', 'creating', 'finalizing', 'revision pending', 'revising', 'drafted', 'completed'));
```

Colors (`src/lib/status-styles.ts`) — values are **all-lowercase**; display
casing comes from a CSS `capitalize` utility class on the consuming component
(see §3.1), not from the stored string:

```ts
export const STATUS_STYLES: Record<PageStatus, string> = {
  open: "bg-muted text-muted-foreground border-border",
  creating:
    "bg-orange-100 text-orange-800 border-orange-200 dark:bg-orange-950 dark:text-orange-200 dark:border-orange-900",
  finalizing:
    "bg-yellow-100 text-yellow-800 border-yellow-200 dark:bg-yellow-950 dark:text-yellow-200 dark:border-yellow-900",
  // grey-yellow / khaki
  "revision pending":
    "bg-[#ebe7cd] text-[#6d6837] border-[#d8d2a6] dark:bg-[#3a3720] dark:text-[#cbc488] dark:border-[#55502d]",
  // chartreuse
  revising:
    "bg-lime-100 text-lime-800 border-lime-200 dark:bg-lime-950 dark:text-lime-200 dark:border-lime-900",
  drafted:
    "bg-green-100 text-green-800 border-green-200 dark:bg-green-950 dark:text-green-200 dark:border-green-900",
  completed:
    "bg-blue-100 text-blue-800 border-blue-200 dark:bg-blue-950 dark:text-blue-200 dark:border-blue-900",
};

export const STATUS_DOT: Record<PageStatus, string> = {
  open: "bg-muted-foreground/50",
  creating: "bg-orange-500",
  finalizing: "bg-yellow-500",
  "revision pending": "bg-[#b8ad5e]",
  revising: "bg-lime-500",
  drafted: "bg-green-500",
  completed: "bg-blue-500",
};
```

Note the **khaki is a raw hex triad**, not a Tailwind palette step — Tailwind
has no built-in "khaki," so it's hand-picked: light `#ebe7cd` / `#6d6837` /
`#d8d2a6`, dark `#3a3720` / `#cbc488` / `#55502d`, dot `#b8ad5e`. **These exact
hex values are reused identically for the Signoff vocab's "revision not
ready"** (§1.2) — deliberate color-sharing across two different columns for
the two closest-related concepts.

### 1.2 Review / Signoff — `pages.signoff` (DB column `signoff`, **UI label "Review"**)

```ts
// src/lib/types.ts
// The "Review" column. Stored in the `pages.signoff` DB column for historical
// reasons; the UI labels it "Review".
export const SIGNOFF_OPTIONS = [
  "not ready", "ready for review", "revision not ready",
  "revision needed", "approved",
] as const;
export type Signoff = (typeof SIGNOFF_OPTIONS)[number];
```

**Vocab history wobble — chase the constraint forward, don't trust the first
migration you find:**
- `0001_init.sql` defined the constraint with **5** values including `revision not ready`.
- `0003_status_review_vocab.sql` dropped and re-added `pages_signoff_check` but
  **silently omitted `revision not ready`** (down to 4 values) while doing an
  unrelated data-migration pass (old values like `'Not Reviewed'` → `'not
  ready'`). This looks like an unintentional regression, not a deliberate vocab cut.
- `0009_review_revision_not_ready.sql` restored it. **This is the final,
  current constraint** (unchanged through `0017`):

```sql
alter table pages add constraint pages_signoff_check
  check (signoff in ('not ready', 'ready for review', 'revision not ready', 'revision needed', 'approved'));
```

Colors:

```ts
export const SIGNOFF_STYLES: Record<Signoff, string> = {
  "not ready": "bg-muted text-muted-foreground border-border",
  "ready for review":
    "bg-orange-100 text-orange-800 border-orange-200 dark:bg-orange-950 dark:text-orange-200 dark:border-orange-900",
  // yellow-grey / khaki
  "revision not ready":
    "bg-[#ebe7cd] text-[#6d6837] border-[#d8d2a6] dark:bg-[#3a3720] dark:text-[#cbc488] dark:border-[#55502d]",
  "revision needed":
    "bg-lime-100 text-lime-800 border-lime-200 dark:bg-lime-950 dark:text-lime-200 dark:border-lime-900",
  approved:
    "bg-blue-100 text-blue-800 border-blue-200 dark:bg-blue-950 dark:text-blue-200 dark:border-blue-900",
};

export const SIGNOFF_DOT: Record<Signoff, string> = {
  "not ready": "bg-muted-foreground/50",
  "ready for review": "bg-orange-500",
  "revision not ready": "bg-[#b8ad5e]",
  "revision needed": "bg-lime-500",
  approved: "bg-blue-500",
};
```

Note the **color mirroring** in the revision sub-loop: when `status` is khaki
(`revision pending`), `signoff` is lime (`revision needed`); when `status`
flips to lime (`revising`), `signoff` flips to khaki (`revision not ready`).
Khaki always means "waiting on the other column"; lime always means "this is
the actively-happening state." Reproduce this pairing, not just the two
palettes independently.

### 1.3 Note Category / Issue Category — `notes.category`, `issues.category` (shared vocab)

```ts
// src/lib/types.ts
export const NOTE_CATEGORIES = [
  "Revision", "Bug", "Question", "Live Changelog", "Informational",
] as const;
export type NoteCategory = (typeof NOTE_CATEGORIES)[number];
export const DEFAULT_NOTE_CATEGORY: NoteCategory = "Revision";
```

Values are **Title Case already** (unlike §1.1/§1.2) — no CSS `capitalize`
needed/applied for these in most consuming components (see §3.1 table).

History: originally `'Change'` (`0002_note_category_resolve.sql`) → renamed to
`'Revision'` (`0004_note_category_rename.sql`, with a data `UPDATE` alongside
the constraint swap) → `issues` table launched in `0008` reusing only 4 values
(no Informational yet) → `0011_informational_category.sql` added
`'Informational'` to **both** `notes` and `issues` constraints in the same
migration. Final, current constraints (identical value lists, two separate
`CHECK`s on two separate tables — see gotcha §4.17 re: not sharing an enum type):

```sql
-- notes_category_check (0011)
check (category in ('Revision', 'Bug', 'Question', 'Live Changelog', 'Informational'));
-- issues_category_check (0011)
check (category in ('Revision', 'Bug', 'Question', 'Live Changelog', 'Informational'));
```

Colors:

```ts
export const CATEGORY_STYLES: Record<NoteCategory, string> = {
  Revision:
    "bg-blue-100 text-blue-800 border-blue-200 dark:bg-blue-950 dark:text-blue-200 dark:border-blue-900",
  Bug: "bg-red-100 text-red-800 border-red-200 dark:bg-red-950 dark:text-red-200 dark:border-red-900",
  Question:
    "bg-amber-100 text-amber-800 border-amber-200 dark:bg-amber-950 dark:text-amber-200 dark:border-amber-900",
  "Live Changelog":
    "bg-violet-100 text-violet-800 border-violet-200 dark:bg-violet-950 dark:text-violet-200 dark:border-violet-900",
  Informational:
    "bg-teal-100 text-teal-800 border-teal-200 dark:bg-teal-950 dark:text-teal-200 dark:border-teal-900",
};

export const CATEGORY_DOT: Record<NoteCategory, string> = {
  Revision: "bg-blue-500",
  Bug: "bg-red-500",
  Question: "bg-amber-500",
  "Live Changelog": "bg-violet-500",
  Informational: "bg-teal-500",
};
```

This is the **single vocab reused the most times across the whole app**: page
notes, feature notes, issues, and notification-bell rows (§3.6) all render the
identical pill using these two maps. Reproducing it once and reusing it
everywhere is the entire point of having it centralized.

### 1.4 Issue Status — `issues.status`

```ts
// src/lib/types.ts
export const ISSUE_STATUSES = [
  "Open", "Addressing", "Ready for Review", "Revision Required",
  "Address Later", "FYI Only", "Resolved",
] as const;
export type IssueStatus = (typeof ISSUE_STATUSES)[number];
export const DEFAULT_ISSUE_STATUS: IssueStatus = "Open";
```

Authoritative constraint (`0008_general_issues.sql`; **never altered again** —
confirmed by grepping every migration for `issues_status_check`):

```sql
status text not null default 'Open'
  check (status in ('Open', 'Addressing', 'Ready for Review',
    'Revision Required', 'Address Later', 'FYI Only', 'Resolved')),
```

Colors:

```ts
export const ISSUE_STATUS_STYLES: Record<IssueStatus, string> = {
  Open: "bg-muted text-muted-foreground border-border",
  Addressing:
    "bg-orange-100 text-orange-800 border-orange-200 dark:bg-orange-950 dark:text-orange-200 dark:border-orange-900",
  "Ready for Review":
    "bg-blue-100 text-blue-800 border-blue-200 dark:bg-blue-950 dark:text-blue-200 dark:border-blue-900",
  "Revision Required":
    "bg-red-100 text-red-800 border-red-200 dark:bg-red-950 dark:text-red-200 dark:border-red-900",
  "Address Later":
    "bg-slate-100 text-slate-700 border-slate-200 dark:bg-slate-800 dark:text-slate-300 dark:border-slate-700",
  "FYI Only":
    "bg-violet-100 text-violet-800 border-violet-200 dark:bg-violet-950 dark:text-violet-200 dark:border-violet-900",
  Resolved:
    "bg-green-100 text-green-800 border-green-200 dark:bg-green-950 dark:text-green-200 dark:border-green-900",
};

export const ISSUE_STATUS_DOT: Record<IssueStatus, string> = {
  Open: "bg-muted-foreground/50",
  Addressing: "bg-orange-500",
  "Ready for Review": "bg-blue-500",
  "Revision Required": "bg-red-500",
  "Address Later": "bg-slate-400",
  "FYI Only": "bg-violet-500",
  Resolved: "bg-green-500",
};
```

**Reproduce the `Address Later` dark-mode inconsistency deliberately, or fix it
consciously** — every other vocab's dark variant uses the `-950`
background/`-200` text/`-900` border formula; `Address Later` alone uses
`slate-800`/`slate-300`/`slate-700` (a full step lighter/more saturated than
the formula). This is either a deliberate exception (slate has no good
"muted-but-visible" `-950` step in this palette) or a one-off inconsistency —
either way, it's the one outlier in an otherwise perfectly consistent formula.

### 1.5 Feature Status / Signoff

Features (`0013_features.sql`) reuse **the exact same two vocabularies** as
pages — not a separate vocab, and notably not a shared enum/domain type
either: the migration re-declares byte-for-byte identical `CHECK` lists on a
second table:

```sql
status       text not null default 'open'
               constraint features_status_check
               check (status in ('open', 'creating', 'finalizing', 'revision pending', 'revising', 'drafted', 'completed')),
signoff      text not null default 'not ready'
               constraint features_signoff_check
               check (signoff in ('not ready', 'ready for review', 'revision not ready', 'revision needed', 'approved')),
```

Rendered with the identical `STATUS_STYLES`/`STATUS_DOT`/`SIGNOFF_STYLES`/`SIGNOFF_DOT`
maps from §1.1/§1.2 (same `PillSelect` component, different first column). In
a rebuild, this is exactly the case for one shared Postgres `ENUM` type (or a
shared Python `Enum` + single `CHECK` reused via a generated column
constraint) instead of copy-pasting the value list per table — see gotcha §4.17.

### 1.6 Notification `kind` — **not DB-constrained**

Unlike every vocab above, `notifications.kind` has **no `CHECK` constraint**
anywhere in the migrations (`0014_notifications.sql`: `kind text not null
default 'mention'` — no `check (...)`). The 3 real values are established only
by convention in application code:

```ts
// src/components/notifications-bell.tsx
const KIND_LABEL: Record<string, string> = {
  mention: "mentioned you",
  thread_reply: "replied in a thread",
  action_to: "assigned you",
};
function kindText(kind: string): string {
  return KIND_LABEL[kind] ?? "notified you"; // defensive fallback for unknown kind
}
```

In a rebuild with a real backend, this is exactly the kind of vocab that
should get a proper `CHECK`/Python `Enum` it never had — the fallback string
`"notified you"` is defensive code written because nothing enforces the 3
values at the data layer.

### 1.7 One-off hardcoded pill (not from the shared maps)

The notification bell's **"Resolved" badge** on each row is a bespoke inline
green pill, not sourced from `CATEGORY_STYLES` or any shared map — reproduce
this exact string if you want the identical look:

```
"inline-flex items-center rounded-full border border-green-200 bg-green-100 px-1.5 py-0 text-[10px] font-medium leading-4 text-green-800 dark:border-green-900 dark:bg-green-950 dark:text-green-200"
```

And the bell's unknown-category fallback (defensive, for legacy/bad data):

```ts
CATEGORY_STYLES[n.category as NoteCategory] ?? "bg-muted text-muted-foreground border-border"
CATEGORY_DOT[n.category as NoteCategory] ?? "bg-muted-foreground/50"
```

---

## 2. AUTOMATIONS

### 2.1 Page Status → Review coupling (client-side)

Source: `src/components/review-table.tsx`, function `setStatus`. This is
**plain React/client code that calls Supabase directly** — there is no DB
trigger for this half of the coupling.

```ts
async function setStatus(pageId: string, status: PageStatus) {
  // Automation: the status drives the Review state.
  const patch: { status: PageStatus; signoff?: Signoff } = { status };
  if (status === "open" || status === "creating" || status === "finalizing") {
    patch.signoff = "not ready";
  } else if (status === "drafted") {
    patch.signoff = "ready for review";
  } else if (status === "revising") {
    patch.signoff = "revision not ready";
  }

  const snapshot = pages;
  setPages((prev) => prev.map((p) => (p.id === pageId ? { ...p, ...patch } : p)));
  // The DB trigger writes the StatusLogs history row automatically.
  const { error } = await supabase.from("pages").update(patch).eq("id", pageId);
  if (error) {
    setPages(snapshot);
    toast.error("Couldn't update status. Please try again.");
    return;
  }
  router.refresh();
}
```

**The coupling is intentionally not exhaustive** — `status === "revision
pending"` and `status === "completed"` have **no rule here**; those two
statuses are only ever reached from the *other* direction (§2.2). Status
drives signoff for the "author is making forward progress" states; signoff
drives status for the "reviewer rendered a verdict" states. Nobody manually
sets a page to "completed" by picking a status — only an "approved" signoff
does that.

### 2.2 Review (Signoff) → Status coupling (client-side)

Source: `src/components/review-table.tsx`, function `setSignoff`.

```ts
async function setSignoff(pageId: string, signoff: Signoff) {
  // Automation: a review state can drive the page status.
  const patch: { signoff: Signoff; status?: PageStatus } = { signoff };
  if (signoff === "revision needed") patch.status = "revision pending";
  else if (signoff === "approved") patch.status = "completed";

  const snapshot = pages;
  setPages((prev) => prev.map((p) => (p.id === pageId ? { ...p, ...patch } : p)));
  const { error } = await supabase.from("pages").update(patch).eq("id", pageId);
  if (error) {
    setPages(snapshot);
    toast.error("Couldn't update review. Please try again.");
    return;
  }
  router.refresh();
}
```

**Full bidirectional map** (for the rebuild's service-layer function):

| User sets Status to… | Auto-sets Signoff to… | | User sets Signoff to… | Auto-sets Status to… |
|---|---|---|---|---|
| `open` | `not ready` | | `not ready` | *(no rule)* |
| `creating` | `not ready` | | `ready for review` | *(no rule)* |
| `finalizing` | `not ready` | | `revision not ready` | *(no rule)* |
| `drafted` | `ready for review` | | `revision needed` | `revision pending` |
| `revising` | `revision not ready` | | `approved` | `completed` |
| `revision pending` | *(no rule — reached via signoff)* | | | |
| `completed` | *(no rule — reached via signoff)* | | | |

**Feature status/signoff should get the identical coupling** in a rebuild —
the review app does **not** currently wire this automation for `features`
(the `features` table has the same two columns, but `features-table.tsx`
wasn't in the requested file set and — worth verifying directly if you build
a court/ball-config system — the coupling above is defined only inside
`review-table.tsx`'s `setStatus`/`setSignoff`, scoped to pages).

**History logging is asymmetric**: only `pages.status` changes get an audit
row, via a DB trigger (`0001_init.sql`), and it fires for **every** update
regardless of client, by design:

```sql
-- log_status_change(), 0001_init.sql
-- Guarantees a status_logs row is written whenever pages.status changes,
-- regardless of which client made the change (the spec's "backend captures it").
-- security definer so the insert bypasses RLS.
create or replace function log_status_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if new.status is distinct from old.status then
    insert into status_logs (page_id, previous_status, new_status)
    values (new.id, old.status, new.status);
  end if;
  return new;
end;
$$;

create trigger pages_status_change
  after update on pages
  for each row
  execute function log_status_change();
```

`pages.signoff` changes get **no equivalent history table** — an asymmetry
worth a deliberate decision in the rebuild (either give both columns history,
or consciously decide status history is the one that matters).

*Port note:* in FastAPI, `log_status_change` becomes a service-layer call in
the same transaction as the `UPDATE` (or a SQLAlchemy `before_update`/
`after_update` event listener) — the "regardless of which client" guarantee
still holds as long as **all** writes go through the service layer, which
plan-v1.md's architecture already guarantees (no client writes directly to
the DB).

### 2.3 Issue: Ready-for-Review auto-reassign, Revision-Required restore, Resolved clears

Source: `src/components/issues-table.tsx`, function `setStatus`. **100%
client-side** — no DB trigger involved, unlike the notification automations in §2.6.

```ts
function setStatus(issue: Issue, status: IssueStatus) {
  const patch: IssuePatch = { status };
  if (status === "Ready for Review") {
    // Remember current assignees, then hand back to the creator.
    patch.prev_action_to = issue.action_to;
    patch.action_to = issue.author_email ? [issue.author_email] : [];
  } else if (status === "Revision Required") {
    // Restore whoever it was assigned to before "Ready for Review".
    patch.action_to = issue.prev_action_to;
  } else if (status === "Resolved") {
    patch.action_to = [];
  }
  updateIssue(issue.id, patch);
}
```

Mechanics to preserve exactly:
- `prev_action_to` is a **single-slot snapshot**, not a stack. If you go
  `Ready for Review` → (someone manually reassigns) → `Ready for Review`
  again, the first snapshot is overwritten; only the most recent "who had it
  right before the last handback" is recoverable.
- Restoring on `Revision Required` sets `action_to = issue.prev_action_to`
  verbatim — it does **not** merge/union with the current `action_to`, it replaces.
- `Resolved` unconditionally empties `action_to` — "point over, ball dead,"
  nobody owns a resolved issue.
- `author_email` (not `author_id`) is what gets used for the reassign-to-author
  step — if an issue has no `author_email` (shouldn't happen given `set_author()`,
  §2.7, but defensively handled), `action_to` becomes `[]` instead of throwing.

### 2.4 Issue: Informational category → FYI Only status (one-directional)

Source: `src/components/issues-table.tsx`, function `setCategory`.

```ts
function setCategory(issue: Issue, category: NoteCategory) {
  // Automation: marking an issue "Informational" sets its status to FYI Only.
  const patch: IssuePatch = { category };
  if (category === "Informational") patch.status = "FYI Only";
  updateIssue(issue.id, patch);
}
```

**This is one-directional only** — manually setting status to `FYI Only`
does **not** set category to `Informational`, and changing category away from
`Informational` later does **not** revert status away from `FYI Only`. Both
are independent fields that happen to get nudged together at one specific
transition. Preserve this exact asymmetry (or make a deliberate call to
tighten it in the rebuild).

### 2.5 "Actionable" — a derived, unstored predicate

Source: `src/components/issues-table.tsx`. Not a column — computed on read,
used for the "Actionable" StatChip and its count:

```ts
// "Actionable" = something that needs doing: not Informational, not FYI Only
// (and not Resolved, which the resolved-hiding rule already excludes).
const isActionable = (i: Issue) =>
  i.category !== "Informational" && i.status !== "FYI Only";

const actionableCount = issues.filter(
  (i) => i.status !== "Resolved" && isActionable(i),
).length;
```

Note `isActionable` alone does **not** exclude `Resolved` — every call site
that wants the "true actionable count" ANDs in `i.status !== "Resolved"`
itself (see also the `visibleIssues` filter, which handles resolved-hiding
via a separate `showResolved` toggle rule, not via `isActionable`).

### 2.6 Notification triggers (server-side, DB triggers today)

Source: `supabase/migrations/0014_notifications.sql` (base),
`0016_notification_enhancements.sql` (+`category`, +`resolved`, thread/action-to
notifications, resolved-propagation), `0017_notification_context_label.sql`
(+`context_label`). **Quoting the final (0017) versions** — these supersede
0014/0016.

Final `notifications` table shape (columns accreted across all three migrations):

```
id, recipient_id, recipient_email, actor_email, kind, category, context_label,
body, link, source_table, source_id, read, resolved, created_at
```

RLS: recipients can `select`/`update` only their own rows
(`recipient_id = auth.uid()`); there is **no client `insert` policy at all** —
every notification is written by a `security definer` trigger function, which
is how "can't be bypassed, needs no client changes" is enforced (quoted
directly from the `0014` migration's top comment).

**Shared preview-stripping idiom**, reused identically wherever a `body`
preview is built (mention helper, both thread-participant triggers,
action-to trigger):

```sql
preview := regexp_replace(coalesce(p_content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g'); -- strip markdown image tags
preview := btrim(regexp_replace(preview, '\s+', ' ', 'g'));                          -- collapse whitespace
preview := left(preview, 160);                                                       -- truncate to 160 chars
```

**Shared context-label idiom**, reused identically in the note/comment mention
trigger, the thread-participants trigger, and the 0017 backfill `UPDATE`:

```sql
coalesce(pg.page_name, left(btrim(regexp_replace(ft.description, '\s+', ' ', 'g')), 60), 'Note')
```

i.e. page name if it's a page note, else the feature's description
whitespace-collapsed and truncated to 60 chars, else the literal `'Note'` as a
last-resort fallback. For issues/issue-comments the context label is always
the **literal string `'General Issue'`** — no computation needed since issues
aren't attached to a page/feature.

#### 2.6.1 Mention parsing + self-mentions

```sql
-- create_mention_notifications, final form (0017)
CREATE OR REPLACE FUNCTION create_mention_notifications(
  p_content text, p_actor_id uuid, p_actor_email text, p_link text,
  p_src_table text, p_src_id uuid,
  p_category text DEFAULT NULL, p_context text DEFAULT NULL
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  rec record;
  preview text;
BEGIN
  preview := regexp_replace(coalesce(p_content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  preview := btrim(regexp_replace(preview, '\s+', ' ', 'g'));
  preview := left(preview, 160);

  FOR rec IN
    SELECT DISTINCT p.id AS uid, p.email AS email
    FROM regexp_matches(
           coalesce(p_content, ''),
           '@([A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,})',
           'g'
         ) AS mm
    JOIN profiles p ON lower(p.email) = lower(mm[1])
    -- NOTE: no `WHERE p.id IS DISTINCT FROM p_actor_id` filter — self-mentions
    -- are intentionally allowed (see 0015 below).
  LOOP
    INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, context_label, body, link, source_table, source_id)
    VALUES (rec.uid, rec.email, p_actor_email, 'mention', p_category, p_context, preview, p_link, p_src_table, p_src_id);
  END LOOP;
END;
$$;
```

Mention regex: `@([A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,})` — requires
a **full email** after the `@` (this is a server-side regex over stored
content, unrelated to the client-side caret-token detection in §3.4, which is
deliberately much looser since it runs on every keystroke against partial input).
Matched case-insensitively against `profiles.email`.

**Self-mentions were originally blocked, then deliberately un-blocked**
(`0015_self_mentions.sql`) — quoted in full because the reasoning is exactly
the kind of product decision worth carrying forward:

```sql
-- Allow self-mentions. Previously create_mention_notifications() skipped the
-- author (`where p.id is distinct from p_actor_id`), so @mentioning yourself did
-- nothing. rian wants self-pings (e.g. as reminders), so we drop that filter.
```

#### 2.6.2 Per-source-table mention triggers (all 4, final 0017 forms)

Each computes its own `focus`/`slug`/`category`/`context_label` then defers to
the shared helper above. Quoting all four because the context-label logic
differs meaningfully between them (notes/comments look up page-or-feature;
issues/issue_comments hardcode `'General Issue'`):

```sql
-- Comments (on page or feature notes)
CREATE OR REPLACE FUNCTION notify_comment_mentions() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_focus text; v_slug text; v_category text; v_context text;
BEGIN
  SELECT CASE WHEN n.page_id IS NOT NULL THEN 'page:' || n.page_id ELSE 'feature:' || n.feature_id END,
         pr.slug, n.category,
         coalesce(pg.page_name, left(btrim(regexp_replace(ft.description, '\s+', ' ', 'g')), 60), 'Note')
    INTO v_focus, v_slug, v_category, v_context
  FROM notes n
  LEFT JOIN pages pg ON pg.id = n.page_id
  LEFT JOIN features ft ON ft.id = n.feature_id
  JOIN projects pr ON pr.id = coalesce(pg.project_id, ft.project_id)
  WHERE n.id = new.note_id;

  PERFORM create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=' || v_focus || '&note=' || new.note_id || '#comment-' || new.id,
    'comments', new.id, v_category, v_context
  );
  RETURN new;
END; $$;

-- Notes themselves
CREATE OR REPLACE FUNCTION notify_note_mentions() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_focus text; v_slug text; v_context text;
BEGIN
  v_focus := CASE WHEN new.page_id IS NOT NULL THEN 'page:' || new.page_id ELSE 'feature:' || new.feature_id END;
  SELECT pr.slug,
         coalesce(pg.page_name, left(btrim(regexp_replace(ft.description, '\s+', ' ', 'g')), 60), 'Note')
    INTO v_slug, v_context
  FROM projects pr
  LEFT JOIN pages pg ON pg.id = new.page_id
  LEFT JOIN features ft ON ft.id = new.feature_id
  WHERE pr.id = coalesce(pg.project_id, ft.project_id);

  PERFORM create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=' || v_focus || '&note=' || new.id || '#note-' || new.id,
    'notes', new.id, new.category, v_context
  );
  RETURN new;
END; $$;

-- Issue comments
CREATE OR REPLACE FUNCTION notify_issue_comment_mentions() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_slug text; v_category text;
BEGIN
  SELECT pr.slug, i.category INTO v_slug, v_category
  FROM issues i JOIN projects pr ON pr.id = i.project_id
  WHERE i.id = new.issue_id;

  PERFORM create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=issue:' || new.issue_id || '#issuecomment-' || new.id,
    'issue_comments', new.id, v_category, 'General Issue'
  );
  RETURN new;
END; $$;

-- Issues themselves
CREATE OR REPLACE FUNCTION notify_issue_mentions() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_slug text;
BEGIN
  SELECT pr.slug INTO v_slug FROM projects pr WHERE pr.id = new.project_id;
  PERFORM create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=issue:' || new.id,
    'issues', new.id, new.category, 'General Issue'
  );
  RETURN new;
END; $$;
```

Deep-link format to note: `/{project_slug}?focus=page:{id}&note={note_id}#comment-{comment_id}`
(or `feature:{id}` / `issue:{id}` for the other parent types; the `#comment-`
/`#note-`/`#issuecomment-` hash selects which DOM node gets CSS `:target`
highlighting — see gotcha §4.10).

#### 2.6.3 Thread-participation notifications + mention dedup

"Participation = subscription": the note/issue author plus every distinct
past commenter gets a `thread_reply` notification on a new comment — **except**
anyone who already got a `mention` notification for this exact comment (the
dedup rule).

```sql
CREATE OR REPLACE FUNCTION notify_thread_participants() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_focus text; v_slug text; v_category text; v_context text;
  v_link text; v_preview text;
  mentioned_emails text[];
  rec record;
BEGIN
  SELECT CASE WHEN n.page_id IS NOT NULL THEN 'page:' || n.page_id ELSE 'feature:' || n.feature_id END,
         pr.slug, n.category,
         coalesce(pg.page_name, left(btrim(regexp_replace(ft.description, '\s+', ' ', 'g')), 60), 'Note')
    INTO v_focus, v_slug, v_category, v_context
  FROM notes n
  LEFT JOIN pages pg ON pg.id = n.page_id
  LEFT JOIN features ft ON ft.id = n.feature_id
  JOIN projects pr ON pr.id = coalesce(pg.project_id, ft.project_id)
  WHERE n.id = new.note_id;

  v_link := '/' || v_slug || '?focus=' || v_focus || '&note=' || new.note_id || '#comment-' || new.id;
  v_preview := regexp_replace(coalesce(new.content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  v_preview := btrim(regexp_replace(v_preview, '\s+', ' ', 'g'));
  v_preview := left(v_preview, 160);

  SELECT array_agg(DISTINCT lower(mm[1])) INTO mentioned_emails
  FROM regexp_matches(coalesce(new.content, ''), '@([A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,})', 'g') AS mm
  JOIN profiles p ON lower(p.email) = lower(mm[1]);

  FOR rec IN
    SELECT DISTINCT sub.uid, sub.email FROM (
      SELECT n.author_id AS uid, n.author_email AS email FROM notes n WHERE n.id = new.note_id
      UNION
      SELECT c.author_id AS uid, c.author_email AS email FROM comments c WHERE c.note_id = new.note_id AND c.id != new.id
    ) sub
    WHERE sub.uid IS DISTINCT FROM new.author_id
      AND (mentioned_emails IS NULL OR NOT lower(sub.email) = ANY(mentioned_emails))
  LOOP
    INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, context_label, body, link, source_table, source_id)
    VALUES (rec.uid, rec.email, new.author_email, 'thread_reply', v_category, v_context, v_preview, v_link, 'comments', new.id);
  END LOOP;
  RETURN new;
END; $$;

CREATE TRIGGER comments_notify_thread AFTER INSERT ON comments
  FOR EACH ROW EXECUTE FUNCTION notify_thread_participants();
```

`notify_issue_thread_participants()` is the exact same shape, scoped to
`issues`/`issue_comments`, with `context_label` hardcoded to `'General Issue'`.
Key dedup line to preserve: `WHERE sub.uid IS DISTINCT FROM new.author_id AND
(mentioned_emails IS NULL OR NOT lower(sub.email) = ANY(mentioned_emails))` —
skip the commenter themselves, and skip anyone who already got a `mention` row
out of the same INSERT.

#### 2.6.4 Action-to (assignment) notifications — array-diff dedup

```sql
CREATE OR REPLACE FUNCTION notify_action_to() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_slug text; v_link text; v_preview text; v_actor_email text;
  new_assignees text[]; assignee text; rec record;
BEGIN
  IF TG_OP = 'INSERT' THEN
    new_assignees := new.action_to;
  ELSE
    -- Only notify newly added assignees
    SELECT array_agg(e) INTO new_assignees
    FROM unnest(new.action_to) AS e
    WHERE e != ALL(coalesce(old.action_to, ARRAY[]::text[]));
  END IF;

  IF new_assignees IS NULL OR array_length(new_assignees, 1) IS NULL THEN
    RETURN new;
  END IF;

  SELECT pr.slug INTO v_slug FROM projects pr WHERE pr.id = new.project_id;
  v_link := '/' || v_slug || '?focus=issue:' || new.id;
  v_preview := regexp_replace(coalesce(new.content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  v_preview := btrim(regexp_replace(v_preview, '\s+', ' ', 'g'));
  v_preview := left(v_preview, 160);

  -- Determine actor: on INSERT it's the author; on UPDATE look up the current user
  IF TG_OP = 'INSERT' THEN
    v_actor_email := new.author_email;
  ELSE
    SELECT p.email INTO v_actor_email FROM profiles p WHERE p.id = auth.uid();
    IF v_actor_email IS NULL THEN v_actor_email := new.author_email; END IF;
  END IF;

  FOREACH assignee IN ARRAY new_assignees
  LOOP
    FOR rec IN SELECT p.id AS uid, p.email FROM profiles p WHERE lower(p.email) = lower(assignee)
    LOOP
      INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, context_label, body, link, source_table, source_id)
      VALUES (rec.uid, rec.email, v_actor_email, 'action_to', new.category, 'General Issue', v_preview, v_link, 'issues', new.id);
    END LOOP;
  END LOOP;
  RETURN new;
END; $$;

CREATE TRIGGER issues_notify_action_to AFTER INSERT OR UPDATE OF action_to ON issues
  FOR EACH ROW EXECUTE FUNCTION notify_action_to();
```

Two mechanics to preserve exactly:
1. **Column-scoped trigger** (`UPDATE OF action_to`) — it does not fire at all
   on an update that leaves `action_to` untouched (e.g. just changing status
   or content), so editing an issue's text never spams a re-notification.
2. **Array-diff on UPDATE** (`e != ALL(coalesce(old.action_to, ...))`) — only
   the assignees newly present in the array get notified; re-saving the same
   list (or a list that still contains someone already in it) doesn't re-ping them.

#### 2.6.5 Resolved-state propagation

Keeps `notifications.resolved` mirrored to the *source item's* resolved
state — a notification's lifecycle is tied to the underlying work item, not
to whether it's been read.

```sql
CREATE OR REPLACE FUNCTION propagate_note_resolved() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF old.resolved IS DISTINCT FROM new.resolved THEN
    UPDATE notifications SET resolved = new.resolved
    WHERE (source_table = 'notes' AND source_id = new.id)
       OR (source_table = 'comments' AND source_id IN (SELECT id FROM comments WHERE note_id = new.id));
  END IF;
  RETURN new;
END; $$;
CREATE TRIGGER notes_propagate_resolved AFTER UPDATE OF resolved ON notes
  FOR EACH ROW EXECUTE FUNCTION propagate_note_resolved();

CREATE OR REPLACE FUNCTION propagate_issue_resolved() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_resolved boolean;
BEGIN
  IF old.status IS DISTINCT FROM new.status THEN
    v_resolved := (new.status = 'Resolved');
    UPDATE notifications SET resolved = v_resolved
    WHERE (source_table = 'issues' AND source_id = new.id)
       OR (source_table = 'issue_comments' AND source_id IN (SELECT id FROM issue_comments WHERE issue_id = new.id));
  END IF;
  RETURN new;
END; $$;
CREATE TRIGGER issues_propagate_resolved AFTER UPDATE OF status ON issues
  FOR EACH ROW EXECUTE FUNCTION propagate_issue_resolved();
```

Note the cascade: resolving a note/issue flips `resolved` on **both** its own
notifications AND every notification generated by its child comments — a
single toggle updates a whole tree of notification rows.

*Port note (applies to all of §2.6):* plan-v1.md §6.4 already calls this —
in FastAPI, none of this lives in Postgres triggers (no client writes
directly to the DB in the new architecture, so the "can't be bypassed"
guarantee doesn't require a trigger to hold). Each of these becomes a plain
service-layer function called in the same transaction/request as the
triggering write (e.g. `create_comment()` calls `notify_mentions()` +
`notify_thread_participants()` before committing), which also makes it
pytest-testable and lets it publish an SSE event in the same breath. The
*design* — three kinds, dedup rule, context label, resolved propagation, deep
link format — ports wholesale; only the *mechanism* (trigger → service call) moves.

### 2.7 Authorship stamping

Source: `0007_authorship.sql`. Applied via `BEFORE INSERT` triggers to
`notes`/`comments` (0007), `issues`/`issue_comments` (0008/0012), and
`features` (0013) — every author-attributed table uses the identical function:

```sql
-- Records who posted each note / comment, set automatically from the JWT on
-- insert (can't be spoofed by the client).
create or replace function set_author()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  new.author_id := auth.uid();
  new.author_email := (select email from auth.users where id = auth.uid());
  return new;
end;
$$;
```

*Port note:* with the app's own session (not Supabase JWTs), this becomes
"stamp `author_id` from the authenticated session in the service layer before
insert" — same guarantee (client can't set someone else as the author),
different mechanism.

---

## 3. UX MECHANICS

### 3.0 The optimistic-update-with-rollback convention (underlies everything below)

Source: every mutation function in `review-table.tsx` and `issues-table.tsx`
follows this exact shape — it's the scaffolding that makes every pill/chip
edit in §3.1 feel instant:

```ts
async function setStatus(pageId: string, status: PageStatus) {
  const patch = /* ... */;
  const snapshot = pages;                 // 1. snapshot current state
  setPages((prev) => /* apply patch */);  // 2. optimistic local update, instantly
  const { error } = await supabase.from("pages").update(patch).eq("id", pageId); // 3. write
  if (error) {
    setPages(snapshot);                   // 4. roll back to the snapshot on failure
    toast.error("Couldn't update status. Please try again.");
    return;
  }
  router.refresh();                       // 5. on success, pull fresh server data
}
```

Every single mutation (`setStatus`, `setSignoff`, `addNote`, `updateNote`,
`deleteNote`, `addComment`, `updateComment`, `updatePageTags`, `saveDomains`,
`updateIssue`, `deleteIssue`, …) repeats this snapshot → optimistic-set →
write → rollback-or-refresh shape. There is no shared helper/hook wrapping it
— it's hand-repeated at every call site. **In the rebuild this is exactly the
kind of thing to factor into one TanStack Query mutation wrapper** (optimistic
update + `onError` rollback + `onSettled` invalidate is literally what
`useMutation` is for) rather than re-hand-rolling it N times.

### 3.1 Inline-edit pill pattern

Source: `PillSelect` (`review-table.tsx`), `StatusPill`/`CategorySelect`
(`issues-table.tsx`, `notes-sheet.tsx`). Three near-identical implementations
of the same shape — worth unifying into one generic component in the rebuild
(`PillSelect<T>` already is the generic one; the other two are copies with the
generic type inlined).

Shape: a `DropdownMenuTrigger` whose `render` prop is a `<button>` styled as a
rounded-full pill, plus a `DropdownMenuContent` listing every option.

```tsx
// review-table.tsx — the generic version
<DropdownMenuTrigger
  render={
    <button
      type="button"
      aria-label={`${label}: ${value}`}
      className={cn(
        "inline-flex items-center gap-1.5 rounded-full border px-2.5 py-1 text-xs font-medium capitalize transition-colors focus:outline-none focus-visible:ring-2 focus-visible:ring-ring",
        pillClass[value],
      )}
    >
      <span className={cn("size-1.5 rounded-full", dotClass[value])} />
      {value}
      <ChevronDown className="size-3 opacity-60" />
    </button>
  }
/>
<DropdownMenuContent align="start" className="min-w-44">
  {options.map((option) => (
    <DropdownMenuItem key={option} onClick={() => onSelect(option)} className="gap-2 capitalize">
      <span className={cn("size-2 rounded-full", dotClass[option])} />
      <span className="flex-1">{option}</span>
      {option === value && <Check className="size-3.5 opacity-70" />}
    </DropdownMenuItem>
  ))}
</DropdownMenuContent>
```

**Critical implementation detail: this uses Base UI's `render` prop, not
Radix's `asChild`.** `<DropdownMenuTrigger render={<button>...} />` — the
button is passed as the `render` prop value, not as a child element. Getting
this wrong (writing `asChild` + a child `<button>`) is the single most likely
copy-paste mistake porting this to a fresh shadcn/Base UI setup. (plan-v1.md
already flags this as "known-good" — carry it forward exactly.)

**Casing table — which vocabs rely on CSS `capitalize` vs. are pre-cased:**

| Vocab | Stored casing | Component applies `capitalize`? |
|---|---|---|
| `PageStatus` (`open`, `revision pending`, …) | all-lowercase | **Yes** — trigger button and dropdown items both have `capitalize` |
| `Signoff` (`not ready`, `ready for review`, …) | all-lowercase | **Yes** — same `PillSelect` component |
| `IssueStatus` (`Open`, `Ready for Review`, …) | Title Case already | **No** — `StatusPill` in issues-table.tsx has no `capitalize` class |
| `NoteCategory` (`Revision`, `Live Changelog`, …) | Title Case already | **No** — `CategorySelect` in notes-sheet.tsx has no `capitalize` class |

CSS `text-transform: capitalize` only uppercases the first letter of each
whitespace-separated word and leaves the rest untouched — this is why it's
safe to apply unconditionally in `EnumFilter`'s dropdown items (§3.3) even to
already-correctly-cased strings like `"FYI Only"` (no-op) or raw email
addresses like `adi@example.com` (capitalizes only the leading `a` → cosmetic
quirk, not a crash — worth a conscious choice on whether to keep it in the rebuild).

Dropdown item dot is `size-2`; the trigger's own dot is smaller, `size-1.5`.
`DropdownMenuContent` width varies by call site: `min-w-44` (status/signoff/category),
`min-w-48` (issue status), `min-w-52` (assignee multi-select).
No explicit "close the menu" call anywhere — Base UI's `DropdownMenuItem`
auto-dismisses on select.

### 3.2 StatChip / StatCard behavior

Source: `src/components/stat-chip.tsx` (`StatChip`, used by `issues-table.tsx`)
and `review-table.tsx`'s inline `StatCard` — **two independent implementations
of the same component**, worth consolidating to one in the rebuild:

```tsx
// stat-chip.tsx — StatChip (standalone)
export function StatChip({ label, value, dot, active, onClick }: {
  label: string; value: number; dot?: string | null; active: boolean; onClick: () => void;
}) {
  return (
    <button type="button" onClick={onClick} className={cn(
      "flex items-center gap-1.5 whitespace-nowrap rounded-md border bg-background px-2.5 py-1.5 transition-colors hover:bg-accent",
      active && "border-foreground/40 ring-1 ring-foreground/20",
    )}>
      <span className="text-base font-semibold tabular-nums">{value}</span>
      {dot && <span className={cn("size-1.5 rounded-full", dot)} />}
      <span className="text-xs capitalize text-muted-foreground">{label}</span>
    </button>
  );
}
```

`review-table.tsx`'s `StatCard` is identical except: `dot` is `string | null`
(required, not optional) and the label span has **no** `capitalize` class.
Functionally interchangeable; the duplication itself is the gotcha (§4 lists it).

Behavior contract, common to both:
- `value` is a **live count computed in-memory** (array `.filter()`/`.reduce()`
  over the currently-loaded list), never a separate query.
- `active` means "clicking this chip would be a no-op because its filter is
  already the current state" — each chip's `onClick` calls a `focus*`
  function that **first resets every other filter, then sets just this one**
  (mutually exclusive quick-filters, not additive checkboxes). The one
  exception is the "Total" chip, whose `onClick` is `resetFilters` itself,
  and whose `active` state (`allCleared`) is true only when literally every
  filter (status, category/type, assignee, tags, search text) is at its default.
- `tabular-nums` on the count so digit width doesn't jitter as counts change.
- Active visual: `border-foreground/40 ring-1 ring-foreground/20`.

### 3.3 Column filters

Source: `src/components/column-filters.tsx`.

**`ExpandableSearch`** — collapses to an icon, expands to an input:
```
expanded = open || active   // active = value.trim().length > 0
```
Collapsed: `size-7` icon-only button. Expanded: `h-7 w-44` text input,
auto-focuses via a `useEffect` keyed on `expanded`. Blur only re-collapses
**if the value is empty** (`if (!active) setOpen(false)`) — a non-empty
search stays visually expanded even after losing focus. The inline clear (X)
button both clears the value AND collapses in one action.

**`TagFilter`** — multi-select, **OR semantics** (matches ANY selected tag,
not all): a `DropdownMenuCheckboxItem` per tag; trigger shows a `Tag` icon +
`"Tags"` or `"{n} tag(s)"`; a `DropdownMenuSeparator` + "Clear tags" item
appears only when at least one tag is selected.

**`EnumFilter`** — the generic dropdown filter, shared by every enum column:
```ts
export const ALL = "all" as const;
```
Always renders, in this exact order: (1) an "All" item (label configurable via
`allLabel`, defaults to `"All"`), (2) any `extraOptions` (synthetic grouped
values — e.g. status column's `{value:"incomplete", label:"Incomplete"}` and
`{value:"inprogress", label:"In progress"}`, or the assignee column's
`{value:"__me__", label:"Me"}`), (3) the real enum `options`, each with its dot.
Trigger label: `value === ALL ? allLabel : (extra ? extra.label : value)`, with
`capitalize` applied to the trigger span (harmless per §3.1's CSS note).
Active styling: `active ? "border-foreground/30 bg-accent font-medium" :
"text-muted-foreground"` where `active = value !== ALL`.

The "Incomplete"/"In progress" extra options are how the Page Status column
encodes compound filters without a real DB value:
```ts
const INCOMPLETE = "incomplete";   // everything except status === "completed" — this is the DEFAULT filter on load
const IN_PROGRESS = "inprogress";  // status in [creating, finalizing, revision pending, revising]
```
and the Issues table's status column has its own single extra option,
`"actionable"` (§2.5), plus a separate always-visible `Switch` for "Show resolved"
sitting right under the `EnumFilter` in the same header cell (not itself an
`EnumFilter` option).

### 3.4 @mention autocomplete

Source: `Composer` in `src/components/notes-sheet.tsx`.

**Caret-token detection** — deliberately loose (matches whatever is typed,
not a full email regex), because it must work against partial/in-progress input:

```ts
/** Detects an in-progress "@mention" token immediately before the caret.
 * Emails contain a second "@", so we grab the whole trailing non-space run and
 * only treat it as a mention if it starts with "@". */
function detectMention(value: string, caret: number) {
  const token = value.slice(0, caret).match(/(\S*)$/)?.[1] ?? "";
  if (!token.startsWith("@")) return null;
  return { start: caret - token.length, end: caret, query: token.slice(1) };
}
```

Matching: `mentionUsers.filter(u => u.toLowerCase().includes(query.toLowerCase())).slice(0, 6)`
— substring match anywhere in the email (not prefix-only), max 6 results, case-insensitive.
The whole feature is inert when `mentionUsers.length === 0` (checked in `syncMention`)
— no empty dropdown ever renders.

**Positioned upward, not downward** — because the composer sits at the bottom
of a Sheet, where a downward popup would clip:
```
className="absolute left-0 bottom-full z-50 mb-1 max-h-48 w-full overflow-auto rounded-md border bg-popover p-1 ..."
```

**Keyboard nav** (only active while `showMentions`, i.e. `mention !== null &&
matches.length > 0`): `ArrowDown`/`ArrowUp` cycle `activeIdx` with wraparound
(`(i + 1) % matches.length`, `(i - 1 + matches.length) % matches.length`);
`Enter` **or** `Tab` both commit the active match (both call
`e.preventDefault()`); `Escape` closes the popup without touching the typed text.

**Mouse selection — the single most important interaction-ordering detail in
the whole app:**
```tsx
<button
  type="button"
  // mousedown (not click) so the textarea doesn't blur first
  onMouseDown={(e) => {
    e.preventDefault();
    pickMention(email);
  }}
  ...
>
```
A `click` handler would fire *after* the textarea's `blur` event, and blur
dismisses the mention popup (see below) before the click ever registers —
so the picker item must be `onMouseDown` with `preventDefault()` to suppress
the blur-then-click race entirely.

**Blur grace period** (defense-in-depth alongside the mousedown fix, covers
other blur triggers): `onBlur={() => setTimeout(() => setMention(null), 120)}`
— exactly 120ms.

**Committing a selection** (`pickMention`): splices `@{email} ` (trailing
space included) into the content at `[mention.start, mention.end)`, then moves
the caret to just past the inserted text:
```ts
requestAnimationFrame(() => {
  const ta = textareaRef.current;
  if (ta) { ta.focus(); ta.setSelectionRange(caret, caret); }
});
```
The `requestAnimationFrame` wrapper is necessary — at the point `pickMention`
runs, React hasn't yet re-rendered the textarea with the new `value`, so
`setSelectionRange` would operate on stale content if called synchronously.

### 3.5 Image-paste flow

Source: `src/hooks/use-image-paste.ts` + the 4 call sites that upload
(`addNote`/`addComment` in `review-table.tsx`, `addComment`/`createIssue` in
`issues-table.tsx`) + `signImagePaths` (`src/lib/data/projects.ts`).

**The hook** (full — it's short and every line matters):
```ts
export function useImagePaste() {
  const [image, setImage] = useState<PastedImage | null>(null);
  const [error, setError] = useState<string | null>(null);

  const handlePaste = useCallback((e: React.ClipboardEvent) => {
    const items = e.clipboardData?.items;
    if (!items) return;
    for (const item of items) {
      if (item.kind === "file" && item.type.startsWith("image/")) {
        const file = item.getAsFile();
        if (!file) continue;
        // Stop the image bytes from also landing as garbage text in the textarea.
        e.preventDefault();
        const reader = new FileReader();
        reader.onload = () => { setImage({ file, dataUrl: reader.result as string }); setError(null); };
        reader.onerror = () => setError("Could not read the pasted image.");
        reader.readAsDataURL(file);
        return;
      }
    }
  }, []);

  const clear = useCallback(() => { setImage(null); setError(null); }, []);
  return { image, error, handlePaste, clear };
}
```

**Full pipeline, step by step:**
1. Attach `onPaste={handlePaste}` to the composer's `<Textarea>`.
2. Hook scans `clipboardData.items` for the first `kind === "file" &&
   type.startsWith("image/")` entry.
3. **`e.preventDefault()` is called before reading the file** — without it,
   some browsers additionally paste a filename or broken text representation
   into the textarea alongside/instead of just capturing the image.
4. `FileReader.readAsDataURL` → the data URL becomes an **instant local
   preview** (`<img>` capped `max-h-40`, with a remove/X button) — no network
   round-trip yet.
5. On submit, the composer passes **both** `imageDataUrl` (for optimistic UI)
   and `imageFile` (the raw `File`, for the real upload) up to the caller.
6. Caller uploads the raw file to a **private** Supabase Storage bucket
   *first*: path = `` `${pageId}/${crypto.randomUUID()}.${ext}` `` for page
   notes (nested per-page), or flat `` `issues/${crypto.randomUUID()}.${ext}` ``
   for issue comments/issues — `ext` derived from the original filename,
   lowercased, falling back to `"png"`; `contentType: input.imageFile.type ||
   "image/png"`.
7. Then inserts the DB row with `image_path` set to that storage key —
   **never** a public URL (bucket `note-images` is private; RLS only allows
   `authenticated` insert/select, `0001_init.sql`).
8. Optimistic local append renders the **data URL** as `image_url` — instant,
   but not yet the real signed URL.
9. `router.refresh()` immediately follows, which re-runs the server loader →
   calls `signImagePaths`/`createSignedUrls` with a **1-hour TTL**
   (`SIGNED_URL_TTL = 60 * 60`) → the data-URL placeholder is silently
   replaced by a real signed HTTPS URL once the refreshed props land.
10. Rendering is a plain `<img src={image_url}>` (`NoteImage` component) — a
    real `<img>`, not Next's `<Image>`, with an explicit eslint-disable
    comment (`@next/next/no-img-element`) marking that choice as deliberate
    (arbitrary private signed URLs with a TTL aren't worth Next's optimization
    pipeline).

**Batch signing** — a real perf pattern worth preserving: every note+comment
image path for an entire page/feature/issue-list load is collected into one
array and signed in a **single** `createSignedUrls` call per page load, not
per-image:
```ts
/** Resolve private storage keys to short-lived signed URLs in one batch. */
export async function signImagePaths(supabase: SupabaseClient, paths: string[]): Promise<Map<string, string>> {
  const signed = new Map<string, string>();
  if (paths.length === 0) return signed;
  const { data } = await supabase.storage.from(NOTE_BUCKET).createSignedUrls(paths, SIGNED_URL_TTL);
  for (const item of data ?? []) if (item.path && item.signedUrl) signed.set(item.path, item.signedUrl);
  return signed;
}
```

*Port note:* plan-v1.md §6.5 already specifies the FastAPI equivalent —
`POST /api/attachments` (size/mime-capped) writes a UUID-named file to a
project-local volume (never web-root-served), served back via an **authed**
`GET /api/attachments/{id}` behind the default-deny auth middleware. Same UX
(paste → instant preview → real upload → reconcile), simpler trust model (the
session cookie *is* the authorization — no signed-URL TTL bookkeeping needed).
The batch-signing perf pattern has no direct equivalent to port since an
authed-route-per-id has no per-request signing cost to batch — just don't
introduce an N+1 *query* for attachment metadata instead.

### 3.6 Notification bell mechanics

Source: `src/components/notifications-bell.tsx`.

**Two independent counts** that move separately: `unread` (`!n.read`, per
recipient/per-row) vs. `unresolvedCount` (`!n.resolved`, mirrors the *source
item's* resolved state via the propagation triggers in §2.6.5).

**Icon + badge logic** (quote exactly):
```ts
const BellIcon = unresolvedCount > 0 ? BellRing : Bell;
const showBadge = unread > 0 || unresolvedCount > 0;
const badgeCount = unread > 0 ? unread : unresolvedCount;
const badgeIsGrey = unread === 0 && unresolvedCount > 0;
// render: badgeCount > 9 ? "9+" : badgeCount
```
Reading: the **ringing** icon variant means "unresolved work exists," not
"unread messages exist." The badge prefers the unread count; if there are
zero unread but some unresolved remain, it shows the unresolved count in
**grey** instead of the primary color — "nothing new, but stuff's still open"
vs. "you have new stuff."

**One-time bounce on load:**
```ts
useEffect(() => {
  if (unresolvedCount > 0 && !hasAnimated) setHasAnimated(true);
}, [unresolvedCount, hasAnimated]);
// className: hasAnimated && unresolvedCount > 0 && "animate-[bell-bounce_0.5s_ease-in-out]"
```
`hasAnimated` flips true the first time unresolved work is observed and never
resets — the CSS keyframe itself has no `infinite`, so it plays once when the
class first attaches and then just holds its end state; it only replays on a
full component remount (hard reload), not on every count fluctuation within a session.

**Data loading**: one initial `select` ordered `created_at desc` with **no
limit** ("no item cap; whole history scrolls," `max-h-[70vh] overflow-y-auto`
on the list), then Realtime subscriptions for `INSERT` (prepend to local
array) and `UPDATE` (replace-by-id in local array), both filtered
`recipient_id=eq.${currentUserId}` — RLS-backed so cross-user leakage is
structurally impossible, not just hidden in the UI.

**Mark-read**: optimistic local set THEN `.update({read:true}).in("id", ids)` —
same snapshot-then-write shape as §3.0, minus the rollback (a failed
mark-read is treated as low-stakes enough not to need one).

**"Show resolved" toggle**: `visible = showResolved ? items : items.filter(n
=> !n.resolved)` — resolved hidden by default, exactly mirroring the
show-resolved pattern on notes/issues.

**Row rendering order**: unread dot (`bg-primary` or transparent) → actor
email (bold if unread) + `kindText(kind)` → context label, bold, prefixed
`"on "` → category pill (+ green "Resolved" pill if resolved) → 160-char
body preview → timestamp. `context_label` render:
```tsx
{n.context_label && (
  <span className="mt-0.5 block text-xs text-muted-foreground">
    on <span className="font-semibold text-foreground">{n.context_label}</span>
  </span>
)}
```

**Deep-link cache-busting** (`bustLink`) — necessary because re-navigating to
an identical URL is normally a router no-op, so re-clicking the same
notification twice would do nothing without this:
```ts
// Insert it into the QUERY string (before any #hash) so it stays parseable.
function bustLink(link: string): string {
  const hashIdx = link.indexOf("#");
  const base = hashIdx === -1 ? link : link.slice(0, hashIdx);
  const hash = hashIdx === -1 ? "" : link.slice(hashIdx);
  const sep = base.includes("?") ? "&" : "?";
  return base + sep + "_t=" + Date.now() + hash;
}
```
The consuming page pairs this with a handled-guard keyed on `focus + ":" +
_t` (see §4 for the exact quote) so the identical notification can be
re-clicked any number of times and re-open its sheet every time.
`openNotif` marks read (if unread) and navigates in that order, before
closing the dropdown.

---

## 4. GOTCHAS

Hard-won, non-obvious details — each with its source and why it matters.

1. **Hydration-safe timestamp formatting.** `src/lib/format.ts`:
   > *"Deterministic UTC-based formatter so server and client render
   > identically (avoids React hydration mismatches that locale/timezone
   > formatting can cause)."*
   `formatTimestamp` hand-builds `"Jul 18, 2:30 PM"` from `getUTCMonth`/`getUTCDate`/
   `getUTCHours`/`getUTCMinutes` — never `toLocaleString()` or anything
   timezone-sensitive, because a server-rendered vs. client-rendered locale
   string can differ and break React hydration.

2. **The `datetime-local` editor is forced through the same UTC convention** —
   `toDatetimeLocalUTC`/`fromDatetimeLocalUTC` treat the `<input
   type="datetime-local">` value as UTC wall-clock specifically "so it
   round-trips consistently with `formatTimestamp`." If the date editor used
   the browser's real local timezone instead, an edited timestamp would
   silently disagree with how it's displayed everywhere else.

3. **`sessionTouchedRef` — filters are snapshots, not live traps.** Source:
   `issues-table.tsx`.
   > *"Issues created or edited this session. Under the 'Me' assignee filter
   > these stay visible even after being re-assigned away, until a full page
   > reload (persists across router.refresh; cleared only when the component
   > remounts)."*
   ```ts
   const sessionTouchedRef = useRef<Set<string>>(new Set());
   // ... populated in updateIssue() and createIssue()
   if (assigneeFilter === ME) {
     const mine = currentUserEmail !== null && i.action_to.includes(currentUserEmail);
     if (!mine && !sessionTouchedRef.current.has(i.id)) return false;
   }
   ```
   Deliberately a plain `useRef` (not state) — no re-render needed when it
   changes, it's only consulted during the next render's filter pass, and
   deliberately **not persisted** (survives `router.refresh()` but not a hard
   reload/remount). The point: realtime/refresh should never yank a row out
   from under the person who's actively working on it.

4. **Real `<a>` tags for Staging/Live links, not `onClick` + `window.open`.**
   `review-table.tsx`'s `UrlRow`, `notes-sheet.tsx`'s `ParentLinks`. The
   `title` attribute says exactly why: `` `Open ${label.toLowerCase()} —
   middle-click or Ctrl/Cmd-click to open in a background tab` ``. A real
   anchor supports native browser gestures (middle-click, Cmd/Ctrl-click)
   that a script-driven `window.open` cannot faithfully replicate — browsers
   generally ignore scripted "modifier click" simulation.

5. **Markdown link clicks call `e.stopPropagation()`.** `src/components/markdown.tsx`:
   ```tsx
   a: (props) => (
     <a className="underline underline-offset-2" target="_blank" rel="noreferrer"
        // Don't let a link click bubble to a clickable row/cell.
        onClick={(e) => e.stopPropagation()}
        {...props} />
   ),
   ```
   Needed because note/issue cards are themselves `role="button"` clickable
   divs that open the detail sheet — without this, clicking a link inside
   rendered markdown would *also* fire the card's own click handler.

6. **`useImagePaste`'s `preventDefault()` ordering** — called *before* reading
   the file, not after (§3.5 step 3); getting the order wrong lets some
   browsers dump extra garbage text into the textarea alongside the image.

7. **Mention picker uses `onMouseDown` + `preventDefault()`, never `onClick`**
   (§3.4) — the single most important interaction-ordering detail in the
   app. A `click`-based picker item would never fire, because the textarea's
   `blur` (which dismisses the popup) always happens first.

8. **Storage is private + signed, never public, and re-signed every load.**
   `src/lib/constants.ts`: `NOTE_BUCKET = "note-images"` — *"private; access
   via signed URLs."* Combined with the 1-hour `SIGNED_URL_TTL` and re-signing
   on every server load: a signed URL that leaks stops working within an
   hour, and images never get one permanent stable URL — a fresh one is
   minted on every render.

9. **Notes/features "exactly one parent" enforced by a DB `CHECK`, not app
   logic.** `0013_features.sql`:
   ```sql
   alter table notes add constraint notes_parent_check
     check ((page_id is not null) <> (feature_id is not null));
   ```
   The boolean-XOR-via-`<>` idiom for "exactly one of two nullable FKs must be
   set" — a malformed insert is structurally impossible regardless of client bugs.

10. **Deep-linked comments highlight via pure CSS `:target`, zero JavaScript
    scroll code.** `notes-sheet.tsx`'s `CommentBlock`:
    ```tsx
    <div id={`comment-${comment.id}`}
         className="scroll-mt-24 rounded-lg border bg-muted/30 p-3 text-sm target:ring-2 target:ring-primary">
    ```
    The notification deep link's `#comment-<id>` URL fragment triggers the
    native browser same-page anchor scroll AND the `:target` pseudo-class
    ring, with no scroll-into-view JS at all. `scroll-mt-24` offsets the
    landing position so the sticky header doesn't cover it.

11. **Signoff vocab history wobble — always chase the constraint forward.**
    `0001_init.sql` defined 5 signoff values → `0003_status_review_vocab.sql`
    silently dropped to 4 (lost `revision not ready`) while doing an unrelated
    data migration → `0009_review_revision_not_ready.sql` restored it. If you
    only grep the first migration that defines `pages_signoff_check` you get
    the *wrong* authoritative list — always take the last migration that
    touches a given constraint (§1.2).

12. **`resolved_at` is client-stamped, not DB-defaulted — and issues have no
    equivalent column at all.** `review-table.tsx`'s `updateNote`:
    ```ts
    // When resolving, stamp resolved_at; clear it when reopening.
    const dbPatch: NotePatch & { resolved_at?: string | null } = { ...patch };
    if (patch.resolved !== undefined) {
      dbPatch.resolved_at = patch.resolved ? new Date().toISOString() : null;
    }
    ```
    This is the browser's wall clock (`new Date()`), not Postgres `now()` —
    a client with a wrong clock stamps a wrong `resolved_at`. Issues have no
    `resolved_at` at all; only `status === 'Resolved'` (no timestamp) — an
    explicit asymmetry between the two "done" mechanisms.

13. **`prev_action_to` is a single-slot snapshot, not a history stack**
    (§2.3) — going Ready-for-Review → reassign → Ready-for-Review again
    overwrites the first snapshot irrecoverably.

14. **`notify_action_to` is column-scoped (`AFTER INSERT OR UPDATE OF
    action_to`)** — it will not fire at all on an issue edit that leaves
    `action_to` untouched, and on `UPDATE` it array-diffs against `OLD` to
    notify only genuinely new assignees (§2.6.4) — re-saving an unchanged
    list is silent.

15. **The "Action to: Me" default is a mount-time default only.**
    `issues-table.tsx`: `useState<string>(currentUserEmail ? ME : ALL)` — if
    the user manually clears it to "Anyone" mid-session, it stays cleared;
    it is never re-applied later in that session.

16. **`features_status_check`/`features_signoff_check` are byte-for-byte
    duplicated `CHECK` lists**, not a shared enum/domain type (§1.5) — the
    same 7-status/5-signoff vocab is declared **twice** in the schema. A
    rebuild should use one shared enum type (Postgres `ENUM`, or a Python
    `Enum` + one constraint helper) instead of copy-pasting the value list
    per table — this is exactly the class of problem Volleyboard's generic
    `courts`/`balls` model (plan-v1.md §5) already solves by construction.

17. **`profiles` is a readable mirror of `auth.users`, synced by a DB
    trigger** — `0008_general_issues.sql`:
    > *"a readable mirror of auth.users so the app can list assignable users
    > (auth.users isn't queryable with the anon/authenticated key)"*
    kept in sync via `on_auth_user_created` (`AFTER INSERT ON auth.users`).
    This whole mirror-table mechanism is a Supabase-specific workaround — a
    rebuild with its own `players`/users table queries it directly and
    doesn't need an equivalent mirror.

18. **Author-gated edit/delete is enforced only in the UI, not at the data
    layer.** Every consuming component computes `canEdit = email !== null &&
    email === currentUserEmail` client-side, but the RLS policy on
    `notes`/`comments`/`issues`/`issue_comments` is uniformly `for all to
    authenticated using (true) with check (true)` — i.e. **any** authenticated
    client can currently `UPDATE`/`DELETE` any row directly via the Supabase
    client, bypassing the UI gate entirely. A rebuild with a real backend
    should enforce "only the author may edit/delete" server-side (a route
    guard, not just a disabled button), since a hand-rolled request against
    the API is no longer blocked by anything the review app's DB layer does.

19. **`notifications.kind` has no `CHECK` constraint** (§1.6) — the only vocab
    in the whole schema enforced purely by convention; the bell's defensive
    `?? "notified you"` fallback is written *because* nothing stops an
    unexpected `kind` value from reaching the client.

20. **Two duplicate StatChip/StatCard implementations** (§3.2) — functionally
    identical, minor prop-shape/class differences, never consolidated.
    Consolidate to one component in the rebuild.

21. **`EnumFilter`'s `capitalize` class is applied unconditionally**, including
    to the assignee filter whose `options` are raw email addresses — CSS
    `text-transform: capitalize` only affects the first letter of each
    whitespace-delimited word, so an email like `adi@example.com` renders
    with its leading letter capitalized (`Adi@example.com`) purely as a CSS
    side effect, not a data change. Cosmetic-only, but worth a conscious
    choice (keep the quirk, or scope the `capitalize` class to only the
    columns that need it).

22. **The `NoteBucket`/signed-URL flow is the *only* trust boundary for
    attachments** — there is no separate size/mime validation visible in the
    migrations or hook; `useImagePaste` accepts anything
    `clipboardData.items[i].type.startsWith("image/")` reports, and the
    upload call passes that same client-reported `contentType` straight to
    storage (`contentType: input.imageFile.type || "image/png"`). A rebuild's
    `POST /api/attachments` (plan-v1.md §6.5, "size/mime-capped") is a
    deliberate hardening over this, not a like-for-like port.