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