2026-03-17 02:26:22Claude (MCP):
[mcp] Update per-wiki DB design with Option A implementation decision and upstream refactor sketch
Design/Per_Wiki_Database.md ..
@@ 1,74 1,129 @@
---
category: reference
tags: [architecture, database, multi-tenancy]
-
last_updated: 2026-03-16
+
last_updated: 2026-03-17
confidence: high
---
# Per-Wiki Database Design
+
## Decision (2026-03-17)
+
+
**Implementing Option A (per-request DB swap via monkey-patching) now.** A cleaner upstream refactor (contextvars-based dependency injection) is sketched below and will be proposed to redimp as a separate PR. Option A is throwaway work if the upstream refactor lands, but unblocks per-wiki permissions immediately.
+
## 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.
+
Otterwiki uses a single shared SQLite DB (`/tmp/otterwiki_default.db`) for all tenants. The `Preferences`, `User`, `Drafts`, and `Cache` 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`
1. Resolver calls `_swap_database(wiki_slug)` after `_swap_storage(repo_path)`
+
2. Updates `app.config["SQLALCHEMY_DATABASE_URI"]` to `sqlite:////srv/data/wikis/{slug}/wiki.db`
+
3. Calls `db.engine.dispose()` to close connections to previous DB
+
4. Lazy-creates `wiki.db` with schema if it doesn't exist (first request)
+
5. Calls `update_app_config()` to reload Preferences into `app.config`
+
+
### Schema initialization
+
+
Raw SQL in management API (avoids importing Otterwiki's server.py). Creates 4 tables matching `otterwiki/models.py`: `preferences`, `drafts`, `user`, `cache`. Seeds `SITE_NAME`.
+
+
### Permission model
+
+
**Platform ACLs remain authoritative.** ProxyHeaderAuth trusts proxy headers, ignores READ_ACCESS/WRITE_ACCESS. No change to auth flow. Per-wiki Preferences used only for wiki appearance/behavior settings.
+
+
### DID-as-email
+
+
Already handled — resolver injects `@handle` in the email header field. No User table rows needed under ProxyHeaderAuth.
+
+
### Migration
+
+
Lazy: if `wiki.db` doesn't exist when resolver hits it, create and seed on the spot. No data migration from shared default DB.
+
+
### Risks
+
+
- **Flask-SQLAlchemy engine caching**: `dispose()` + config update should work with FSA 3.x lazy engine creation. Fallback: access `db._engines` internals.
+
- **Worker model**: Assumes gunicorn sync workers. Async/threaded workers would race on global state.
+
- **`update_app_config()` side effects**: Recreates Flask-Mail per-call. Harmless.
+
- **Renderer**: Holds reference to `app.config` dict, so Preferences updates propagate. But `RENDERER_HTML_WHITELIST` parsed at init time won't update per-wiki.
-
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.
+
## Option B: Upstream Contextvars Refactor (future PR to redimp)
-
## Approaches
+
### Core idea
-
### Option 1: Per-request DB swap
+
New `otterwiki/context.py` provides getter functions backed by `contextvars`:
- Each falls back to the current module-level singleton when no context override is set
+
- `wiki_context()` context manager sets overrides for a block
-
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`.
+
### What stays the same
-
- **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
+
- `app` (Flask) stays as module-level singleton — Flask has its own `current_app`
+
- `db` stays importable for model definitions (`db.Model`, `db.Column`)
+
- All `@app.route` decorators unchanged
+
- `models.py` unchanged
-
### Option 2: Side-channel read
+
### Migration path
-
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.
+
~280 mechanical substitutions across 14 files:
+
- `storage` → `get_storage()`
+
- `app.config[...]` → `current_app.config[...]`
+
- `db.session.X()` → `get_db().session.X()`
+
- One lazy-init fix for `_serializer` in `helper.py`
-
- **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.
- Backward compatible: getters fall back to singletons
-
Otterwiki's `User` model uses email as the primary identifier. Robot.wtf uses ATProtocol DID handles (e.g. `@alice.bsky.social`), not emails.
+
### Constraint
-
### 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)
+
`models.py` uses `db.Model` at class definition time — `db` can never fully move behind a getter. The contextvar is for session operations only.
-
### 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
+
## SQLite Multi-Tenant: Why It's Fine
-
### 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
+
Per-tenant SQLite is a well-established pattern (37signals, Turso, Laravel Tenancy, Rails Shardines). For our use case:
-
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.
+
- Small per-tenant data (prefs, drafts, accounts)
+
- Read-heavy workload
+
- Single VPS, tens-to-hundreds of tenants
+
- Already using SQLite with per-wiki git repos
-
## Prerequisites
+
Postgres would add a daemon, configuration, and maintenance for no benefit. The pattern breaks down at tens of thousands of tenants — not a concern here.
-
- 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)
+
Operational concern is **schema migrations** — lazy migration on connection open (`PRAGMA user_version`) is the simplest approach.
