category: reference

tags: [architecture, database, multi-tenancy]

last_updated: 2026-03-16

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`

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.

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

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.

- 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)

Otterwiki's `User` model uses email as the primary identifier. Robot.wtf uses ATProtocol DID handles (e.g. `@alice.bsky.social`), not emails.

- 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)

- 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

- 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.

- 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)