---
category: reference
tags:
  - robot.wtf
  - v1
  - sqlite
  - data-access
last_updated: 2026-03-15
confidence: high
---

# V1-1/V1-2: SQLite Data Access Layer & TenantResolver Port

## Summary

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

## Files Created (21 total)

### Data Access Layer
| 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 |

### Auth
| 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 |

### WSGI Middleware
| 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) |

### Tests
| 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 |

## Key Schema Differences from wikibot-io

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

## Architecture Notes

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