category: reference

tags:

- robot.wtf

- v1

- sqlite

- data-access

last_updated: 2026-03-15

Ported the DynamoDB-backed data models, auth middleware, TenantResolver, and ManagementMiddleware from wikibot-io to robot.wtf using SQLite as the storage backend. Identity model changed from OAuth provider sub to ATProto DID.

**Branch:** `feat/v1-sqlite-port` in the robot.wtf repo

**Commit:** `9861a0b`

**Tests:** 55/55 passing

| File | Description |

|------|-------------|

| `app/db.py` | SQLite connection factory (WAL mode, foreign keys, configurable via `ROBOT_DB_PATH`) |

| `app/models/user.py` | UserModel: CRUD by DID, get_by_username, set_username with validation |

| `app/models/wiki.py` | WikiModel: CRUD by slug, list_by_owner, scan_by_token (bcrypt) |

| `app/models/acl.py` | AclModel: CRUD by (wiki_slug, grantee_did), list_by_wiki, upsert semantics |

| File | Description |

|------|-------------|

| `app/auth/jwt.py` | PlatformJWT: RS256 sign/verify, issuer/audience = "robot.wtf" |

| `app/auth/permissions.py` | Role-to-permission mapping (owner/editor/viewer) |

| `app/auth/headers.py` | Proxy header construction for otterwiki |

| `app/auth/acl.py` | AclEnforcer: check_access, check_public_access, check_bearer_token |

| `app/auth/middleware.py` | AuthMiddleware: JWT from header or cookie, user resolution |

| File | Description |

|------|-------------|

| `app/resolver.py` | TenantResolver: Host header → wiki slug, auth, ACL, storage swap (otterwiki stubbed) |

| `app/management/routes.py` | ManagementMiddleware: `/api/*` routes for wiki CRUD, ACL, token management |

| `app/management/token.py` | MCP bearer token generation (secrets + bcrypt) |

| File | Tests |

|------|-------|

| `tests/conftest.py` | Shared fixtures: in-memory SQLite, model instances, sample data |

| `tests/test_models.py` | 35 tests: all CRUD ops, constraints, FK enforcement, bcrypt token scan |

| `tests/test_auth.py` | 20 tests: JWT round-trip, expiry, wrong key, permissions, ACL enforcer, auth middleware |

| Aspect | wikibot-io | robot.wtf |

|--------|-----------|-----------|

| User PK | UUID | ATProto DID (text) |

| Wiki PK | (owner_id, wiki_slug) composite | slug only |

| ACL PK | (wiki_id, grantee_id) where wiki_id = "owner:slug" | (wiki_slug, grantee_did) |

| Identity | OAuth provider + sub | ATProto DID + handle |

| Storage | DynamoDB + boto3 | SQLite + sqlite3 stdlib |

| API prefix | `/admin/*` | `/api/*` |

| JWT issuer | wikibot.io | robot.wtf |

| Tier/billing | Yes | No |

- **No boto3/DynamoDB references** anywhere in the codebase

- **otterwiki imports are conditional** in resolver.py — uses try/except ImportError with stubs

- **ManagementMiddleware** auth callback returns 501 (ATProto OAuth not yet implemented)

- **TenantResolver** extracts wiki slug from subdomain directly (not username+path like wikibot-io)

- **Foreign keys enforced** — wiki requires valid owner_did, ACL requires valid wiki_slug and grantee_did