336a5890a8
Soft-delete cascade was the missing half of archive: stamping workspaces.archived_at alone left objects visible to anyone with a direct id. The cascade runs in one transaction so the partial state isn't reachable, and restore inverts it for any archived row in the workspace — provenance-blind on purpose until we have a use case that needs to distinguish per-workspace from per-object archives. audit_log keeps the keyset index on (workspace_id, created_at) and the actor_user_id FK with onDelete set null. recordAudit() refuses to write a null actor without a metadata.system_actor label so the audit view always has something to render. workspaces and invites mutations call recordAudit on success; objects-router instrumentation and the markdown importer's system-actor flow are filed as P2 follow-ups because each needs a thoughtful "what's audit-worthy?" pass, not mechanical wiring. Settings → Audit log lives at /<slug>/settings/audit, owner-gated, keyset-paginated. ACTION_LABELS is small on purpose; new actions fall back to their raw key so missing a label degrades gracefully. Co-authored-by: Cursor <cursoragent@cursor.com>
105 lines
6.9 KiB
Markdown
105 lines
6.9 KiB
Markdown
---
|
|
kind: task
|
|
slug: workspace-soft-delete-and-audit
|
|
title: Workspace soft-delete (archive/restore) and append-only audit log
|
|
plan_slug: multitenant-saas-hardening
|
|
epic_slug: tenant-lifecycle
|
|
status: in_progress
|
|
priority: P1
|
|
tenant_id: global
|
|
owner: unassigned
|
|
cursor_todo_id: null
|
|
updated_at: "2026-06-02"
|
|
---
|
|
|
|
# Task summary
|
|
|
|
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.
|
|
|
|
## Subtasks
|
|
|
|
- [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.
|
|
|
|
## Owner or assignee
|
|
|
|
Unassigned
|
|
|
|
## Status
|
|
|
|
ready
|
|
|
|
## Estimation
|
|
|
|
L
|
|
|
|
## Acceptance criteria
|
|
|
|
- [x] Archived workspace stops appearing in the workspace switcher (`listForUser` filters `archived_at IS NULL` — pre-existing).
|
|
- [x] Restoring an archived workspace makes its objects visible again (cascade in same txn; `restore` mutation added).
|
|
- [x] At least one `audit_log` row is written for every mutation in `workspaces` and `invites`. **Partial** for `objects` — deferred to follow-up.
|
|
- [x] Audit view renders paginated rows scoped to the current workspace (owner-only, keyset pagination).
|
|
|
|
## Links to related Epic / Plan
|
|
|
|
- Epic: `./Epic-tenant-lifecycle.md`
|
|
- Plan: `../Plan-multitenant-saas-hardening.md`
|