---
category: reference
tags: [architecture, database, multi-tenancy]
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`, `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`

### What the resolver swaps today

| Component | Swapped? | How |
|-----------|----------|-----|
| GitStorage | Yes | Monkey-patches `storage` across 7 modules |
| GitHttpServer | Yes | Recreated per-request |
| `app.config["REPOSITORY"]` | Yes | Direct assignment |
| SQLAlchemy `db` | **No** | All wikis share one DB |
| `app_renderer` | No | Created once at import time |
| Flask-Mail | No | Never swapped |
| `app.config` (other keys) | No | SITE_NAME, permissions, etc. are global |

## Option A: Per-Request DB Swap (implementing now)

### Storage layout

```
/srv/data/
├── robot.db                    (platform: users, acls, wikis, oauth)
└── wikis/{slug}/
    ├── repo/                   (git repo — already exists)
    └── wiki.db                 (otterwiki: preferences, drafts, user, cache)
```

### Mechanism

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.

## Option B: Upstream Contextvars Refactor (future PR to redimp)

### Core idea

New `otterwiki/context.py` provides getter functions backed by `contextvars`:
- `get_storage()`, `get_db()`, `get_renderer()`, `get_mail()`, `get_githttpserver()`
- Each falls back to the current module-level singleton when no context override is set
- `wiki_context()` context manager sets overrides for a block

### What stays the same

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

### Migration path

~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`

### Phasing

**PR 1** (small): Add `context.py`, `init_defaults()` call, migrate 2-3 leaf modules. Purely additive.
**PR 2** (wide, mechanical): Migrate remaining modules.

### Upstream pitch

- Testability: inject mocks via `wiki_context()` without real git repos or SQLite
- Flask best practice: `current_app` over direct `app` import
- Plugin flexibility: temporary renderer/storage overrides
- Backward compatible: getters fall back to singletons

### Constraint

`models.py` uses `db.Model` at class definition time — `db` can never fully move behind a getter. The contextvar is for session operations only.

## SQLite Multi-Tenant: Why It's Fine

Per-tenant SQLite is a well-established pattern (37signals, Turso, Laravel Tenancy, Rails Shardines). For our use case:

- 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

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.

Operational concern is **schema migrations** — lazy migration on connection open (`PRAGMA user_version`) is the simplest approach.