Activity

1. ryanbarlow97 commented on Oct 16, 2026

   

   ryanbarlow97

   on Oct 16, 2026

   ContributorAuthor

   More actions

   Moving province.db from SQLite to PostgreSQL 17

   Research plan, written from the code in ProvinceSystem at dd1e1674 (main, 2026-10-16). Paths are
   relative to ProvinceSystem/ unless they start with Docs/. Counts come from an AST scan of string
   literals plus rg; they are close but not exact (dynamic SQL built in f-strings is counted once per literal).

   Summary

   province.db is reached almost entirely through one seam, src.skins.db.connect()
   (backend/src/skins/db.py:14-19), which about 50 modules in backend/src import (59 counting server.py and the scripts). Only the Database studio
   (backend/src/dbstudio/studio.py:240) and the two host scripts (scripts/site-deploy.sh:67-74,
   scripts/ps-preview.sh:97-107) open the file directly. That makes a compatibility connection layer
   the cheapest safe route: keep the roughly 470 SQL literals (about 1,225 ? placeholders) as they are,
   fix the few dozen genuinely SQLite-only constructs at source while still on SQLite, and switch engines
   with DATABASE_URL.

   The real risks are not syntax. They are:

   1. Locking semantics. province.db runs in rollback-journal mode, where a writer cannot commit while
      another connection reads, and 42 code paths take BEGIN IMMEDIATE to serialise read-check-write
      sequences (single-use codes, leases, LuckPerms snapshots, Patreon brake). Postgres's default
      READ COMMITTED is weaker. The plan keeps today's single-writer behaviour with a transaction-scoped
      advisory lock in the connection layer.
   2. Connection cost. The code opens a connection per call (266 with connect() as conn: blocks,
      28 conn = connect() sites), which is free on SQLite and expensive on Postgres. A pool is required.
   3. Tests. 58 test files patch DB_PATH to a temporary file. They need one shared helper that
      works on both engines, and CI needs a Postgres service.
   4. Host tooling. Deploy backups and preview copies both assume a SQLite file.

   ---

   1. Inventory

   1.1 How province.db is opened

   Entry point	How	Notes
   backend/src/skins/db.py:14 connect()	sqlite3.connect(DB_PATH), row_factory = sqlite3.Row, PRAGMA foreign_keys = ON	Default isolation (implicit BEGIN before DML). Never closes on with exit.
   backend/src/skins/db.py:22 migrate()	executescript(schema.sql) then _upgrade()	Called at startup from backend/server.py:52 and by scripts and tests.
   backend/src/scripts/ledger/store.py:124 open_connection()	wraps connect()	Lets a caller run several ledger writes in one transaction (ingest.index_snapshot).
   backend/src/dbstudio/studio.py:240	direct `sqlite3.connect("file:…?mode=ro	rw", uri=True, isolation_level=None)`
   scripts/site-deploy.sh:67-74	sqlite3 backup API inside the backend container	Writes deploy-backups/province.db.before-deploy-*, keeps 10.
   scripts/ps-preview.sh:97-107	sqlite3 .backup of Dev's file, then DELETE + VACUUM	Per-preview copy.
   scripts/retag-realm.py:21	connect()	Staging tool, 16 execute calls.

   Not in scope: backend/src/coreprotect/reader.py:118 and the studio's coreprotect source open the
   Minecraft server's CoreProtect SQLite file. backend/src/auth/players.py reads both CoreProtect
   (lines 123-124, 343-369, which stay on SQLite) and province.db (lines 61-81, 411); the new layer must only
   apply to province connections.

   1.2 Modules that query province.db

   "SQL" is the number of SQL string literals; "connect" is the number of connect() call sites.

   Module	SQL	connect	Writes	Notes
   backend/src/skins/db.py	24	2	discord_links, submissions (fix-ups)	schema, _upgrade, 9 PRAGMA table_info
   backend/src/skins/codes.py	37	18	codes, sessions, cosmetic_mint_resets	named params at 197-217, lastrowid 730/955, BEGIN IMMEDIATE 716/930
   backend/src/skins/submissions.py	29	21	submissions, codes	sqlite3.IntegrityError 967, PRAGMA table_info 419/877, 8 sqlite3.Row hints
   backend/src/skins/drinks.py	36	27	drink_submissions, drink_textures, drink_catalog, drink_notifications	lastrowid 244
   backend/src/skins/discord_link.py	36	17	discord_links, discord_link_codes, microsoft_link_states, plugin_notices	BEGIN IMMEDIATE 321/353
   backend/src/skins/moderation.py	7	5	player_warnings, moderation_notifications	lastrowid 58/109
   backend/src/skins/notifications.py	4	3	skin_notifications	lastrowid 46
   backend/src/skins/plugin_notices.py	4	3	plugin_notices	lastrowid 47
   backend/src/skins/catalog.py	2	2	armourshop_catalog
   backend/src/skins/review_sheet.py, drink_review_sheet.py	3	3	none	reads submissions / drink_submissions
   backend/src/skins/seed_dev_code.py	3	1	codes	dev tool
   backend/src/characters/wardrobe.py	32	26	character_wardrobe_slots, character_create_wardrobe, character_creates, character_roster
   backend/src/characters/lore_items.py	33	17	lore_item_customisations	PRAGMA table_info 1416/1705/1801, COLLATE NOCASE 775
   backend/src/characters/creates.py	17	9	character_creates, lore_item_customisations
   backend/src/characters/wills.py	13	4	will_requests	BEGIN IMMEDIATE 134/212, LIKE … ESCAPE 318-320
   backend/src/characters/realm_wipe.py	11	2	creates, roster, wardrobe tables, lore items	dynamic IN (?,…) 136
   backend/src/characters/roster.py	9	5	character_roster, character_player_meta	executemany 573/593
   backend/src/characters/estates.py	5	(caller's)	character_estates, character_roster	sqlite3.Row hint 263
   backend/src/characters/rpc_player_meta.py, creation_catalog.py, pending_create.py	6	6	rpc_player_meta, creation_catalog
   backend/src/auth/users.py	17	7	users, user_sessions, sessions, discord_oauth_states	LIKE 'staff_view:%' 35, BEGIN IMMEDIATE 98/126
   backend/src/auth/view_as.py	17	6	users, user_sessions	4 × BEGIN IMMEDIATE
   backend/src/auth/microsoft_link.py	10	3	microsoft_link_states
   backend/src/auth/players.py	8	4	none	mixes CoreProtect reads
   backend/src/auth/admin.py, admin_cli.py, audit.py, preview_sign_in.py, discord_names.py, account_overview.py	17	9	users, user_sessions, admin_audit, preview_sign_in_tickets	admin_cli.py:68-85 takes --db <file>
   backend/src/luckperms/mirror.py	25	3	lp_state, lp_groups, lp_group_nodes, lp_tracks, lp_users, lp_user_nodes	INSERT OR REPLACE 88/98/134
   backend/src/luckperms/changes.py	20	5	lp_changes, lp_state	lastrowid 370
   backend/src/luckperms/commands.py, views.py	4	6	lp_commands
   backend/src/patreon/service.py	72	20	9 patreon_* tables	12 × BEGIN IMMEDIATE, COLLATE NOCASE 547
   backend/src/patreon/linking.py	19	8	patreon_oauth_states, patreon_pending_links, patreon_webhook_members
   backend/src/patreon/client.py	6	3	patreon_tokens	INSERT OR IGNORE … VALUES (1, ?, ?, ?) without a column list, 148
   backend/src/diagnostic/reports.py	4	3	diagnostic_reports	INSERT OR IGNORE 45
   backend/src/wars/declare_codes.py	6	5	war_declare_codes	lastrowid 185
   backend/src/api/admin_routes.py, database_routes.py, ledger_routes.py	3	3	none	json_extract in database_routes.py:60
   backend/src/scripts/chronicle/store.py	7	8	map_chronicle_snapshots
   backend/src/scripts/chronicle/wipe.py	4	3	snapshots, snapshots_archive	INSERT OR REPLACE 71
   backend/src/scripts/chronicle/restore.py	4	3	snapshots	INSERT OR IGNORE 158
   backend/src/scripts/chronicle/audit.py	5	5	map_chronicle_wipes	lastrowid 106, sqlite3.OperationalError 157
   backend/src/scripts/ledger/store.py	21	7	the 4 map_ledger_* tables	TEMP table 438-453, scalar MIN/MAX 386/390
   backend/src/scripts/ledger/wipe.py, reindex.py, remap_wealth_keys.py, ingest.py	4	3	ledger tables	through store
   backend/src/scripts/maps/archive_copy.py	2	2	chronicle + ledger tables	deletes by map
   backend/src/scripts/tools/generate_spoof_ledger.py			ledger tables	os.path.exists(DB_PATH) 723
   backend/src/dbstudio/studio.py	7	direct	any (root)	see 4.4
   backend/scripts/*_smoke.py (6 files)	18	13	various	rpc_player_meta_smoke.py already fails to parse (IndentationError at line 85)

   Total: about 600 SQL literals in backend/src, 42 BEGIN IMMEDIATE, 127 commit(), 18 rollback().

   1.3 Tables

   backend/src/skins/schema.sql creates 61 tables, 54 indexes and 3 triggers. With sqlite_sequence
   that is 62. The live files report 64, so they probably still hold the tables dropped from the schema in
   #85 (d98754de, 2026-10-04: armourshop_player_meta, drink_player_meta) or player_keys (removed
   99359c83, 2026-08-08). No code reads them. Before the copy, confirm with
   sudo sqlite3 -readonly …/province.db .tables on each site and decide whether to archive or drop them.

   W = writes, R = reads only (from the scan, with dynamic table names added by hand).

   Skins and cosmetic codes (6)

   • codes: W skins/codes.py, skins/submissions.py, skins/seed_dev_code.py; R auth/users.py, auth/view_as.py.
   • submissions: W skins/submissions.py, skins/db.py (_set_legacy_armor_sets); R characters/lore_items.py, skins/codes.py, skins/review_sheet.py.
   • sessions (code sessions): W skins/codes.py, auth/users.py; R auth/view_as.py.
   • skin_notifications: W/R skins/notifications.py.
   • cosmetic_mint_resets: W/R skins/codes.py.
   • armourshop_catalog (singleton id = 1): W/R skins/catalog.py.

   Drinks (4): drink_submissions, drink_textures, drink_catalog (singleton), drink_notifications. All W/R in skins/drinks.py; drink_submissions also R in skins/codes.py and skins/drink_review_sheet.py.

   Moderation (2): player_warnings, moderation_notifications. W/R skins/moderation.py.

   Discord and Microsoft linking (4)

   • discord_links: W skins/discord_link.py, skins/db.py (_move_nicknames_out_of_usernames); R auth/admin.py, auth/discord_names.py, auth/players.py, auth/view_as.py, luckperms/changes.py, luckperms/views.py, patreon/linking.py, patreon/service.py, skins/codes.py.
   • discord_link_codes: W skins/discord_link.py; R patreon/linking.py.
   • microsoft_link_states: W auth/microsoft_link.py, skins/discord_link.py.
   • plugin_notices: W skins/plugin_notices.py, skins/discord_link.py.

   Website accounts and sessions (5)

   • users: W auth/users.py, auth/view_as.py, auth/admin.py, auth/admin_cli.py; R auth/players.py, auth/preview_sign_in.py, luckperms/changes.py, luckperms/views.py, skins/discord_link.py.
   • user_sessions: W the same four auth modules; R auth/microsoft_link.py.
   • discord_oauth_states: W/R auth/users.py.
   • preview_sign_in_tickets: W/R auth/preview_sign_in.py.
   • admin_audit (append-only by trigger): W auth/audit.py (called from many routes); R api/database_routes.py; protected in dbstudio/studio.py:47.

   Characters (8)

   • character_creates: W characters/creates.py, characters/wardrobe.py, characters/realm_wipe.py; R characters/lore_items.py, characters/pending_create.py.
   • character_roster: W characters/roster.py, characters/estates.py, characters/wardrobe.py, characters/realm_wipe.py; R auth/players.py, characters/creates.py, characters/lore_items.py, characters/pending_create.py, characters/wills.py.
   • character_estates: W/R characters/estates.py.
   • will_requests: W/R characters/wills.py.
   • character_player_meta: W/R characters/roster.py.
   • rpc_player_meta: W/R characters/rpc_player_meta.py.
   • creation_catalog (singleton): W/R characters/creation_catalog.py.
   • lore_item_customisations: W characters/lore_items.py, characters/creates.py, characters/realm_wipe.py; R skins/submissions.py.

   Wardrobe (2): character_wardrobe_slots, character_create_wardrobe. W/R characters/wardrobe.py, characters/realm_wipe.py.

   Wars (1): war_declare_codes. W/R wars/declare_codes.py.

   Map chronicle (3)

   • map_chronicle_snapshots: W scripts/chronicle/store.py, chronicle/wipe.py, chronicle/restore.py, scripts/maps/archive_copy.py; R through store from api/chronicle_routes.py and chronicle/verify.py.
   • map_chronicle_snapshots_archive: W chronicle/wipe.py; R chronicle/restore.py.
   • map_chronicle_wipes: W/R chronicle/audit.py, used by api/chronicle_staff_routes.py.

   Economy ledger (4): map_ledger_days, map_ledger_factions, map_ledger_faction_days, map_ledger_guild_days. W scripts/ledger/store.py (through ingest.py, reindex.py, remap_wealth_keys.py), ledger/wipe.py, maps/archive_copy.py, tools/generate_spoof_ledger.py; R through store from api/ledger_routes.py and api/data_routes.py.

   Patreon (13): patreon_members, patreon_links, patreon_tokens (singleton, secrets), patreon_leases, patreon_sync_state (singleton, seeded at schema.sql:477), patreon_applied, patreon_desired, patreon_changes, patreon_alerts, patreon_brake_approvals, patreon_oauth_states, patreon_pending_links, patreon_webhook_members. W/R patreon/service.py, patreon/linking.py, patreon/client.py.

   LuckPerms mirror (8): lp_state, lp_groups, lp_group_nodes, lp_tracks, lp_users, lp_user_nodes (W luckperms/mirror.py, lp_state also luckperms/changes.py; R luckperms/views.py, auth/account_overview.py), lp_commands (luckperms/commands.py), lp_changes (luckperms/changes.py).

   Diagnostics (1): diagnostic_reports. W/R diagnostic/reports.py.

   ---

   2. Schema and migrations today, and on Postgres

   Today

   • migrate() (db.py:22-35) runs all of schema.sql with executescript on every start. Every
     statement is CREATE … IF NOT EXISTS, plus one INSERT OR IGNORE seed row (schema.sql:477).
   • _upgrade() (db.py:99-141) runs in one BEGIN IMMEDIATE transaction. It reads
     PRAGMA table_info for eight tables and adds missing columns one by one
     (users.role, users.placeholder, user_sessions.acting_user_id, discord_links.discord_nickname and
     link_method, codes.minted_via, submissions.tier_sets, character_creates.held_until, five
     character_roster death columns). It drops rpc_player_meta.donator_tier. It runs two data fix-ups:
     _move_nicknames_out_of_usernames (once, when the column is added) and _set_legacy_armor_sets
     (every start, only touches rows where tier_sets IS NULL).
   • Request code also checks columns at run time: lore_items.py:1416, 1705, 1801 and
     submissions.py:419, 877 call PRAGMA table_info and branch on whether a column exists.
     chronicle/audit.py:157 treats sqlite3.OperationalError as "table not created yet".
   • No version table exists. The schema is "whatever schema.sql plus _upgrade produce".

   On Postgres

   • Start every Postgres database at the final schema. None of _upgrade's column additions apply.
     The copied data already carries the results of the two fix-ups.
   • Add a small versioned runner (about 60 lines; no ORM, so Alembic adds little). It needs:
     • schema_migrations(version text primary key, applied_at timestamptz default now())
     • ordered files backend/src/database/migrations/NNNN_name.sql, each applied in its own transaction
     • pg_advisory_lock(<migrate key>) around the run, so two backends starting together (or a preview
       racing Dev) never apply the same file twice.
   • 0001_baseline.sql is schema.sql translated:
     • INTEGER PRIMARY KEY AUTOINCREMENT becomes bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY.
       BY DEFAULT lets the copy insert existing ids.
     • Every other INTEGER becomes bigint. SQLite integers are 64-bit, and several columns hold
       epoch values the plugins send (lp_state.generated_at, character_player_meta.account_created_at_epoch,
       lp_*_nodes.expiry, map_ledger_days.captured_at_ts). Docs/…/map/ledger.md:144 already
       documents signed 64-bit.
     • REAL becomes double precision. TEXT stays text.
     • Singletons keep integer PRIMARY KEY CHECK (id = 1).
     • The seed row becomes INSERT … ON CONFLICT DO NOTHING.
     • The three admin_audit triggers (schema.sql:779-795) become one plpgsql function
       with a BEFORE UPDATE OR DELETE … FOR EACH ROW trigger and a BEFORE TRUNCATE … FOR EACH STATEMENT
       trigger. The replace trigger is unnecessary because Postgres has no INSERT OR REPLACE, and the
       primary key already rejects a reused id. The TRUNCATE trigger is new: row triggers do not fire on
       TRUNCATE.
     • Partial and expression indexes (schema.sql:455-458, 660-662, 841) and the
       CHECK ((a IS NULL) <> (b IS NULL)) constraints (535, 548) are valid Postgres as written.
     • Foreign keys (submissions, sessions and drink_submissions to codes; user_sessions and
       preview_sign_in_tickets to users, with cascade) are always enforced in Postgres. Today they
       depend on PRAGMA foreign_keys = ON per connection.
   • Keep the column types as stored: dates as text, 0/1 flags as bigint, JSON as text. Changing
     types is a separate project, because the stored formats are mixed:
     • most code writes %Y-%m-%dT%H:%M:%SZ (for example skins/codes.py:30)
     • characters/estates.py:64 writes milliseconds
     • patreon/resolver.py:14 and diagnostic/reports.py:40-81 write isoformat() with +00:00 and
       microseconds.
       Converting would change every value the API returns.
   • Create each database with --locale-provider=builtin --builtin-locale=C.UTF-8 (Postgres 17).
     Text comparison and ORDER BY then follow code-point order, like SQLite's BINARY collation; the
     default en_US.UTF-8 would reorder names and break lexical timestamp ranges with mixed Z and
     +00:00 suffixes.
   • While both engines are supported, a parity test compares the tables, columns, nullability and primary
     keys that schema.sql and the Postgres migrations produce. Any schema change in that window must touch
     both; keep the window short.

   ---

   3. Dialect issues

   Counts are production code (tests in brackets). "Portable" means the fix also works on SQLite 3.35+, so
   it can land before the switch. Python 3.12 images ship SQLite 3.40 or later; the host's system Python 3.10
   has 3.37.

   Issue	Count	Examples	Fix
   ? placeholders	~470 literals, ~1,225 marks (177 test literals)	everywhere; dynamic lists at characters/wardrobe.py:1239, realm_wipe.py:136, lore_items.py:267, 1720, roster.py:567, wills.py:264, skins/drinks.py:1268, ledger/store.py:284, 320	Translator in the connection layer (section 5).
   Named params :name	2 queries	skins/codes.py:197-217 (+ _COUNTS_TOWARD_COOLDOWN 175-183)	Translator maps to %(name)s when params is a mapping. Or make positional (portable).
   Literal % in SQL	2	auth/users.py:35 LIKE 'staff_view:%', luckperms/mirror.py:367 LIKE 'group.%'	Translator doubles every % when params are passed. psycopg parses % even inside string literals.
   cursor.lastrowid	10	wars/declare_codes.py:185, skins/drinks.py:244, plugin_notices.py:47, notifications.py:46, moderation.py:58, 109, codes.py:730, 955, chronicle/audit.py:106, luckperms/changes.py:370	INSERT … RETURNING id (portable). psycopg has no lastrowid.
   last_insert_rowid()	0 (4 tests)		Tests only.
   rowid	0 province	dbstudio/studio.py:398-407 (max(rowid) row counts); auth/players.py:447, 455 is CoreProtect	Studio only.
   INSERT OR IGNORE	5	diagnostic/reports.py:45, patreon/client.py:148, chronicle/restore.py:158, ledger/store.py:444 (temp table), schema.sql:477	ON CONFLICT DO NOTHING (portable).
   INSERT OR REPLACE	4	luckperms/mirror.py:88, 98, 134, chronicle/wipe.py:71	ON CONFLICT (pk) DO UPDATE SET … (portable).
   ON CONFLICT … DO UPDATE	30	29 qualify correctly; ledger/store.py:379-395 uses bare first_seen_day/last_seen_day on the right-hand side	Postgres raises "column reference is ambiguous". Qualify with the table name (portable).
   Scalar MIN(a, b) / MAX(a, b)	2	ledger/store.py:386, 390	CASE WHEN … END (portable; SQLite has no LEAST).
   COLLATE NOCASE	2	characters/lore_items.py:775 (ORDER BY), patreon/service.py:547 (minecraft_name = ? COLLATE NOCASE)	lower(x) on both sides (portable).
   json_extract	1	api/database_routes.py:60	Parse detail_json in Python (portable), or detail_json::jsonb ->> 'database'.
   dict(cursor) of pairs	1	api/database_routes.py:59	Works if rows stay tuple-like (see row class).
   strftime / datetime('now') / julianday / unixepoch in SQL	0 (1 test)		None. All times are made in Python.
   WITHOUT ROWID	0	studio comment only, studio.py:407	None.
   sqlite3.Row behaviour	1 factory, 13 type hints	factory db.py:17; hints submissions.py:138-1391 (8), wills.py:72, 84, 93, estates.py:263; row.keys() in submissions.py:139-272, 1275; positional fetchone()[0] / r[0] in patreon/service.py:184, 311, 431, 481, 511, 533, patreon/linking.py:305, luckperms/views.py:61, luckperms/mirror.py:349, auth/admin_cli.py:45, submissions.py:419, 877; dict(row) about 46 sites	A psycopg row factory returning a tuple subclass with keys() and name lookup. That covers all of these unchanged. Replace hints with database.Row.
   executescript	1 (9 tests)	db.py:33	Migration runner; psycopg runs a multi-statement string when no params are passed.
   executemany	9	roster.py:573, 593, ledger/store.py:308, 341, 378, 443, luckperms/mirror.py:90, 99, 134	Works in psycopg 3 (pipelined).
   isolation_level	1	studio.py:241	Studio rewrite.
   BEGIN IMMEDIATE	42	patreon/service.py (12), patreon/linking.py (5), auth/view_as.py (4), luckperms/changes.py (3), auth/users.py, auth/microsoft_link.py, characters/wills.py, skins/codes.py, skins/discord_link.py (2 each), auth/admin.py:56, auth/admin_cli.py:28, auth/preview_sign_in.py:97, luckperms/mirror.py:125, patreon/client.py:164, api/admin_routes.py:267, skins/db.py:101, studio.py:246	Not valid Postgres. The layer turns it into BEGIN plus the writer advisory lock (section 5).
   PRAGMA	17 executed	db.py:18, 103-134; runtime column checks lore_items.py:1416, 1705, 1801, submissions.py:419, 877; studio.py:245, 248	Remove the runtime checks (portable, the columns exist). foreign_keys is implicit on Postgres.
   sqlite_master / sqlite_sequence	3	studio.py:218, 367, 451	Studio already has Postgres catalogue queries (studio.py:375-386, 446-450).
   ATTACH	0	studio refusal list only (studio.py:51)	None.
   TEMP tables	1	ledger/store.py:438-453 (temp. prefix, INSERT OR IGNORE)	On Postgres, faction_key <> ALL(%s) with a list parameter; no 999-variable limit. Branch on engine, or drop the temp table once SQLite support ends.
   sqlite3 exceptions	5	submissions.py:967 (IntegrityError for slug conflicts), auth/discord_names.py:170, 202 (sqlite3.Error), chronicle/audit.py:157 (OperationalError), studio	Export database.IntegrityError / DatabaseError (tuples of both drivers' classes) and catch those.
   Errors abort the transaction		submissions.py:967 re-raises, so fine. Any code that catches a database error and keeps using the same connection breaks on Postgres ("current transaction is aborted").	Audit each except around execute; the Postgres CI run finds the rest.
   Type affinity: booleans		Most sites bind 1 if x else 0 (auth/users.py:162, 225, preview_sign_in.py:83); any True that reaches an integer column fails on Postgres	Register a bool → int dumper on province connections. No province column is boolean.
   Type affinity: text versus integer		SQLite compares text_col = 123 loosely; Postgres raises operator does not exist	No static finds; the Postgres test run is the check.
   Type affinity: integer width		see section 2	bigint everywhere.
   BLOBs	0	none in province.db (CoreProtect only)	None.
   LIKE case	6	SQLite LIKE ignores ASCII case, Postgres does not; all province uses lower the input first (wills.py:318, mirror.py:342) or match lowercase keys (users.py:35, mirror.py:367)	None needed; keep in review.
   NULL ordering	66 ORDER BY	SQLite sorts NULL first in ASC, Postgres last. Nullable sort keys: submissions.py:1304 (reviewed_at), drinks.py:1257; creates.py:641 already uses COALESCE	Add NULLS FIRST where it matters.
   GROUP BY bare columns	0	the two GROUP BY queries (mirror.py:367, database_routes.py:62) are valid	None.
   with conn: semantics	266 with connect() + 13 with conn:	sqlite3 commits or rolls back but never closes; psycopg 3 closes on with exit	The layer's __exit__ commits or rolls back, then returns the connection to the pool. An AST scan found no production code using conn after its with connect() block (the hits were re-bindings).
   total_changes / rowcount	1 / 37	studio.py:253; rowcount works in psycopg	Studio only.

   ---

   4. Everything outside request code that assumes a SQLite file

   4.1 scripts/site-deploy.sh

   • 64-78 back up with SQLite's backup API inside the container to deploy-backups/province.db.before-deploy-<sha>-<time> and keep 10.
   • Replace with docker compose exec -T db pg_dump -Fc -d province_<site> to deploy-backups/province.before-deploy-<sha>-<time>.dump, keeping 10, plus pg_restore --list as a cheap integrity check.
   • Run pg_dump in the database container: the backend image (python:3.12-slim) has no client tools.
   • During the transition, branch on whether the site's override sets DATABASE_URL.
   • The installed copy is root's /usr/local/sbin/site-deploy, so it must be reinstalled after merging.
   • The Deploy workflow message (.github/workflows/deploy.yml:75) still holds.

   4.2 scripts/ps-preview.sh

   • 88-107 rsync Dev's data without *.db, copy province.db with sqlite3 .backup, then delete from sessions, user_sessions, discord_oauth_states and discord_link_codes, and VACUUM.
   • New flow:
     • createdb pv_<slug> (hyphens to underscores) with its own role and password, saved in $dir/keys next to STAFF_KEY.
     • pg_dump -Fc province_dev | pg_restore --no-owner --role=pv_<slug> -d pv_<slug>. About 7 MB, so seconds.
     • The same four DELETEs through psql.
     • DATABASE_URL and a small pool size in write_compose (115-167).
     • do_down/cmd_reap (169-238) add DROP DATABASE … WITH (FORCE) and DROP ROLE.
   • CREATE DATABASE … TEMPLATE province_dev will not work, because Dev's backend is always connected to it.
   • The database container must join the ps-previews network. Each database should revoke CONNECT from PUBLIC, so a preview role reaches only its own database.

   4.3 backend/src/scripts/** and other tools

   • Chronicle (store.py, wipe.py, restore.py, audit.py, verify.py) and ledger (store.py, wipe.py, reindex.py, remap_wealth_keys.py, ingest.py): SQL fixes are in section 3. They also take file locks (scripts/util/maplock.py), which are unaffected. chronicle/audit.py:157's missing-table fallback goes.
   • scripts/maps/archive_copy.py: plain deletes; nothing beyond placeholders.
   • scripts/tools/generate_spoof_ledger.py:723: treats "no DB_PATH file" as "no data". On Postgres this becomes "the ledger table is empty" or "the database is unreachable".
   • auth/admin_cli.py:68-85 (--db <file>): change to --database-url.
   • scripts/retag-realm.py and scripts/staging-import-prod-data.sh: the import rsyncs backend/src/data (which then holds no database) and retags in place. Change it to pg_dump/pg_restore then retag, or retire it if previews have replaced it.
   • backend/scripts/*_smoke.py: they call migrate() and connect(), so they follow the layer. Fix rpc_player_meta_smoke.py:85 while there.
   • backend/benchmarks/coreprotect_reader.py: CoreProtect only; unaffected.

   4.4 Database studio (backend/src/dbstudio/studio.py)

   • The site source is hard-wired to SQLite:
     • sources() (86-88) and _site() (234-266).
     • The authorizer (206-230) refuses ATTACH, transactions, triggers and non-schema pragmas. It also refuses writes to sqlite_sequence and any change to admin_audit.
     • schema() (397-408) estimates rows with max(rowid); _measure() (451-455) sums file and -wal sizes.
   • The Postgres half (_postgres, POSTGRES_SCHEMA, the _check dollar-quote handling) is already there, keyed on SUPABASE_DB_URL (304).
   • On Postgres, the site source runs _postgres with DATABASE_URL under a dedicated province_studio role. The role should:
     • not own any table, so it cannot DROP/ALTER/TRUNCATE or disable triggers
     • have SELECT only on admin_audit
     • have USAGE but not UPDATE on sequences, so no setval, which matches the sqlite_sequence rule
     • have DML on the rest.
   • That role replaces the authorizer. _check() stays as the second layer.
   • The frontend already handles engine names generically (frontend/lib/admin/database.ts:6, DatabaseList.tsx:22).
   • api/database_routes.py:59-62 uses json_extract (section 3).

   4.5 Tests

   • 155 test files and about 1,980 tests in total. 58 files touch province.db, holding about 860 tests.
   • Eight conftest.py fixtures do the same thing: auth, dbstudio, diagnostic, luckperms, patreon, rail, servers, scripts/ledger. Each patches db.DB_PATH, DATA_DIR and the three directories, then calls migrate().
   • About 30 unittest-style classes assign db_mod.DB_PATH = root / "province.db" in setUp and restore it in tearDown. Examples: skins/test_drinks.py:33-55, characters/test_realm_wipe.py:39-66, api/test_skins_routes.py:30-82.
   • The root backend/conftest.py:16-17 aliases sys.modules["skins.db"], and api/data_routes.py:27-33 documents how that aliasing once pointed the ledger at the wrong database. Keep one module object as the seam.
   • SQLite-specific tests:
     • tests/test_database_schema.py (iterdump, PRAGMA foreign_keys)
     • scripts/chronicle/test_schema_indexes.py:52, 95 (sqlite_master)
     • legacy-schema upgrade tests in auth/test_admin.py:99-123, auth/test_view_as.py:316-331, auth/test_discord_nicknames.py:32 and characters/test_create_hold.py:97-111 (raw sqlite3.connect + executescript + PRAGMA)
     • scripts/chronicle/test_capture.py:38 and test_verify.py:36 (executescript(schema.sql))
     • patreon/test_linking.py:184, 242, which assert a token is absent from the file bytes
     • dbstudio/test_studio.py:16-310 (authorizer, PRAGMA writable_schema, sqlite_master).
   • The existing Postgres tests (patchnotes/test_db.py, precedent/test_db.py) mock psycopg2 completely. No test runs a real Postgres.
   • api/test_ledger_routes.py:721 and api/test_chronicle_routes.py:760 count connect() calls. They stay valid with a pool.

   4.6 CI (.github/workflows/build.yml)

   • One job, python -m pytest -q (line 47), no database service.
   • Add a postgres service (pgvector/pgvector:pg17, health check pg_isready, tmpfs data, -c fsync=off -c synchronous_commit=off -c full_page_writes=off) and TEST_DATABASE_URL.
   • Run a db: [sqlite, postgres] matrix while both engines are supported.
   • preview.yml builds images only and is unaffected.

   4.7 Compose and host

   • docker-compose.yml (www) has no database service.
   • docker-compose.staging.yml (Dev) already runs patchnotes-db: pgvector/pgvector:pg16 for patch notes (lines 17-38).
   • Both mount backend/src/data, which keeps the skin, wardrobe and drink files after the move.
   • Secrets live in each site's host-only docker-compose.override.yml/.env. Back these up before editing and never print them.
   • Any host backup job outside the repo that copies provincesystem-data/data/province.db (nightly copies, maintenance-backups/, which ps-preview.sh:90 excludes) needs a pg_dump equivalent. Check the host's timers before cutover.

   4.8 Documentation

   • ProvinceSystem guides (Docs repo):
     • Docs/projects/ProvinceSystem/docs/architecture.md:12, 53, 66, 149-167
     • overview.md:43, 67
     • docs/README.md:53
     • DEPLOY.md:31, 67, 74, 79, 89 (backups and preview data)
     • docs/ops/database-studio.md:16-66
     • docs/map/ledger.md:144, 175, 185, 206
     • docs/cosmetics/skins.md:108
     • docs/identity/tfmcweb.md:78
     • docs/identity/auth-security.md:198
     • docs/integrations/patreon-backend.md:9
     • docs/integrations/precedent.md:188
   • Elsewhere:
     • workspace AGENTS.md:90 ("backs up province.db")
     • backend/src/luckperms/README.md.

   ---

   5. Recommended approach

   5.1 One database package, psycopg 3, a compatibility layer first

   Add backend/src/database/ (__init__.py, postgres.py, sqlite.py, translate.py, migrations/,
   testing.py). src/skins/db.py keeps DATA_DIR, SKINS_DIR, WARDROBE_DIR, DRINKS_DIR and
   re-exports connect and migrate, so none of the importing modules change in the first PRs.

   The engine is chosen by DATABASE_URL. If it is unset, the layer uses SQLite at DB_PATH (today's
   behaviour); postgresql://… selects Postgres. The Postgres connection wrapper should provide:

   • Pool: psycopg_pool.ConnectionPool (new pinned dependency psycopg-pool). Sizes come from the
     environment: about 15 on www, 5 on previews. The checkout timeout is 10 s. connect() returns a wrapper
     around a pooled connection; close() and __exit__ return it to the pool.
   • sqlite3-style transactions: the connection runs in autocommit, and the wrapper issues BEGIN
     itself before the first INSERT/UPDATE/DELETE. That copies Python sqlite3's default mode, where
     SELECTs outside a transaction see the latest committed data. commit() and rollback() act only when
     a transaction is open, and in_transaction is exposed.
   • Single-writer lock: BEGIN IMMEDIATE, and the first write of any implicit transaction, also run
     SELECT pg_advisory_xact_lock(<writer key>). Writers are then serialised as they are on SQLite today
     (rollback journal: one RESERVED lock), and the 42 read-check-write paths stay safe without rewriting
     them. Set lock_timeout = '5s' (sqlite3's default busy timeout), statement_timeout = '30s' and
     idle_in_transaction_session_timeout = '60s'. Taking the lock before any row lock means it cannot
     deadlock. The remaining gap is a transaction that reads before its first write without
     BEGIN IMMEDIATE: on Postgres it can act on a value another writer has just changed. List these
     during the per-area PRs and move them to BEGIN IMMEDIATE or SELECT … FOR UPDATE.
   • Translator (cached by SQL text):
     • Turn ? into %s outside quotes, comments and dollar-quotes.
     • Turn :name into %(name)s only when params is a mapping, and never touch ::casts.
     • Double every % when params are present.
     • Leave SQL without params untouched.
     • Unit-test it on its own.
   • Rows: a row factory that returns a Row(tuple) with keys() and string indexing, matching
     sqlite3.Row. Positional access, unpacking, dict(row) and dict(cursor) then all keep working.
   • Adapters: bool dumped as integer; TimeZone=UTC.
   • Errors: database.IntegrityError, OperationalError and DatabaseError are tuples of both
     drivers' classes, for except clauses.

   Why not rewrite the queries per module first? About 470 literals and 1,225 placeholders across
   about 50 modules would mean 30 to 40 large, risky PRs with no way to run both engines meanwhile. The layer lets
   the existing suite run unchanged against both engines. Once SQLite is gone, an optional mechanical codemod
   can switch the SQL to %s and delete the translator, leaving pool, rows and lock. The ? style could also
   stay for good; the translator is about 80 lines.

   One driver: after cutover, move precedent/db.py, patchnotes/db.py, api/precedent_routes.py,
   api/patchnotes_routes.py and the three precedent scripts from psycopg2 to psycopg 3:

   • pgvector.psycopg.register_vector
   • psycopg.types.json.Jsonb for Json
   • dict_row for RealDictCursor
   • watch out: psycopg 3 closes the connection on with conn:.

   Then drop psycopg2-binary, and give the root-owned patch note job copy (tfmc-patchnotes-update)
   psycopg[binary]. Precedent and patch notes keep their own SUPABASE_DB_URL.

   5.2 Topology

   Use two Postgres 17 servers (pgvector/pgvector:pg17):

   • one in the www compose project (province_www)
   • one in the staging project for province_dev and all pv_* preview databases.

   That keeps preview churn and Dev load away from www, and fits max_connections (Dev 15 + 4 previews × 5).

   • Upgrading staging's existing pg16 patchnotes-db to 17 needs a dump and restore of its patch note data, or a separate pg17 service.
   • Bind ports to 127.0.0.1 or keep them internal to the compose network.
   • Each database gets an owner role for migrations and the app, plus a province_studio role.

   5.3 Fast tests on Postgres

   Transaction rollback per test will not work. The code under test commits, and it opens several
   connections (and, with the pool, several sessions) per test.

   • Session setup: create ps_test_<worker> once per session, run the migrations once, and point the
     pool at it.
   • Before each test: SET session_replication_role = replica; TRUNCATE <all province tables> RESTART IDENTITY CASCADE;
     then re-seed patreon_sync_state(1). replica skips the admin_audit TRUNCATE trigger. The CI role
     must be a superuser, which is fine for a throwaway service. Expect 10 to 30 ms per test, roughly
     10 to 25 s over the ~860 database tests.
   • Shared helper: database.testing.fresh_database(tmp_path) serves both engines. On SQLite it
     patches DB_PATH as now. Use it from all 8 conftests and the ~30 unittest setUp methods.
   • Engine-specific tests: mark the SQLite-only legacy-upgrade, iterdump and authorizer tests
     @pytest.mark.sqlite_only. Rewrite the file-bytes secret checks as queries. Add Postgres equivalents
     for the studio role and the admin_audit triggers.
   • Locally: docker run --rm -d -p 55432:5432 -e POSTGRES_PASSWORD=test --tmpfs /var/lib/postgresql/data pgvector/pgvector:pg17 -c fsync=off
     with TEST_DATABASE_URL=postgresql://postgres:test@127.0.0.1:55432/postgres.

   5.4 Data copy and verification

   Use one tool for both directions: backend/src/scripts/tools/copy_province_db.py.

   copy_province_db.py to-postgres   --sqlite FILE --database-url URL [--verify-only]
   copy_province_db.py to-sqlite     --database-url URL --sqlite NEWFILE
   

   1. Source checks: open SQLite with mode=ro, text_factory=bytes. Run PRAGMA integrity_check and
      PRAGMA foreign_key_check; abort on any problem.
   2. Target checks: the target must be at the latest migration with every table empty.
   3. Legacy tables: list any tables that are not in the schema (the live files' legacy tables) and skip
      them unless --archive-legacy DIR writes them out as JSON.
   4. Copy: go table by table in foreign key order (codes before submissions/sessions/drink_submissions;
      users before user_sessions/preview_sign_in_tickets), using an explicit column list.
      • Check every value against the target type:
        • typeof() must be integer for bigint columns; integer or real for double precision; text for text.
        • Text must be strict UTF-8 with no NUL bytes.
      • Report every mismatch with table, rowid and column, and abort.
      • Load with cursor.copy("COPY t (cols) FROM STDIN").
   5. Sequences: set each identity sequence to max(max(id), sqlite_sequence.seq), so AUTOINCREMENT's
      never-reuse rule holds (it matters for admin_audit and codes).
   6. Verify: compare row counts, then a SHA-256 per table over rows ordered by primary key, with values
      normalised (NULL marker, int as decimal, float as repr, text as UTF-8). Compute it the same way on
      both sides in Python and print a table. Exit non-zero on any difference. Run ANALYZE at the end.
   7. CI round trip: fill a SQLite database through the normal code paths (or a fixture covering every
      table), copy to Postgres and back, and require identical checksums.

   5.5 Cutover and rollback per site

   Do Dev first, soak it for at least a few days with previews running from it, then www.

   Before cutover

   • The deployed code is the dual-engine build, and the Postgres CI job has been required for a while.
   • The database server is running, with an empty migrated database, roles and a host-only DATABASE_URL ready but not yet active.
   • Host backup jobs are updated.

   Cutover

   1. Stop writes. ps-compose <target> stop backend. The plugins' pushes (roster, LuckPerms bridge, TFMCWeb) fail during the window, so check that each retries. Patreon and Discord loops stop with the backend.
   2. Take the rollback point. sqlite3 .backup of province.db to province.db.pre-postgres-<time>.
   3. Copy. Run copy_province_db.py to-postgres in a one-off container from the deployed image (ps-compose <target> run --rm backend …). It verifies as it goes.
   4. Switch. Set DATABASE_URL in the override, backing the file up first. Start the backend.
   5. Smoke test.
      • site-deploy's health URLs
      • sign in and open Profile, Characters and the wardrobe
      • redeem a skin code
      • Staff panel: Players, Ranks (LuckPerms poll), Database studio
      • map ledger and chronicle index
      • Patreon sync log line
      • backend logs for psycopg errors.
   6. Keep the old file. Keep province.db read-only for 30 days; nothing reads it any more.

   The downtime is a few minutes; the copy itself takes seconds at 7 MB.

   Rollback

   • Soon after cutover, no writes worth keeping: unset DATABASE_URL and restart. The site is back on the SQLite file as of step 2.
   • Writes must be kept: stop the backend, run copy_province_db.py to-sqlite into a fresh file that migrate() has created, check the verification, swap the file in, unset DATABASE_URL and start.
   • Keep SQLite support in the code until www has run on Postgres for at least one full release cycle.

   ---

   6. Staged PRs

   Each PR leaves both sites working on SQLite until the cutover steps. Docs PRs go to the Docs repo and are
   linked from the code PR.

   #	PR	Main files	Risk	Verify
   1	Portable SQL on SQLite. RETURNING id for the 10 lastrowid sites; ON CONFLICT for INSERT OR IGNORE/REPLACE (including schema.sql:477); qualify ledger/store.py:379-395 and replace scalar MIN/MAX; lower() for COLLATE NOCASE; Python-side json_extract; drop runtime PRAGMA table_info checks and the audit.py:157 fallback; positional params in codes.py:197-217; NULLS FIRST where needed.	modules listed in section 3	Low. Behaviour unchanged; RETURNING needs SQLite 3.35+.	Full pytest on SQLite; check sqlite3.sqlite_version in the image and CI.
   2	Database package, SQLite only. src/database/ with connect, migrate, Row, error tuples; skins/db.py re-exports; sqlite3.Row hints and sqlite3 exception catches switched to database.*.	backend/src/database/*, skins/db.py, submissions.py, wills.py, estates.py, discord_names.py	Low.	Full suite unchanged.
   3	Postgres backend in the package. Pool, translator, row factory, bool dumper, advisory writer lock, timeouts; psycopg-pool pinned. Off unless DATABASE_URL is set.	database/postgres.py, translate.py, requirements.txt	Low (inactive).	Translator unit tests; wrapper tests against a CI Postgres service (introduced here, one test file).
   4	Postgres migrations. Runner, 0001_baseline.sql, triggers, schema parity test.	database/migrations/*, database/__init__.py	Low (inactive).	Migrate twice is idempotent; parity with schema.sql; admin_audit refuses UPDATE, DELETE and TRUNCATE.
   5	Test helper and CI matrix. database/testing.py; convert the 8 conftests and ~30 unittest classes; mark SQLite-only tests; build.yml gets the postgres service and db matrix, with the Postgres leg continue-on-error.	backend/conftest.py, src/*/conftest.py, ~50 test files, .github/workflows/build.yml	Medium (test churn).	SQLite leg green with the same test count; the Postgres leg's failure list drives PRs 6-10.
   6	Accounts and linking on Postgres.	auth/*, skins/discord_link.py, api/admin_routes.py, api/database_routes.py	Medium. Sign-in, view-as and sessions.	Postgres leg green for auth/, api/test_*auth*.
   7	Skins, drinks, codes, moderation, wars.	skins/*, wars/declare_codes.py	Medium.	Postgres leg green for these packages; scripts/skins_e2e_smoke.py against a Postgres stack.
   8	Characters and wardrobe.	characters/*	Medium. Plugin ingest paths.	Postgres leg green; character_ingest_smoke.py, lore_items_smoke.py.
   9	Patreon, LuckPerms, diagnostics. Audit read-before-write transactions; check the LuckPerms snapshot time with a large payload (thousands of per-user statements; use pipeline mode if slow).	patreon/*, luckperms/*, diagnostic/*	Medium-high (money and permissions).	Postgres leg green; time a 2,000-user snapshot apply.
   10	Chronicle and ledger. Engine branch for the temp table; check that ingest promotion stays inside its time budget.	scripts/chronicle/*, scripts/ledger/*, scripts/maps/archive_copy.py, tools/generate_spoof_ledger.py, api/ledger_routes.py	Medium.	Postgres leg green; time /ledger/series and a day promotion with a 2,000-faction payload. Then make the Postgres leg required.
   11	Database studio on Postgres. site source follows DATABASE_URL; studio role; tests for both engines.	dbstudio/studio.py, api/database_routes.py, tests; Docs database-studio.md	Medium (root write access).	Attempts to change admin_audit, setval, DROP, TRUNCATE and ALTER all refused under the studio role.
   12	Copy tool. Both directions, verification, CI round trip.	scripts/tools/copy_province_db.py + test	Medium.	Round trip equal; run --verify-only against a copy of Dev's file made on the host.
   13	Host plumbing. db services in both compose files (pg17, builtin C.UTF-8 locale, healthcheck, volume); site-deploy.sh pg_dump branch; ps-preview.sh per-preview database and role, scrub, drop on reap; admin_cli --database-url; staging import tool updated or retired.	docker-compose*.yml, scripts/site-deploy.sh, scripts/ps-preview.sh, scripts/staging-import-prod-data.sh, scripts/retag-realm.py, auth/admin_cli.py; Docs DEPLOY.md	High (root-installed scripts).	Both branches tested on Dev; reinstall /usr/local/sbin copies; preview up/down twice; deploy once with a backup.
   —	Cutover Dev (ops, section 5.5).	host override	High.	Smoke list; previews come up from province_dev.
   —	Cutover www (ops, on request).	host override	High.	Smoke list; first site-deploy produces a .dump.
   14	Remove SQLite for province.db. Drop schema.sql, _upgrade, the SQLite backend, SQLite-only tests and the matrix. Optional codemod ? → %s, then delete the translator. CoreProtect stays on SQLite.	skins/db.py, database/*, tests, build.yml; Docs architecture, overview, ledger, skins, tfmcweb, auth-security, patreon-backend, workspace AGENTS.md	Low-medium.	Full suite on Postgres.
   15	psycopg 3 for Precedent and patch notes.	precedent/db.py, patchnotes/db.py, api/precedent_routes.py, api/patchnotes_routes.py, scripts/*precedent*.py, requirements.txt	Medium (with conn closes in psycopg 3).	Their mocked tests updated, plus one real Postgres test each; patch note jobs updated on the host.

   Follow-ups, outside this plan

   • About 20 API modules have async def handlers that call synchronous database code on the event loop:

     • characters_routes.py:218-287
     • skins_routes.py:573
     • luckperms_routes.py, patreon_routes.py, admin_routes.py, diagnostic_routes.py, drinks_routes.py, data_routes.py, chronicle_routes.py.

     Network round trips make those stalls longer than on SQLite. Move the database parts into run_in_threadpool, as chronicle_staff_routes.py and view_as_guard.py already do.

   • Typed columns: timestamptz for the *_at text columns once the formats are normalised, boolean for 0/1 flags, jsonb for *_json/payload/manifest.

   • Replace the global writer lock with row locks area by area, if write throughput ever matters.
2. ryanbarlow97 commented on Oct 16, 2026

   

   ryanbarlow97

   on Oct 16, 2026

   ContributorAuthor

   More actions

   Change of approach (2026-10-16, decided by Ryan): no compatibility layer. The site moves to native Postgres (psycopg 3, %s, real types, row-level locking for each read-check-write path) on the integration branch postgres. Area PRs target postgres. When everything is converted, postgres merges to main, Dev cuts over with a copy tool, then www on request.

   Freeze: while postgres is open, please avoid changing province.db queries or schema on main. Anything that must land there has to be ported into postgres too. The steps table above is superseded; the foundation PR posts the revised native plan here.

   🤖 Generated with Claude Code
3. ryanbarlow97 commented on Oct 16, 2026

   

   ryanbarlow97

   on Oct 16, 2026

   Last edited by ryanbarlow97

   ContributorAuthor

   More actions

   Revised plan: native Postgres, no compatibility layer

   The site moves to native Postgres in one go. This replaces sections 5 and 6 of the plan above: there is no ? translator, no sqlite3-style row class and no dual-engine period. Work happens on the integration branch postgres; area PRs target it, and it is never deployed until everything is converted. When it is complete, postgres merges to main, which deploys Dev; www follows through Actions → Deploy. Mixed states inside the branch are fine, but postgres must be green before it merges to main.

   1. Foundation (#252)

   backend/src/database/ gives a pooled psycopg 3 connect() (one transaction per block, dict rows, search_path=public, UTC, lock and statement timeouts, and every connection reset before reuse), transaction(isolation=, read_only=, lock=), advisory_lock() (waits up to 20 s) and serializable() with retry. A migration runner applies numbered SQL files under an advisory lock at start-up and from python -m src.database.migrate. It refuses histories that disagree with the files: a changed, missing or out-of-order file. Seed rows (patreon_sync_state) are idempotent inserts in seeds.sql, applied at start-up after the migrations, so a freshly migrated database has every table empty. 0001_baseline.sql creates all 61 tables with real types: timestamptz, boolean and jsonb, identity ids that accept explicit values, and COLLATE "C" on every text column so ordering matches SQLite whatever the database's default. admin_audit refuses UPDATE, DELETE and TRUNCATE by trigger. Pytest fixtures run against a Postgres from TEST_DATABASE_URL, and CI has a pgvector/pgvector:pg17 service. The PR body lists every column whose Python value changes, the code sites the type change breaks, and a recommended Postgres pattern for each of the 42 BEGIN IMMEDIATE paths.

   Province tables share each site's existing provincesystem database with Precedent and patch notes, through the same DATABASE_URL.

   2. Area PRs, into postgres

   Each PR moves one area to src.database: %s placeholders, RETURNING for ids, ON CONFLICT for INSERT OR …, the new Python types, aliases for duplicate column names in joins (dict_row keeps the last, sqlite3.Row kept the first), the locking pattern from the foundation PR for its read-check-write paths, and its tests on the postgres fixture. SQLite-only tests (legacy upgrades, iterdump, PRAGMA) are deleted with the code they test.

   1. Accounts: auth/* including audit.record and session_user_in, api/admin_routes.py and api/database_routes.py's audit read. This goes first, because most areas call audit.record(conn, …) or session_user_in(conn, …) with their own connection.
      • session_user_in(conn, token, lock=True) takes FOR SHARE OF s, u for staff re-checks only.
      • Every transaction that calls drop_view_leftovers opens with transaction(lock="auth:placeholders").
   2. Linking: skins/discord_link.py, auth/microsoft_link.py.
   3. Skins, drinks and codes: skins/*, wars/declare_codes.py.
      • The skin and drink submission inserts lock the code row FOR UPDATE and re-validate it in the same transaction.
      • Both mint paths (start_site_session and TFMCWeb's issue_code) take cosmetic_mint:<uuid>.
   4. Characters: characters/* (roster, estates, wills, wardrobe, lore items, creates).
   5. LuckPerms and diagnostics: luckperms/*, diagnostic/*. Time a 2,000-user snapshot apply.
      • Lock order in every transaction: lp_state first, then users and sessions, then lp_changes.
      • queue_change takes lp_state FOR SHARE for one consistent mirror.
      • record_results locks lp_state before settling changes.
      • _current_actor reads the actor row FOR SHARE.
   6. Patreon: patreon/*, with the patreon advisory lock around _compute/_plan.
   7. Map chronicle and ledger: scripts/chronicle/*, scripts/ledger/*, scripts/maps/archive_copy.py, scripts/tools/generate_spoof_ledger.py, api/ledger_routes.py. The temp table becomes <> ALL(%s). Time a day promotion with 2,000 factions.
   8. Database studio: the site source runs on Postgres under a province_studio role that owns nothing, can only SELECT from admin_audit and has no UPDATE on sequences. The SQLite authorizer goes.
   9. Scripts and tools: backend/scripts/*_smoke.py, auth/admin_cli.py --database-url, scripts/retag-realm.py, and the staging import, which is retired if previews have replaced it.
   10. Remove SQLite for site data: schema.sql, skins/db.connect()/migrate()/_upgrade(), and the DB_PATH test patching. DATA_DIR and the file directories stay; CoreProtect stays on SQLite.
   11. Optional: move Precedent and patch notes from psycopg2 to psycopg 3 and drop psycopg2-binary.

   Where a callee takes the caller's connection (audit.record, session_user_in, characters/estates, scripts/ledger/store.open_connection), callers in areas not yet converted will fail until their PR lands. That is expected on the branch.

   3. Copy tool

   backend/src/scripts/tools/copy_province_db.py:

   copy_province_db.py to-postgres --sqlite FILE --database-url URL [--verify-only] [--archive-legacy DIR]
   copy_province_db.py to-sqlite   --database-url URL --sqlite NEWFILE   # rollback that keeps writes
   

   • Source checks: open the file read-only and run PRAGMA integrity_check and foreign_key_check.
   • Target checks: the target must be at the latest migration with every province table empty. The tool migrates without seeding, so this holds, and SQLite's patreon_sync_state row is copied in; the backend's start-up seed is then a no-op.
   • Legacy tables (armourshop_player_meta, drink_player_meta, player_keys, and anything else outside the schema) are listed and skipped, or written out as JSON with --archive-legacy.
   • Conversion, checked value by value, with any failure reported by table, rowid and column before anything is written:
     • times: accept the three stored formats (…Z, milliseconds …Z, isoformat() +00:00 with or without microseconds) and nothing else
     • flags: 0/1 only
     • JSON columns: must parse, refusing the NaN, Infinity and -Infinity that Python's json.loads accepts and Postgres rejects
     • text: strict UTF-8 without NUL
     • integers within bigint
   • Precision: timestamps with more than six fractional digits (ledger captured_at, first_seen_at, last_seen_at and deleted_at carry Java's nanoseconds) round to microseconds in timestamptz. The tool reports how many rows rounded, and verification compares times at microsecond precision.
   • Constraints SQLite never had: check before writing anything.
     • Report duplicate open patreon_alerts per kind.
     • Report more than one pending will_requests row per (realm_id, character_id).
     • Report more than one pending/sent lp_changes row per (target_type, target).
     • Keep the newest microsoft_link_states row per user (they expire in minutes).
     • Report discord_links.player_uuid values that are not lower case.
   • Load table by table in foreign key order with COPY, then setval each identity to max(max(id), sqlite_sequence.seq), so AUTOINCREMENT's never-reuse rule holds.
   • Verify: compare row counts and a SHA-256 per table over rows in primary key order. Both sides are normalised the same way (time as UTC microseconds, JSON as sort_keys compact, booleans as 0/1), the result is printed as a table, and any difference exits non-zero. Run ANALYZE at the end.
   • CI: fill a SQLite database through schema.sql with every format seen live, then round-trip it to Postgres and back.

   4. Host plumbing

   • Databases: each site already has its db service (pg17, provincesystem database). The province tables go in it, and site-deploy's existing pg_dump (feat(db): run Precedent and patch notes on each site's own Postgres #250) already backs them up.

   • site-deploy: a one-time copy hook. If the checked-out code has src/database/migrations, the site's Postgres has no province rows and province.db exists, then before starting the new backend:

     • stop the old one
     • take the usual SQLite backup
     • run migrations and copy_province_db.py to-postgres in a one-off container from the new image
     • only then start it.

     A copy or verification failure aborts the deploy with the old code still running. After cutover the SQLite backup step is dropped. The root-owned /usr/local/sbin/site-deploy must be reinstalled.

   • Previews get their own database on staging's db:

     • CREATE ROLE pv_<slug> and CREATE DATABASE pv_<slug> OWNER pv_<slug>
     • REVOKE CONNECT … FROM PUBLIC
     • pg_dump -Fc of Dev's provincesystem, excluding the precedent_* and patchnote_* tables, piped to pg_restore --no-owner --role=pv_<slug>
     • the same scrub as today (sessions, user_sessions, discord_oauth_states, discord_link_codes; consider patreon_tokens too)
     • DATABASE_URL and DATABASE_POOL_MAX_SIZE=3 in the generated compose file.

     do_down and reap add DROP DATABASE … WITH (FORCE) and DROP ROLE. Staging's db joins the ps-previews network. CREATE DATABASE … TEMPLATE will not work while Dev is connected. Previews have no DATABASE_URL today, so they skip the Postgres migration until this lands.

   • Pool size: www 10 (default), Dev 10, previews 3; under Postgres's default max_connections of 100.

   • Backups outside the repo: any host job that copies province.db needs the pg_dump equivalent. Check the host's timers before cutover.

   5. Cutover

   1. Before: postgres green, including the copy tool's round trip. Run copy_province_db.py to-postgres --verify-only against fresh host copies of Dev's and www's province.db, and fix any rows it reports.

   2. Dev: merge postgres into main. Build passes and Deploy runs site-deploy dev, whose copy hook moves the data. Then smoke test:

      • sign-in, Profile, Characters, wardrobe
      • redeem a skin code
      • Staff panel: Players, Ranks, Database
      • map ledger and chronicle
      • the Patreon sync log line
      • the backend log for psycopg errors
      • one preview from Dev's data.

      Soak for a few days.

   3. www: on request, Actions → Deploy with site www, and the same smoke test.

   4. Rollback:

      • Before any writes worth keeping: deploy the last pre-Postgres commit. province.db is untouched from the moment of the copy.
      • To keep writes: stop the backend, run copy_province_db.py to-sqlite into a fresh file, swap it in, then deploy the old commit.

      Keep province.db read-only on the host for 30 days.

   5. Docs (Docs repo, with the cutover PR):

      • architecture, overview, DEPLOY.md (backups, previews), database studio, ledger, skins, tfmcweb, auth-security, Patreon backend
      • workspace AGENTS.md ("backs up province.db").
4. ryanbarlow97

   added 9 commits that reference this issue on Oct 16, 2026

   feat(db): native Postgres foundation (database package, schema, tests…

   ...

   b5e161e

   feat(db): accounts on native Postgres — #251 (#253)

   ...

   7d2a1ee

   feat(db): chronicle and ledger on native Postgres — #251

   ...

   4eb2265

   feat(db): chronicle and ledger on native Postgres — #251 (#254)

   ...

   9a3835b

   feat(db): Patreon, LuckPerms and diagnostics on native Postgres — #251 (

   ...

   8cb27b4

   feat(db): linking, skins, drinks and codes on native Postgres — #251 (#…

   ...

   51ced2a

   feat(db): characters on native Postgres — #251 (#255)

   ...

   166341d

   feat(db): database studio on a restricted Postgres login — #251 (#256)

   ...

   e79c6fb

   Move ProvinceSystem runtime to native PostgreSQL (#263)

   ...

   85ab01c
5. ryanbarlow97

   closed this as completedin #270on Oct 17, 2026