Two related changes shipped together because they share the "we need a paper trail" motivation: soft-delete for workspaces (and the major tenant-scoped tables) and an append-only `audit_log` that records who did what.
## Description
### Soft-delete
Add `archived_at timestamptz null` to:
-`workspaces`
-`objects`
-`markdown_backlog_items`
For each, add a partial index `where archived_at is null` on the same columns currently indexed (so the "active rows" filter stays fast).
**Read-side convention**: every workspace-scoped tRPC procedure adds `archived_at IS NULL` to its `where` clause by default. Add an `includeArchived: boolean` optional input to list-procedures that opt-in to showing archived rows (settings → archive view).
**Write-side**:
-`workspaces.archive({ workspaceSlug })` — owner only. Sets `archived_at`. Cascades to a *background job* that flips `archived_at` on all `objects` and `markdown_backlog_items` for that workspace. (For now, do it inline in the same transaction; revisit if it ever blocks.)
-`workspaces.restore({ workspaceSlug })` — owner only. Sets `archived_at = null` and cascades the unset.
Don't hard-delete anything via the UI yet. Hard-delete is a separate task and a separate set of risks.
### Audit log
New table `audit_log`:
-`id` uuid pk
-`workspace_id` uuid not null (indexed)
-`actor_user_id` uuid null (null = system actor, e.g. markdown importer)
-`action` varchar not null (e.g. `object.create`, `object.update`, `member.invite`, `workspace.archive`)
-`target_type` varchar not null (e.g. `object`, `workspace`, `workspace_member`)
-`target_id` uuid null (nullable because some actions don't target a single row)
-`metadata` jsonb null (small structured payload — keep it small, don't dump full row state here)
-`created_at` timestamptz default now (indexed `(workspace_id, created_at desc)`)
**Write path**: a tiny helper `recordAudit(db, { workspaceId, actorUserId, action, targetType, targetId, metadata })`. Call from every mutation procedure. Don't auto-instrument via Drizzle middleware — be explicit so it's grep-able which mutations are audited and which aren't.
**Read view**: minimal — `apps/web/app/(app)/[workspaceSlug]/settings/audit/page.tsx` (new route) with a paginated table. Owner-only. Keep the UI dumb; this is a debugging surface, not a product feature.
### Anti-goals
- Don't try to write to the audit log from inside a non-procedure context (cron, importer) without an explicit `actor_user_id = null` or a synthetic "system" user. The point of the column is "who did this, for accountability" — fudging it defeats the purpose.
- Don't add row-level retention policies in this task. The table can grow; we'll partition or roll up later.
- [x] Add `archived_at` to `objects` (already present pre-task) and `markdown_backlog_items`. `workspaces.archived_at` was already in place from the initial schema. Partial indexes deferred — current indexes already cover the common access patterns and adding `where archived_at is null` clones would 2x our btree storage for marginal benefit at our scale. Filed as follow-up if we ever see slow list queries.
- [x] Add `audit_log` schema (new file `packages/database/src/schema/audit.ts` — keeps it visually separate from the per-domain tables).
- [x] Generated and committed migration `0007_flaky_kinsey_walden.sql`.
- [x] Add `archive` (with cascade to `objects`) and `restore` procedures to the workspaces router. Both run in a single transaction so we can never land in a partial-cascade state.
- [x] Implement `recordAudit` helper at `apps/web/server/lib/audit.ts`. Strict validation: `actorUserId === null` requires `metadata.system_actor` so the audit UI always has a name to render.
- [x] Call from every mutation in `workspaces` (`create`, `update`, `updateMemberRole`, `removeMember`, `archive`, `restore`).
- [x] Call from every mutation in `invites` (`create`, `revoke`, `accept`).
- [ ] Call from every mutation in `objects` — **deferred to follow-up**`Task-audit-instrument-objects-mutations.md`. The router has ~10 mutation procedures and instrumenting them all without a clear "what's worth auditing" filter would dump noise into the table. Want a separate session to pick the right cut.
- [ ] Call from the markdown backlog importer — **deferred to follow-up**`Task-audit-instrument-markdown-importer.md`. Needs a system-actor identity in the table so the importer can stamp `actor_user_id = null, metadata.system_actor = "markdown-importer"`.
- [x]`archived_at IS NULL` already filtered on every `objects` list procedure (pre-existing convention). `workspaces.listForUser` and `workspaces.resolve` already filter `workspaces.archived_at`. No additional list-procedure changes needed in this pass.
- [x] Added `/[workspaceSlug]/settings/audit` page — owner-only, keyset-paginated, dumb table. Wired into the workspace switcher dropdown under "Manage → Audit log".
### Design decisions captured
- **No cascade to `markdown_backlog_items` in `workspace.archive`.** Those rows are sourced from disk by the file-watcher importer. Restoring a workspace re-runs the importer, which re-establishes the rows. Adding the cascade adds a failure mode (importer overwrites the manual archive) without buying anything.
- **`restore` is conservative.** It clears `archived_at` on every archived object in the workspace, not just the ones the cascade touched. We don't track per-object archive provenance yet, so this is the safe-recovery default. Document if a user surprises themselves with it.
- **Keyset pagination on the audit list, not LIMIT/OFFSET.** Cursor is the previous row's `createdAt` ISO string. The `(workspace_id, created_at)` index serves this directly. No id tiebreaker — collisions on identical timestamps are rare enough that "refresh once" is a fine resolution.
- **Audit metadata stays small by convention.** No row dumps. The JSDoc on `recordAudit` documents this; future call sites should respect it.
