- Introduce isMissingCid predicate (typeof !== string || === "") applied to all
three sites: needsLookup check, unresolvedUris filter, resolvedThemes map.
Explicit cid:"" bypassed the previous typeof-only guard and would have been
written verbatim to the PDS as an invalid strongRef CID.
- Add test: cid:"" entry returns 400 (same as absent cid not found in DB)
- Add test: DB select failure during needsLookup returns 500

- Return 400 (not 200 with cid:"") when availableThemes contains uri-only entries
not found in the themes DB — empty string is not a valid AT Proto strongRef CID
- Wrap Response.json() calls in GET /admin/themes in inner try-catch so upstream
non-JSON responses are caught as parse errors rather than re-thrown as programming
errors via isProgrammingError(SyntaxError)
- Fix Bruno seq conflict: Duplicate Theme seq 4→5, List Themes seq 5→6

Add missing unauthenticated, 403, and network-error tests to all four POST
theme routes. Separate the 409 branch in POST /admin/themes/:rkey/delete to
return a web-layer-owned human-friendly message. Strengthen the availableThemes
assertion in the theme-policy success test to verify exact payload shape.

Add missing `href="/admin/themes"` assertions to three tests:
- wildcard (*) permission test: assert themes card IS shown
- manageMembers-only test: assert themes card is NOT shown
- manageMembers + moderatePosts combo test: assert themes card is NOT shown

Add missing negative assertions to ensure single-permission tests verify
that unrelated cards are not shown. The Themes card test now asserts that
members/structure/modlog links are absent; the manageCategories, moderatePosts,
banUsers, and lockTopics tests now assert that the themes link is absent.

* docs: add design doc for ATB-57 theme write API endpoints

* docs: add implementation plan for ATB-57 theme write API endpoints

* feat(appview): add manageThemes permission to Admin role (ATB-57)

* feat(appview): POST /api/admin/themes — create theme on Forum PDS (ATB-57)

* test(appview): add 401/403/PDS-failure tests for POST /api/admin/themes (ATB-57)

* feat(appview): PUT /api/admin/themes/:rkey — update theme on Forum PDS (ATB-57)

* feat(appview): DELETE /api/admin/themes/:rkey — delete theme, 409 if default (ATB-57)

* test(appview): assert 409 error body in dark-theme default check (ATB-57)

* test(appview): verify deleteRecord called with exact theme args (ATB-57)

* feat(appview): PUT /api/admin/theme-policy — upsert policy singleton on Forum PDS (ATB-57)

* test(appview): add theme-policy update path and updatedAt assertions (ATB-57)

* refactor(appview): strengthen theme-policy type guards and test assertions (ATB-57)

* docs(bruno): add Admin Themes collection for ATB-57 write endpoints

* docs: mark ATB-57 plan docs complete, move to docs/plans/complete/

* fix(appview): add 503 ForumAgent-not-authenticated tests; fix Bruno error code docs (ATB-57)

* docs: add ATB-55 theme read API design doc

Records approved design for themes table, theme_policies table,
theme_policy_available_themes join table, firehose indexer configs,
and GET /api/themes + GET /api/themes/:rkey + GET /api/theme-policy endpoints.

* docs: add ATB-55 theme API implementation plan

* feat(db): add themes, theme_policies, theme_policy_available_themes tables

Generate Postgres (0013) and SQLite (0001) migrations for the three new
theme tables. Build @atbb/db to verify schema compiles correctly.

* feat(appview): add GET /api/themes, /api/themes/:rkey, /api/theme-policy endpoints (ATB-55)

* feat(appview): index space.atbb.forum.theme and themePolicy from firehose (ATB-55)

* docs(bruno): add Themes API collection (ATB-55)

* docs: ATB-52 CSS token extraction design doc

* docs: ATB-52 implementation plan

* test(web): add failing preset completeness tests (ATB-52)

* test(web): improve preset test descriptions (ATB-52)

* feat(web): add neobrutal-light and neobrutal-dark JSON presets with font-size-xs token (ATB-52)

* feat(web): switch base layout to JSON preset import, remove TS preset (ATB-52)

* fix(web): replace all hardcoded CSS values with design tokens in mod and structure UI (ATB-52)

Define two new AT Proto record types for the theming system:
- space.atbb.forum.theme (tid key) — design tokens, color scheme, CSS overrides, font URLs
- space.atbb.forum.themePolicy (literal:self) — available themes, light/dark defaults, user choice toggle

Uses knownValues for colorScheme extensibility and strongRef wrapped in themeRef named def for CID integrity.

README was missing 3 of 5 packages (atproto, cli, logger).
Deployment guide had SESSION_TTL_DAYS default wrong (30 vs actual 7)
and was missing LOG_LEVEL, SEED_DEFAULT_ROLES, DEFAULT_MEMBER_ROLE
from the optional env vars table. Production env example was missing
FORUM_HANDLE and FORUM_PASSWORD variables.

* feat(web): admin mod action log page — /admin/modlog (ATB-48)

* docs: ATB-48 modlog UI implementation plan and completion notes

* fix(web): wrap modlogRes.json() in try-catch for non-JSON AppView responses (ATB-48)

A proxy returning HTML with HTTP 200 would cause Response.json() to throw
SyntaxError, which isProgrammingError() re-throws, producing an unhandled crash
instead of a 500 error page. Wrap with the same pattern used in the members
and role-assignment handlers.

Step-by-step TDD plan for GET /api/admin/modlog — requireAnyPermission
middleware, Drizzle alias double join, route handler, and Bruno collection.

Design for GET /api/admin/modlog — paginated mod action audit log with
double users join for moderator and subject handles, and requireAnyPermission
middleware for OR-based permission checks.

* docs: ATB-46 mod action log endpoint design

Design for GET /api/admin/modlog — paginated mod action audit log with
double users join for moderator and subject handles, and requireAnyPermission
middleware for OR-based permission checks.

* docs: ATB-46 mod action log implementation plan

Step-by-step TDD plan for GET /api/admin/modlog — requireAnyPermission
middleware, Drizzle alias double join, route handler, and Bruno collection.

* feat(appview): add requireAnyPermission middleware (ATB-46)

* test(appview): failing tests for GET /api/admin/modlog (ATB-46)

* feat(appview): GET /api/admin/modlog with double users leftJoin (ATB-46)

* docs(bruno): GET /api/admin/modlog collection (ATB-46)

* docs: move ATB-46 plan docs to complete/

* fix(appview): scope modlog queries to forumDid (ATB-46)

* feat(appview): add uri field to serializeCategory (ATB-47)

* test(web): add failing tests for GET /admin/structure (ATB-47)

* feat(web): add GET /admin/structure page with category/board listing (ATB-47)

* fix(web): use lowercase method="post" on structure page forms (ATB-47)

* test(web): add failing tests for category proxy routes (ATB-47)

* feat(web): add category proxy routes for structure management (ATB-47)

* test(web): add failing tests for board proxy routes (ATB-47)

* feat(web): add board proxy routes for structure management (ATB-47)

* feat(web): add CSS for admin structure management page (ATB-47)

* test(web): add missing network error tests for edit proxy routes (ATB-47)

* fix(web): log errors when boards fetch fails per-category in structure page (ATB-47)

* docs: add completed implementation plan for ATB-47 admin structure UI

* fix(web): validate sort order and fix board delete 409 test (ATB-47)

- parseSortOrder now returns null for negative/non-integer values and
redirects with "Sort order must be a non-negative integer." error;
0 remains the default for empty/missing sort order fields
- Use Number() + Number.isInteger() instead of parseInt() to reject
floats like "1.5" that parseInt would silently truncate
- Add validation tests for negative sort order across all 4 create/edit
handlers (create category, edit category, create board, edit board)
- Fix board delete 409 test mock to use the real AppView error message
("Cannot delete board with posts. Remove all posts first.") and assert
the message appears in the redirect URL, matching category delete test

* test(appview): add failing tests for POST /api/admin/boards (ATB-45)

* test(appview): add ForumAgent not authenticated test for POST /api/admin/boards (ATB-45)

* feat(appview): POST /api/admin/boards create endpoint (ATB-45)

* test(appview): add failing tests for PUT /api/admin/boards/:id (ATB-45)

* test(appview): add error body assertion to PUT boards malformed JSON test (ATB-45)

* feat(appview): PUT /api/admin/boards/:id update endpoint (ATB-45)

* test(appview): add failing tests for DELETE /api/admin/boards/:id (ATB-45)

* test(appview): improve DELETE /api/admin/boards/:id test coverage (ATB-45)

* feat(appview): DELETE /api/admin/boards/:id delete endpoint (ATB-45)

Pre-flight refuses with 409 if any posts reference the board (via posts.boardId).
Also fixes test error messages to use "Database connection lost" (matching isDatabaseError keywords) for consistent 503 classification.

* docs(bruno): add board management API collection (ATB-45)

* fix(appview): close postgres connection after each admin test to prevent pool exhaustion (ATB-45)

Each createTestContext() call opens a new postgres.js connection pool. With 93
tests in admin.test.ts, the old pools were never closed, exhausting PostgreSQL's
max_connections limit. Fix by calling $client.end() in cleanup() for Postgres.

* docs(plans): move ATB-44 and ATB-45 plan docs to complete/

* test(appview): add missing DB error 503 tests for board endpoints (ATB-45)

- POST /api/admin/boards: add "returns 503 when category lookup query fails"
- PUT /api/admin/boards/:id: add "returns 503 when board lookup query fails"
- PUT /api/admin/boards/:id: add "returns 503 when category CID lookup query fails"
(call-count pattern: first select passes, second throws)

Replace loose pattern checks (toMatch, toContain) with an exact toBe()
assertion using the seeded role's full AT URI. Also assert toHaveLength(1)
so the test fails if extra roles appear unexpectedly.

Add a startsWith("did:") guard in the POST /admin/members/:did/role handler
before the upstream fetch call. Malformed path parameters now return an inline
MemberRow error fragment without hitting the AppView. Covered by a new test.

* feat(appview): POST /api/admin/categories create endpoint (ATB-44)

* feat(appview): PUT /api/admin/categories/:id update endpoint (ATB-44)

* test(appview): add malformed JSON test for PUT /api/admin/categories/:id (ATB-44)

* test(appview): add failing tests for DELETE /api/admin/categories/:id (ATB-44)

* feat(appview): DELETE /api/admin/categories/:id delete endpoint (ATB-44)

* docs(bruno): add category management API collection (ATB-44)

* fix(appview): use handleRouteError after consolidation refactor (ATB-44)

PR #74 consolidated handleReadError, handleWriteError, and
handleSecurityCheckError into a single handleRouteError. Update the
new category management handlers added in this branch to use the
consolidated name.

* fix(appview): address category endpoint review feedback (ATB-44)

- Tighten sortOrder validation: Number.isInteger() && >= 0 instead of
typeof === "number" (rejects floats, negatives, NaN, Infinity per lexicon
constraint integer, minimum: 0)
- Add 503 "ForumAgent not authenticated" tests for POST, PUT, DELETE
- Add 503 database failure tests for PUT and DELETE category lookup
- Add 403 permission tests for POST, PUT, DELETE

* fix(appview): address final review feedback on category endpoints (ATB-44)

- Fix PUT data loss: putRecord is a full AT Protocol record replacement,
not a patch. Fall back to existing category.description and
category.sortOrder when not provided in request body.
- Add test verifying existing description/sortOrder are preserved on
partial updates (regression test for the data loss bug).
- Add test for DELETE board-count preflight query failure path (503),
using a call-count mock so category lookup succeeds while the second
select throws.

* fix(web): show reply count and last-reply date on board topic listing

The topic listing on board pages always showed "0 replies" (hardcoded)
and used the topic's own createdAt for the date instead of the most
recent reply's timestamp.

- Add getReplyStats() helper: single GROUP BY query computing COUNT() and
MAX(createdAt) per rootPostId for a batch of topic IDs in one round-trip
- Enrich GET /api/boards/:id/topics response with replyCount and lastReplyAt
per topic; fail-open so a stats query failure degrades gracefully to 0/null
- Update TopicResponse interface and TopicRow in boards.tsx to consume the
new fields; date now reflects lastReplyAt ?? createdAt
- Add 5 integration tests covering zero replies, non-banned count, MAX date
accuracy, banned-reply exclusion, and per-topic independence

* fix(web): address code review issues from PR #75

- Add fail-open error path test: mocks getReplyStats DB failure and
asserts 200 response with replyCount 0 / lastReplyAt null / logger.error called
- Fix UI attribution: when lastReplyAt is set, show "last reply X ago"
instead of raw date next to "by {author}" — disambiguates whose action
the timestamp refers to
- Update Bruno collection: add replyCount and lastReplyAt to docs example
and assert blocks for GET /api/boards/:id/topics
- Rebase: merge conflict in boards.ts imports resolved (handleReadError →
handleRouteError from PR #74 refactor)

* fix(web): update setupSuccessfulFetch type to include replyCount and lastReplyAt

The helper's topics array element type was not updated alongside makeTopicsResponse,
causing tsc to reject the new test cases with TS2353. Vitest strips types via esbuild
so it passed locally; tsc in CI caught the mismatch.

handleReadError, handleWriteError, and handleSecurityCheckError had
byte-for-byte identical implementations. Replaced with a single
handleRouteError function, eliminating ~250 lines of duplicated code
across the error handler and its tests. Updated all 10 call sites.

https://claude.ai/code/session_018SH9pay3PqGo9JDdAv3iRj

Co-authored-by: Claude <noreply@anthropic.com>

* feat(web): add canManageRoles session helper (ATB-43)

* feat(appview): include uri in GET /api/admin/roles response (ATB-43)

Add rkey and did fields to the roles DB query, then construct the AT URI
(at://<did>/space.atbb.forum.role/<rkey>) in the response map so the
admin members page dropdown can submit a valid roleUri.

* style(web): add admin member table CSS classes (ATB-43)

* feat(web): add GET /admin/members page and POST proxy route (ATB-43)

* fix(web): add manageRoles permission gate to POST proxy route (ATB-43)

* docs: mark ATB-42 and ATB-43 complete in project plan

* docs: add ATB-43 implementation plan

* fix(web): address PR review feedback on admin members page (ATB-43)

* fix(web): use canManageRoles(auth) instead of hardcoded false in rolesJson error path

* docs: ATB-42 admin panel landing page implementation plan

* feat(web): add hasAnyAdminPermission() helper to session.ts (ATB-42)

* test(web): add hasAnyAdminPermission tests + tighten JSDoc (ATB-42)

* feat(web): add GET /admin landing page with permission-gated nav cards (ATB-42)

* refactor(web): move canManageMembers/canManageCategories/canViewModLog to session.ts (ATB-42)

* test(web): add admin landing page route tests (ATB-42)

* test(web): add missing structure-absent assertions for banUsers/lockTopics (ATB-42)

* style(web): add admin nav grid CSS (ATB-42)

* docs: add admin panel UI preview screenshot (ATB-42)

* fix(web): address minor code review feedback on ATB-42 admin panel

- Use var(--font-size-xl, 2rem) for admin card icon (CSS token consistency)
- Add banUsers and lockTopics test cases for canViewModLog helper
- Move plan doc to docs/plans/complete/

Step-by-step plan: add axe-core + jsdom deps, create consolidated test
file with jsdom env pragma, one happy-path WCAG AA test per page route.

Captures the approved design for adding axe-core + jsdom automated
accessibility tests to apps/web — single consolidated test file,
per-file jsdom environment pragma, one happy-path test per page route.

* docs: ATB-34 axe-core a11y testing design

Captures the approved design for adding axe-core + jsdom automated
accessibility tests to apps/web — single consolidated test file,
per-file jsdom environment pragma, one happy-path test per page route.

* docs: ATB-34 axe-core a11y implementation plan

Step-by-step plan: add axe-core + jsdom deps, create consolidated test
file with jsdom env pragma, one happy-path WCAG AA test per page route.

* chore(web): add axe-core, jsdom, vitest as explicit devDependencies (ATB-34)

* test(web): scaffold a11y test file with jsdom environment and module mocks (ATB-34)

* test(web): add WCAG AA accessibility tests for all page routes (ATB-34)

* test(web): suppress document.write deprecation with explanatory comment (ATB-34)

* test(web): use @ts-ignore to suppress deprecated document.write diagnostic (ATB-34)

* docs: move ATB-34 plan docs to complete

* test(web): address PR review feedback on a11y tests (ATB-34)

- Fix DOMParser comment to explain axe isPageContext() mechanism accurately
- Add DOM replacement guard after document.write() to catch silent no-ops
- Wrap axe.run() in try/catch with routeLabel for infrastructure error context
- Add routeLabel param to checkA11y; update all 6 call sites
- Reset canLockTopics/canModeratePosts/canBanUsers in beforeEach
- Add afterEach DOM cleanup via documentElement.innerHTML
- Fix path.startsWith('/topics/1') to exact match '/topics/1?offset=0&limit=25'
- Add form presence guard in new-topic test to catch silent auth fallback
- Update design doc to document DOMParser divergence and its reason

* test(web): strengthen DOM write guard to check html[lang] (ATB-34)

afterEach now removes lang from <html> so the guard in checkA11y can use
html[lang] as proof that document.write() actually executed, rather than
just checking that the <html> element exists (which it always does after
afterEach resets innerHTML without touching attributes).

Adds missing \$type fields to forum and board ref objects written to the PDS,
consistent with the replyRef fix in PR #61. Without \$type, the AT Protocol
runtime guards Post.isForumRef()/isBoardRef() return false, which would silently
break any future indexer refactor that adopts typed guards over optional chaining.

Also adds Post.isForumRef()/isBoardRef() assertions to the corresponding tests,
following the same contract-based pattern established for replyRef.

Mark all default roles as critical and throw on any failure during
startup seeding. Previously, per-role errors were caught and swallowed,
allowing the server to start without a Member role — causing every new
user login to create a permanently broken membership with no permissions.

Also scope the existing-role check to ctx.config.forumDid so that roles
from other DIDs in a shared database don't incorrectly satisfy the
idempotency check.

Adds seed-roles unit tests covering the new fail-fast behavior.

Closes ATB-38

* fix(appview): include role strongRef when upgrading bootstrap membership

upgradeBootstrapMembership was writing a PDS record without a role field.
When the firehose re-indexed the event, membershipConfig.toUpdateValues()
set roleUri = record.role?.role.uri ?? null, overwriting the Owner's
roleUri with null and stripping their permissions on first login.

Fix: look up the existing roleUri in the roles table (parsing the AT URI
to extract did/rkey), then include it as a strongRef in the PDS record —
same pattern used by writeMembershipRecord for new members.

Closes ATB-37

* fix(appview): address review feedback on ATB-37 bootstrap upgrade

- Pass ctx.logger to parseAtUri so malformed roleUris emit structured
warnings at parse time
- Add explicit error log when parseAtUri returns null (data integrity
signal — a stored roleUri that can't be parsed is unexpected)
- Upgrade warn → error in DB failure catch: a failed role lookup during
bootstrap upgrade has the same consequence as the role-not-found path
(firehose will null out roleUri), so both warrant error level
- Add DB failure test for the role lookup catch block
- Add malformed roleUri test (parseAtUri returns null path)
- Add result.created assertions to tests 1 and 2
- Add DB state assertions to all three new bootstrap upgrade tests

Covers member management, forum structure CRUD (categories + boards),
and mod action audit log. Approach A: separate pages per section at
/admin/*.

All structure mutations follow PDS-first pattern (ForumAgent → firehose
→ DB) consistent with the rest of the AppView, not the bootstrap CLI's
dual-write shortcut.

- README: update packages/db to mention SQLite/LibSQL support alongside PostgreSQL
- README: add space.atbb.forum.board to the lexicon table (added by ATB-23)
- Deployment guide: fix stale plan doc path (moved to docs/plans/complete/)
- Deployment guide: update DATABASE_URL to document both PostgreSQL and SQLite formats
- Deployment guide: add SQLite overview paragraph to Section 4 Database Setup
- Deployment guide: update migration verification table list from 7 to 12 tables
- Deployment guide: bump version to 1.2, update date to 2026-02-26

* docs: reorganize completed plans and sync atproto-forum-plan

- Move all 45 completed plan docs from docs/plans/ to docs/plans/complete/
to distinguish finished work from active/upcoming plans
- Mark SQLite support as complete in the Future Roadmap section
- Add show-handles-in-posts as a completed Phase 4 item
- Update docs/plans/ path references to docs/plans/complete/

* docs: trim CLAUDE.md to gotchas-only, add CONTRIBUTING.md

CLAUDE.md's stated purpose is "common mistakes and confusion points."
Several sections had grown into general style guides and process docs
that don't serve that purpose:

- Remove generic testing workflow, quality standards, coverage expectations
→ moved to CONTRIBUTING.md
- Remove defensive programming boilerplate, global error handler template,
error testing examples → discoverable from existing code
- Remove security test coverage requirements and security checklist
→ moved to CONTRIBUTING.md
- Remove Bruno file template and testing workflow → moved to CONTRIBUTING.md

Bug fixes in CLAUDE.md:
- Fix stale `role.permissions.includes()` example → role_permissions join table
- Add `forum.board` to lexicon record ownership list (added by ATB-23)
- Add docs/plans/complete/ convention

CONTRIBUTING.md is a new contributor-facing document covering testing
workflow, error handling patterns (with examples), security checklist,
and Bruno template — content humans read before their first PR.

* docs: consolidate Bruno collections into top-level bruno/

apps/appview/bruno/ was a leftover from when the admin role endpoints
were first written. The top-level bruno/ collection was created later
but never picked up the three files that already existed:

- Admin/Assign Role.bru
- Admin/List Members.bru
- Admin/List Roles.bru

Move all three into bruno/AppView API/Admin/ alongside the backfill
endpoints. Remove the now-redundant apps/appview/bruno/bruno.json and
environments/local.bru.

Add missing environment variables (target_user_did, role_rkey) to both
local.bru and dev.bru so Assign Role.bru resolves correctly.

* feat: constrain oauth scopes

* docs: document oauth scopes and sync test mock

Add inline comment explaining each scope token's purpose and the %23
encoding requirement. Update auth test mock to reflect the new scopes.

* feat(db): add @libsql/client dependency for SQLite support

* feat(db): add SQLite schema file

* feat(db): add role_permissions table to Postgres schema (permissions column still present)

* feat(db): URL-based driver detection in createDb (postgres vs SQLite)

* feat(appview): add dialect-specific Drizzle configs and update db scripts

* feat(db): migration 0011 — add role_permissions table

* feat(appview): add migrate-permissions data migration script

Copies permissions from roles.permissions[] into the role_permissions
join table before the column is dropped in migration 0012.

* feat(db): migration 0012 — drop permissions column from roles (data already in role_permissions)

* feat(db): add SQLite migrations (single clean initial migration)

* feat(appview): update checkPermission and getUserRole to use role_permissions table

* feat(appview): update indexer to store role permissions in role_permissions table

- Remove permissions field from roleConfig.toInsertValues and toUpdateValues
(the permissions text[] column no longer exists on the roles table)
- Add optional afterUpsert hook to CollectionConfig for post-row child writes
- Implement roleConfig.afterUpsert to delete+insert into role_permissions
within the same transaction, using .returning({ id }) to get the row id
- Update genericCreate and genericUpdate to call afterUpsert when defined
- Rewrite indexer-roles test assertions to query role_permissions table
- Remove permissions field from direct db.insert(roles) test setup calls

* feat(appview): update admin routes to return permissions from role_permissions table

- GET /api/admin/roles: removed roles.permissions from select, enriches each
role with permissions fetched from role_permissions join table via Promise.all
- GET /api/admin/members/me: removed roles.permissions from join select, adds
roleId to select, then fetches permissions separately from role_permissions
- Updated all test files to remove permissions field from db.insert(roles).values()
calls, replacing with separate db.insert(rolePermissions).values() calls after
capturing the returned role id via .returning({ id: roles.id })
- Fixed admin-backfill.test.ts mock count: checkPermission now makes 3 DB
selects (membership + role + role_permissions) instead of 2
- Updated seed-roles.ts CLI step to insert permissions into role_permissions
table separately instead of the removed permissions column

* feat(appview): update test context to support SQLite via createDb factory

- Replace hardcoded postgres.js/drizzle-orm/postgres-js with createDb() from @atbb/db,
which auto-detects the URL prefix (postgres:// vs file:) and selects the right driver
- Add runSqliteMigrations() to @atbb/db so migrations run via the same drizzle-orm
instance used by createDb(), avoiding cross-package module boundary issues
- For SQLite in-memory mode, upgrade file::memory: to file::memory:?cache=shared so
that @libsql/client's transaction() handoff (which sets #db=null and lazily reconnects)
reattaches to the same shared in-memory database rather than creating an empty new one
- For SQLite cleanDatabase(): delete all rows without WHERE clauses (isolated in-memory DB)
- For Postgres cleanDatabase(): retain existing DID-based patterns
- Remove sql.end() from cleanup() — createDb owns the connection lifecycle
- Fix boards.test.ts and categories.test.ts "returns 503 on database error" tests to use
vi.spyOn() instead of relying on sql.end() to break the connection
- Replace count(*)::int (Postgres-specific cast) with Drizzle's count() helper in admin.ts
so the backfill/:id endpoint works on both Postgres and SQLite

* feat: add docker-compose.sqlite.yml for SQLite deployments

* feat(nix): add database.type option to NixOS module (postgresql | sqlite)

* feat(nix): include SQLite migrations and dialect configs in Nix package output

* feat(devenv): make postgres optional via mkDefault, document SQLite alternative

* refactor(appview): embed data migration into 0012 postgres migration

Copy permissions array into role_permissions join table inside the same
migration that drops the column. ON CONFLICT DO NOTHING keeps the script
idempotent for DBs that already ran migrate-permissions.ts manually.

* fix(appview): guard against out-of-order UPDATE in indexer; populate role_permissions in mod tests

- indexer.ts: add `if (!updated) return` before afterUpsert call so an
out-of-order firehose UPDATE that matches zero rows (CREATE not yet
received) doesn't crash with TypeError on `updated.id`
- mod.test.ts: add rolePermissions inserts for all 4 role setups so
test DB state accurately reflects the permissions each role claims
to have (ban admin: banUsers; lock/unlock mod: lockTopics)

* fix(appview): replace fabricated editPosts permission with real lockTopics in admin test

space.atbb.permission.editPosts is not defined in the lexicon or any
default role. Swap it for space.atbb.permission.lockTopics so the
GET /api/admin/members/me test uses a real permission value and would
catch a regression that silently dropped a real permission from a role.

Audit of plan doc against Linear issues and codebase state as of 2026-02-24.

Completed work added:
- ATB-25: separate bannedByMod column from deleted (Phase 3 bug fix, PR #56)
- ATB-35: strip title from reply records at index time (Phase 3 bug fix, PR #55)
- ATB-26: neobrutal design system, shared components, route stubs (Phase 4, PR #39)
- ATB-33: server-side offset/limit pagination for GET /api/topics/:id (Phase 4, PR #57)
- Fix ATB-30/31 attribution: compose forms are ATB-31, login/logout is ATB-30

Key Risks section:
- Mark PDS write path resolved (OAuth 2.1 + PKCE, ATB-14)
- Mark record deletion resolved (tombstone handling + bannedByMod split, ATB-25)

New Known Issues / Active Backlog section:
- ATB-39 (High): upgradeBootstrapMembership writes PDS record without role field
- ATB-38 (High): seedDefaultRoles partial failure should fail fast
- ATB-41 (Medium): missing $type on forumRef/boardRef in PDS writes
- ATB-34 (Low): axe-core WCAG AA automated tests
- Notes ATB-39/40 are duplicates of ATB-37/38

Future Roadmap:
- Add SQLite support (design approved, docs/plans/2026-02-24-sqlite-support-design.md)
- Update user self-deletion note: deleted_by_user column already in schema (ATB-25)

Designs dual-dialect database support (PostgreSQL + SQLite) with:
- URL-prefix detection in createDb factory
- role_permissions join table replacing permissions text[] array
- Two-phase Postgres migration with data migration script
- NixOS module and devenv changes for optional Postgres service
- Operator upgrade instructions with safety warnings