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

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.