Commit 6d8811
2026-03-16 05:00:01 Claude (MCP): [mcp] Add per-wiki database design note| /dev/null .. Design/Per_Wiki_Database.md | |
| @@ 0,0 1,74 @@ | |
| + | --- |
| + | category: reference |
| + | tags: [architecture, database, multi-tenancy] |
| + | last_updated: 2026-03-16 |
| + | confidence: high |
| + | --- |
| + | |
| + | # Per-Wiki Database Design |
| + | |
| + | ## Current State |
| + | |
| + | Otterwiki uses a single shared SQLite DB (`/tmp/otterwiki_default.db`) for all tenants. The `Preferences`, `User`, and `Drafts` tables are global. The `TenantResolver` swaps `GitStorage` (the git repo path) per-request but never swaps the SQLAlchemy DB binding. |
| + | |
| + | This hasn't caused problems because: |
| + | - `ProxyHeaderAuth.has_permission()` ignores the Preferences table entirely — permissions come from proxy headers |
| + | - User Management and Permissions admin panels are disabled via `PLATFORM_MODE` |
| + | - The `User` table is effectively dead code under `ProxyHeaderAuth` |
| + | |
| + | ## Why Change |
| + | |
| + | The user wants wiki owners to manage permissions through otterwiki's existing admin UI (READ_ACCESS, WRITE_ACCESS, etc.). This requires per-wiki Preferences at minimum. Without per-wiki DBs, one wiki admin's Preferences changes affect all wikis. |
| + | |
| + | ## Approaches |
| + | |
| + | ### Option 1: Per-request DB swap |
| + | |
| + | Each wiki gets its own SQLite file at e.g. `/srv/data/wikis/{slug}/wiki.db`. The resolver swaps `SQLALCHEMY_DATABASE_URI` per-request in addition to swapping `GitStorage`. |
| + | |
| + | - **Pro:** Otterwiki's admin panel writes land in the correct per-wiki DB naturally |
| + | - **Con:** SQLAlchemy engine re-binding per-request is heavy; Flask-SQLAlchemy uses a singleton `db` object initialized at import time |
| + | |
| + | ### Option 2: Side-channel read |
| + | |
| + | The resolver opens the per-wiki DB directly via `sqlite3.connect()`, reads Preferences, uses the values to compute permissions, then closes. Otterwiki itself never touches the per-wiki DB. |
| + | |
| + | - **Pro:** Simpler, no SQLAlchemy changes needed |
| + | - **Con:** Otterwiki's admin panel still writes to the shared DB, not the per-wiki one. Writes would need to be intercepted or redirected. |
| + | |
| + | ### Option 3: Hybrid (recommended) |
| + | |
| + | - Swap both `GitStorage` AND `SQLALCHEMY_DATABASE_URI` per-request (Option 1) |
| + | - Accept the complexity of engine re-binding as a one-time cost |
| + | - Each wiki bootstraps with its own SQLite DB at creation time (`_init_wiki_repo` in management routes) |
| + | - The resolver uses the per-wiki DB for both reads (permission computation) and writes (admin panel saves) |
| + | |
| + | ## DID-for-Email Question |
| + | |
| + | Otterwiki's `User` model uses email as the primary identifier. Robot.wtf uses ATProtocol DID handles (e.g. `@alice.bsky.social`), not emails. |
| + | |
| + | ### Option A: Store DID handle in email field |
| + | - Quick, minimal code changes |
| + | - The email field becomes a general "identity" field |
| + | - Display will look odd ("Email: @alice.bsky.social") |
| + | - Password fields become irrelevant (ATProto auth is external) |
| + | |
| + | ### Option B: Modify User model for DID-native identity |
| + | - Add `did` and `handle` columns, deprecate email as primary key |
| + | - Override the User Management template for DID-based display |
| + | - Cleaner but more fork divergence from upstream otterwiki |
| + | |
| + | ### Option C: Bridge via ProxyHeaderAuth |
| + | - Keep the proxy header architecture but have the resolver read per-wiki ACL settings from the per-wiki Preferences DB |
| + | - The `User` table remains unused; user identity comes from ATProto |
| + | - Permission levels (ANONYMOUS, REGISTERED, APPROVED) are set per-wiki via the Permissions admin panel |
| + | - The resolver maps these levels to proxy header permissions based on the authenticated user's status |
| + | |
| + | Option C avoids the DID-for-email problem entirely by keeping user management at the platform layer (ACL) while delegating access *policy* to otterwiki's Preferences. |
| + | |
| + | ## Prerequisites |
| + | |
| + | - Per-wiki SQLite DB creation during wiki bootstrap |
| + | - SQLAlchemy DB swap per-request (or side-channel read) |
| + | - Remove `@platform_mode_disabled` from Permissions panel (and possibly User Management) |
| + | - Ensure `ProxyHeaderAuth.has_permission()` reads from `app.config` (or is replaced) |