Milestone target: the freeze window opens 2026-08-24T15:00:00Z and the migration runs at 2026-08-26T15:00:00Z (both 18:00 EEST) — announced to guild owners, so this is now a public commitment, not an internal aim. See Pre-cutover comms for who was told what. Depends on: nothing blocking — the M1 foundation pattern and the M4-established bot/grant-token conventions this milestone originally depended on have both already shipped and been reused (see below). Live production impact: yes, and unlike M4 this one includes a real historical-data migration.
M5 was redesigned 2026-07-22 (from a straight DM-based port into a ticket/private-thread system — rationale below) and, as of that redesign, fully implemented: schema, API, dashboard, and bot, plus two follow-on efforts built on top of it — the thread-history dashboard view (#261) and custom instances + DM mode (#216). None of that is a plan anymore; it's documented as current-state architecture in 01-architecture.md §6a (base ticket system), §7 (thread history), and §8 (custom instances/DM mode). This doc is no longer a design spec — it's scoped down to the one thing still outstanding: migrating real historical thread data out of legacy ChatSift/ModMail and cutting the public deployment over to services/modmail-bot.
Why this hasn't happened yet, despite services/modmail-bot already being live: custom instances (#216) put the new bot into real production use back on 2026-07-30, but only for partner guilds — those are guilds with no prior history, so there was nothing to migrate. The public deployment is the harder case: it's the one legacy ChatSift/ModMail still serves today, with years of real thread history that has to survive the swap intact. That's the entire remaining scope of this milestone.
Direct quote from the design discussion (2026-07-22), owner's framing:
"I think we should kill DMing the bot, it's pretty terrible lmao. My idea is to have staff teams set up a prompt just like AMA, and as the user you click the button and get a private thread within the server anyway where you type, and on the mod end it looks the exact same. Its totally ok because only server admins can technically peek into that private thread, so it's still very privacy isolated. One 'downside' is it's potentially messier to allow the user to revisit conversations with the mod team — maybe you want to completely nuke their perspective sometime after the thread is closed. I'd imagine more people are more on that end. I guess in a sense this makes it more of a ticket bot than a modmail, but I think this is the superior model anyway."
The mod side didn't change. A ticket still lands as a post in the mod-side forum with a running message history, exactly like the old Thread/ThreadMessage model. Only the user-facing origin changed: an in-server private thread (or, for a custom instance opted into DM mode, still a DM — see 01-architecture.md §8) instead of every guild being DM-only. This is why the old schema below is still the migration source for the public deployment, not a from-scratch design — the new schema is the old one plus a handful of new concepts (categories, panels, an origin distinction) layered on top. Full design-outline/build narrative for the create-flow, the mention/user-ID QOL feature, and the schema-authoring decisions lives in git history (this doc's pre-2026-07-30 revisions) and in closed issue #152, not here — 01-architecture.md §6a has the shipped shape.
From legacy ChatSift/ModMail's prisma/schema.prisma (captured 2026-07-16):
model GuildSettings {
guildId String @id
modmailChannelId String?
greetingMessage String?
farewellMessage String?
simpleMode Boolean @default(false)
alertRoleId String?
}
model SnippetUpdates {
snippetUpdateId Int @id @default(autoincrement())
snippetId Int
snippet Snippet @relation(fields: [snippetId], references: [snippetId], onDelete: Cascade)
updatedAt DateTime @default(now())
updatedBy String
oldContent String
}
model Snippet {
snippetId Int @id @default(autoincrement())
guildId String
commandId String
createdById String
name String
content String
timesUsed Int @default(0)
lastUsedAt DateTime? @db.Timestamptz()
createdAt DateTime @default(now()) @db.Timestamptz()
lastUpdatedAt DateTime @updatedAt @db.Timestamptz()
updates SnippetUpdates[]
@@unique([guildId, name])
}
model ScheduledThreadClose {
threadId Int @id
thread Thread @relation(fields: [threadId], references: [threadId], onDelete: Cascade)
scheduledById String
silent Boolean @default(false)
closeAt DateTime
}
model ThreadMessage {
threadMessageId Int @id @default(autoincrement())
localThreadMessageId Int
guildId String
threadId Int
thread Thread @relation(fields: [threadId], references: [threadId], onDelete: Cascade)
userId String
userMessageId String
staffId String?
guildMessageId String
anon Boolean @default(false)
@@unique([threadId, localThreadMessageId])
}
model Thread {
threadId Int @id @default(autoincrement())
guildId String
channelId String
userId String
createdById String
createdAt DateTime @default(now()) @db.Timestamptz()
closedById String?
closedAt DateTime? @db.Timestamptz()
scheduledClose ScheduledThreadClose?
lastLocalThreadMessageId Int @default(0)
messages ThreadMessage[]
alerts ThreadReplyAlert[]
}
model Block {
userId String
guildId String
expiresAt DateTime?
@@id([userId, guildId])
}
model ThreadOpenAlert {
guildId String
userId String
@@id([guildId, userId])
}
model ThreadReplyAlert {
threadId Int
thread Thread @relation(fields: [threadId], references: [threadId], onDelete: Cascade)
userId String
@@id([threadId, userId])
}Because the mod-side model didn't change, this is a close-to-1:1 mapping, not the divergent-schema situation AMA was in (05-migration-cutover.md) — see 01-architecture.md §6a for the actual current column names:
Thread→threads:channelId→mod_thread_id(rename only).category_idanduser_channel_idare bothNULLon every migrated row — correct, since a legacy thread has no category concept and its DM channel isn't something the new bot should ever lock/nuke (it was never a private thread it created).origindefaults to'panel'at the column level, but a migrated row is written as'dm'since that's what it historically was. (There is no #216 precedent to copy here, despite what §8 used to imply — that migration added the column with a plain default and never backfilled anything, because there were no rows to backfill.) This is load-bearing, not cosmetic:lib/preventThreadArchive.tsselectsWHERE closed_at IS NULL AND origin != 'dm'and force-stampsclosed_at = now()on any row whosemod_thread_id404s.ThreadMessage→thread_messages: maps 1:1 (userMessageId/guildMessageId/staffId/anonunchanged in shape).is_internal/is_system/deleted_at(added for #261) are allfalse/NULLfor migrated rows — none of that context exists for historical data, and that's an accurate "not recorded" state, not a gap to backfill.Block,Snippet+SnippetUpdates,ScheduledThreadClose,ThreadOpenAlert,ThreadReplyAlert— map 1:1 ontoblocks,snippets+snippet_updates,scheduled_thread_closes,thread_open_alerts,thread_reply_alerts.GuildSettings→guild_settings:greetingMessage→default_greeting_message,farewellMessage/simpleMode/alertRoleIddirect.modmailChannelIdis dropped —mod_forum_idis written asNULLfor every guild, unconditionally (see item 1 under "Remaining scope"). The new mod side requires a Forum for tag-based category routing, legacy's value was a plain text channel, and nothing at the DB or bot layer validates that (onlyservices/api'supdateConfig.tschecksChannelType.GuildForum, which a direct SQL insert bypasses) — so carrying the old id over would produce a config that inserts cleanly and then fails at first ticket creation.NULLleaves the guild visibly unconfigured instead. Picking a real forum is a manual, per-guild admin step on the dashboard afterwards; the script prints the list of affected guilds so it can drive follow-up comms.simple_modecarries over for fidelity but is inert — nothing inservices/modmail-botreads it.- No
thread_message_contentrows are created for migrated messages (content recording, #261, didn't exist historically) — the dashboard thread view already renders a "not recorded" placeholder for exactly this case, so this needs no special handling in the script.
Write a migration script— done (#157):packages/private/db/src/scripts/migrateLegacyModmail.ts, run viayarn migrate:legacy-modmail --source <slug> --dry-run|--live|--verifywithLEGACY_DATABASE_URLpointing at a restored copy of the legacy database. Transforms all 9 legacy tables per the mapping above, preserving relations and timestamps exactly. Integer PKs are regenerated, not preserved — the new database already holds partner-instance rows, so legacy ids would collide; ids come off each table's identity sequence vianextval, which also means nosetval()fixup afterwards. Four things worth knowing before running it:--source <slug>is required, and names the legacy deployment being migrated (nascar,public, …). It is written tothreads.migration_sourceand is what scopes both the re-run guard and every target-side--verifycount. This exists because the publicChatSift/ModMaildeployment is not the only legacy database — partners self-host their own copies (see the NASCAR pilot below), and migrating one must not wedge or miscount another. The script originally identified its own rows by inferringorigin = 'dm' AND user_channel_id IS NULL, which is global: one partner migration would have made every later run, including the public cutover, abort as a "re-run" — and the abort's suggestedDELETEwould have destroyed that partner's freshly migrated history.- It refuses to run while any legacy thread is still open. Force-closing them is a deliberate runbook step (item 3 below), not something the script does silently.
- It refuses to run twice for the same
--source. Thread history is not idempotent —threadshas no unique key to conflict on, so a second pass would duplicate every ticket. Starting over means deleting that source's migrated rows by hand first; the abort message carries the exact statement, scoped to the slug. A different--sourceis deliberately allowed to proceed. mod_forum_idis migrated asNULLfor every guild, deliberately: the legacy value was a text channel and the new mod side needs a Forum. The script prints the list of affected guilds so it can drive follow-up comms.
- Dry-run against a copy of the production legacy
ChatSift/ModMaildatabase; reconcile row counts and spot-check message content/ordering per thread.--dry-runruns the whole migration in a transaction and rolls it back (so every FK/CHECK/unique constraint is genuinely exercised), and--verifyis the read-only reconciler: per-table row counts, per-thread message counts, and full message-set comparison on a random sample. Do the NASCAR pilot first (below) — it exercises the same code path against real history at a fraction of the blast radius, and produces the first honest wall-clock number. - Cutover runbook (mirroring 05-migration-cutover.md's structure, but with a data migration this time):
- Announce a maintenance window — ModMail is more synchronous/user-facing than AMA was, a message sent mid-cutover shouldn't get lost. See Pre-cutover comms for the three channels this goes out on and the one date they all share.
- Freeze the legacy bot (stop accepting new DMs/replies — it's still DM-based right up to cutover) for the migration run.
- Run the migration script against a final snapshot, then
--verify. Record the wall-clock duration of the dry-run (item 2) and budget the maintenance window off that — it's the only honest estimate available, since runtime is dominated by legacy row counts nobody has measured yet. The script setsstatement_timeout/idle_in_transaction_session_timeoutto 0 for its own transaction (it holds one transaction open across reads of the legacy database, so it idles for reasons unrelated to Postgres' own speed), but that only covers server-side limits — a connection killed by something in between, or the operator's own shell timing out, still means restarting the whole run. - Deploy
services/modmail-botas the public deployment, point the token, smoke-test (create a ticket via a panel, reply from staff, close a ticket and confirm the private thread is gone, pull up a migrated legacy thread in the dashboard thread view and confirm its history rendered correctly). - Keep the legacy deployment + database warm for rollback until confidence is established.
Three separate measures, all driven off one instant: 2026-08-24T15:00:00Z (Mon 24 Aug 2026, 18:00 EEST), with the freeze running 48h to 2026-08-26T15:00:00Z. If that date ever moves, all three have to move together — owners and moderators looking at two different dates is worse than either date being wrong.
-
Owner DMs —
scripts/announce-modmail-migration.mjsin this repo. Dependency-free node-builtins script (it runs on a deploy host with no installed workspace, same constraint as the other root scripts), plain RESTPOST /users/@me/channels+POST /channels/:id/messages, Components V2 body. Recipients are a hardcoded 21-entryOWNERSlist sourced from the prod ModMail guild-activity table — filtered by actual bot usage, not member count, so large-but-idle guilds are deliberately absent. Repeated owner ids are folded into one DM naming every guild they own. RequiresMODMAIL_ANNOUNCE_TOKEN(the legacy prod bot's token, so the DM comes from the bot the owner recognises) plus an explicit--test(one recipient — us, labelled as a fake "ChatSift" guild) or--live— there is no default, so a bare invocation can't blast real owners. The date is hardcoded in the script now that it's settled, not passed in;MIGRATION_START_ISOstill overrides it but is rejected without an explicitZ/offset, becauseDatewould otherwise parse it in the runner's local timezone and silently shift every<t:…>the recipients see. Every run prints the resolved window before sending anything:# __PROD_MODMAIL_TOK__ from .env.private -- NOT __PROD_AMA_TOK__, which sits right above it and # would put the announcement in front of owners as a DM from the AMA bot they never installed. MODMAIL_ANNOUNCE_TOKEN=$__PROD_MODMAIL_TOK__ node scripts/announce-modmail-migration.mjs --test # then --live
NASCAR is deliberately absent from both
OWNERSand the test recipient: they're the pilot (below), moving onto their own #216 custom instance in DM mode, so this announcement's copy — public cutover date, panel-configuration requirement — is simply not true for them. -
Legacy bot status — a custom presence on the legacy bot spelling out the date (Discord doesn't render
<t:…>in a presence). Set onClientReadyand re-applied hourly on asetInterval: Discord drops a bot's presence over time and across gateway resumes, so a one-shotsetPresence()(or an IDENTIFY-payload presence) quietly decays to nothing. -
In-thread moderator notice — a yellow notice embed prepended to the starter message of every newly opened legacy thread, ahead of the existing info embed. This is the measure that actually reaches the people running the queue: the owner DMs above reach 21 accounts, and moderators aren't among them. It rides the existing starter message rather than being a follow-up post, so it's genuinely the first thing in the thread and costs no extra API call; the starter message's
content(member mention + alert-role ping) is untouched, so it adds no second ping.
(2) and (3) live in legacy ChatSift/ModMail, not in this repo — they only make sense on the bot people are using today. Branch feat/migration-notices there: a disposable packages/bot/src/util/migrationNotice.ts holding the timestamps, the status text and the embed builder, wired into events/ready.ts and util/handleThreadManagement.ts. Everything is hardcoded rather than plumbed through that repo's struct/Env.ts on purpose — it has a known expiry date. Merging to main there triggers .github/workflows/deploy.yml, which builds and pushes chatsift/modmail:latest; the prod stack then needs a pull + restart to pick it up. Deploy well ahead of the 24th, and delete both call sites after cutover (or just decommission the legacy deployment, #159).
NASCAR self-hosts its own deployment of legacy ChatSift/ModMail (VPS checkout /home/deploys/repos/nascar-modmail) against its own Postgres. That makes it the rehearsal this milestone has been missing: a real legacy database with real history, small enough that a bad outcome is recoverable. Their new home is the canary/main deployment (/home/deploys/repos/canary); the prod branch runs no modmail-bot at all and folds into canary on 2026-08-13 (prod-branch.md), so this was never a real choice. Afterwards they get a #216 custom instance in DM mode, so their users' flow matches what they have today.
Measured by dry-run 2026-08-11 (guild 877239953174691910): 271 threads, 1289 messages, 1 guild_settings row once the dead test guild below is removed — and zero snippets, snippet-updates, blocks, thread-open-alerts, thread-reply-alerts and scheduled-closes. Only three of the nine tables carry data; the rest of the script is a no-op for them. So the Snippets resync in step 8 is moot, and threads plus guild_settings are the only roots a rollback would have to touch.
The migration itself takes ~1 second (2.8s wall-clock, of which 1.75s was the turbo build). The maintenance window is sized entirely by the human steps — dump, restore, test-guild delete, instance onboarding, forum config — not by the migration. Do not budget a long freeze on its account.
0. Confirm their legacy schema hasn't drifted. Every query in the script uses quoted camelCase legacy column names, so drift from the schema captured above is a hard mid-run failure. pg_dump --schema-only their database and diff the nine tables against "Old schema" before anything else. A tenth table in the dump is Prisma's own _prisma_migrations ledger, which the script never reads.
1. Restore a copy, then drop the dead test guild. Point LEGACY_DATABASE_URL at a restored copy, never their live database — the script holds one transaction open across reads of the legacy side. Restoring it into canary's own Postgres as a separate database (nascar_trial) keeps everything in one container with no cross-stack networking.
Their database holds two guilds, not one, and every migrate* function copies its table wholesale — so the second one rides along into canary unless it's removed first. It was confirmed dead on 2026-08-11 (GET /guilds/:id under their bot token 404s: the bot isn't in it anymore). Delete it from the copy rather than filtering in the script — same effect, no code change, and it re-applies unchanged to each fresh dump:
BEGIN;
DELETE FROM "Thread" WHERE "guildId" = '<test guild id>'; -- cascades messages/alerts/closes
DELETE FROM "GuildSettings" WHERE "guildId" = '<test guild id>';
DELETE FROM "Block" WHERE "guildId" = '<test guild id>';
DELETE FROM "ThreadOpenAlert" WHERE "guildId" = '<test guild id>';
DELETE FROM "Snippet" WHERE "guildId" = '<test guild id>';
COMMIT;Every fresh dump contains it again — re-run this before each migration attempt, and re-take the baseline counts afterwards so they match what the script will actually read. Confirm GuildSettings is down to 1 before continuing.
2. Freeze. Their legacy bot is DM-based; stop it accepting new threads/replies for the window.
3. Force-close open threads. Preflight refuses to run otherwise, deliberately. For a rehearsal ahead of the freeze, do it in the copy (UPDATE "Thread" SET "closedAt" = now(), "closedById" = '<id>' WHERE "closedAt" IS NULL) — that is itself a faithful rehearsal of the real freeze step.
4. Dry-run, and record wall-clock — this is the number that sizes the public window (item 3 above):
IS_PRODUCTION=false LEGACY_DATABASE_URL=<copy> yarn migrate:legacy-modmail --source nascar --dry-runIS_PRODUCTION=false is required when running from the VPS host shell, and is not a mistake: with it true, resolveTargetUrl() picks DATABASE_URL_PROD, whose postgres hostname only resolves inside the compose network. Both URLs reach the same physical database on canary — DATABASE_URL_DEV just goes via the host-published port. Get that port from ./compose port postgres 5432, not from .env.private (canary doesn't set LOCAL_DATABASE_PORT there), and confirm it reaches the right database (SELECT * FROM modmail_instances) before trusting it for a --live run.
5. Live, then verify:
LEGACY_DATABASE_URL=<copy> yarn migrate:legacy-modmail --source nascar --live
LEGACY_DATABASE_URL=<copy> yarn migrate:legacy-modmail --source nascar --verifyExpect their guild in the "needs a forum" list — mod_forum_id migrates as NULL by design.
6. Onboard the #216 instance per workflow.md, slug nascar. Do this after step 5: preflight warns when a legacy guild already has a modmail_instances row, and while that warning is harmless here, it's one you'd want to take seriously during the public run rather than learn to ignore.
7. Enable DM mode (guild_settings.dm_mode = true) so the user-facing flow matches legacy. The API rejects this unless the instance row exists, so it must follow step 6.
8. Two manual repairs, neither automatic:
- Pick a Forum. Their admin sets
mod_forum_idon the dashboard. Until then ModMail does not work for them. - Resync snippets. Migrated
snippets.command_idvalues belong to their legacy application and 404 under the new one — press the snippets resync button (§8). Panels resync is not needed; no panels existed on legacy.
9. Keep their legacy deployment and database warm for rollback until confidence is established, and archive the dump offsite — the same single dump that fed the migration, taken before the test-guild delete and the force-close, so the archive is pristine legacy state and provably identical to what was imported. Checksum it across the transfer and test-restore it once; the VPS surviving is an assumption a rollback plan shouldn't make. The file holds Discord IDs, timestamps and guild config strings — the legacy schema stores no message content — so it is personal data but not a conversation archive.
Everything covered by 01-architecture.md §6a/§7/§8 was already verified feature-by-feature as each piece shipped (2026-07-16 through 2026-07-30) — see closed issues #152, #261, #216 for the phase-by-phase acceptance checks, not repeated here. What's still unverified is exactly the "Remaining scope" above: the migration dry-run's row-count/content reconciliation, and the live cutover's smoke test.
The --source change itself was verified 2026-08-11 against two throwaway legacy-schema databases holding disjoint guilds, migrating into a scratch target: source a live + verify clean; source b dry-run not blocked by a's rows (the old global predicate returned 3 there, i.e. it would have aborted); both sources live, then each verifying independently against its own legacy database with 6 threads in the target; a second a live correctly refused, with the printed DELETE removing exactly a's 3 threads and leaving b intact with no orphaned messages.