Home A - Z
Page Index
Properties 
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.
  • 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)