Skip to content

10-social-port.md

Latest commit

 

History

History
579 lines (521 loc) · 49.2 KB

10-social-port.md

File metadata and controls

579 lines (521 loc) · 49.2 KB
Raw

#343 — Social port (leveling + social interactions)

Tracking issue: #343 (to be created — this doc is referenced from it, not the other way around). Depends on: nothing in flight — M4's AMA cutover (05-migration-cutover.md) and M5's ModMail data migration (06-modmail-port.md) are independent of this and neither blocks nor is blocked by it. Live production impact: none until P6 (cutover) — everything before that is additive: new tables, new service, new routes, new dashboard pages. Legacy ChatSift/Social keeps running untouched the whole time.

Status: done — cut over to the new stack 2026-08-13

All six phases shipped. The legacy ChatSift/Social bot was removed from ChatSift/stack at cutover; its social database on postgres-old is deliberately kept until the AutoModerator port lets the whole legacy stack be torn down at once.

The cutover went without incident. Two things were decided going in and are worth carrying to the next one: the legacy application's token was reused (so no guild had to re-invite), and the migration was run inside the already-running api container rather than from a host checkout — same image, so it has the compiled script and inherits prod IS_PRODUCTION/DATABASE_URL_PROD instead of having them typed by hand.

Reusing the token carries one trap that cost nothing only because it was caught first: bot-core bootstraps /deploy on Ready only when the application has zero global commands (client.ts), so the legacy app's existing globals had to be cleared with a bulk PUT [] before first boot — otherwise /deploy never registers and there is no way to install /level and /leaderboard.

The pre-cutover verification matrix below was deliberately not worked through in full. With NASCAR the only guild that matters for this bot, the call was to invest in diagnosability instead and fix forward — see What to watch in the logs.

10- is the next free roadmap slot. This doc follows the established lifecycle (09-appeals.md explains it): when the phases land, it gets deleted and its durable shape is condensed into a new ## 11. Social bot subsystem section of 01-architecture.md (the next free section number there), with any operator runbook material (cutover steps, interaction-command resync) going to workflow.md.

Owner decisions already made (2026-08-11), recorded so they don't get re-litigated:

  1. Redesign where warranted — parity is the baseline, but known warts are in-scope redesign targets (the ModMail precedent). The Redesign ledger below is the exhaustive list; anything not on it is a straight port.
  2. Social interactions make the cut — both feature clusters (leveling and custom interaction commands) are ported.
  3. Real data migration — users keep their XP and levels. ModMail-style script + --verify + freeze window, not a drain-and-swap (XP is accumulated state; it cannot "drain").
  4. Issue-tracked, no milestone — like appeals (#232), this is a tracking issue + this doc. No calendar commitments; the dates in M4/M5 were public announcements, and nothing here has been announced.

What Social is

ChatSift/Social (separate repo, same situation ChatSift/AMA and ChatSift/ModMail were in) is the leveling bot: users gain XP by sending messages, level up along a configurable curve, and earn role rewards. A second cluster, social interactions, lets guilds define custom slash commands (/hug-style) with templated content.

It runs in production today: stack/docker-compose.social.yml, image chatsift/social:latest, database postgres-old/social, Redis db 1, deployed via that repo's deploy.yml → DockerHub. Last real commit 2025-09-22 (a bug fix); functionally frozen since. It's on the full old stack this monorepo already left behind — Prisma, tsyringe, class-based discord.js framework, yarn 3, and a packages/api that served the old chatsift/dashboard (the API is not part of what gets ported; its route list only informs the new config surface).

Feature catalog (from source, captured 2026-08-11)

Leveling engine (packages/bot/src/events/tracking/messageCreate.ts) — on every guild message from a non-bot:

  • Gate: guild must be configured — GuildSettings row exists with requiredMessages, requiredMessagesTimespan, and xpGain all non-null. Otherwise the bot is fully inert in that guild.
  • Ignores: per-user ignored flag; per-channel ignored flag, resolved against the message channel or its parent category or, for threads, the thread parent's parent — one Channel row can silence/boost a whole category, and threads inherit from their parent channel's config.
  • Eligibility (Redis rolling window): a user must send requiredMessages messages within requiredMessagesTimespan seconds to gain XP once. Implemented with two keys in Redis: leveling_tracking:<guildId>:<userId> (a sorted set of message ids scored by timestamp; trimmed of entries older than 10 minutes, TTL of timespan+5s or 300s) and leveling_ineligible:<guildId>:<userId> (a cooldown key whose TTL is the remainder of the window, computed from the first message's snowflake timestamp, so the window genuinely rolls). requiredMessages <= 1 short-circuits all of this. This logic is subtle, battle-tested, and ports near-verbatim.
  • XP grant: xpGain × (channel multiplier ?? 1) × ∏(role multipliers) — channel multiplier resolved through the same channel→category→thread-grandparent walk; role multipliers are multiplicative across all of the member's configured roles.
  • XP curve: total XP required for level n is requiredXpBase + requiredXpMultiplier · n(n−1)/2 (packages/bot/src/util/calculateLevel.ts — closed form of the triangular-number series, the owner's own derivation, see https://didinele.me/blog/math-journey). Keep this formula exactly: any change silently re-levels every migrated user. calculateUserLevel walks levels upward until XP falls short (O(level), fine at real magnitudes).
  • Role rewards: Reward rows are (roleId, guildId, level, clean). On each message the bot re-derives the member's full role set: all non-clean rewards at or below their level, the highest clean reward only (tiered roles that replace each other), whatever they just earned, plus all their unrelated and managed roles — then calls member.roles.set(). This rebuild-the-world approach is a known wart with a bug-history TODO in the source; see redesign ledger item 2. A failure "bars" the user from role updates for 3 minutes in-memory (the 2025-09-22 fix — previously a single failure barred the entire guild until restart).
  • Level-up notifications: fires when the grant crosses the next level's threshold. Modes None/DM/Channel (message channel first, then a configured fallback channel; a dead fallback auto-nulls itself in the DB). Message is templated ({{ username }}, {{ level }}, {{ guildName }}, {{ earnedRewards }}) with a sensible default.

Commands (all guild-only): /level [user] (level, total XP, progress to next, current/next rewards — the only read surface users have), /config (admin; sets all GuildSettings fields with bounds: required-messages 1–15, timespan 1–60s, xp-gain ≥1, required-xp-base 1–500, required-xp-multiplier 1–100), /channel ignore|unignore|list-ignored|set-multiplier (multiplier 1–10; accepts categories, text, forum, voice, public threads), /role list|set-multiplier, /reward create|delete|list (create is an upsert per role), /interaction create|delete|list.

Social interactions (SocialInteraction model + CommandHandler.handleCommand fallback): /interaction create registers a real per-guild Discord slash command named after the interaction and stores its commandId. When an unrecognized command comes in, the handler looks up (guildId, commandId) and renders the stored content — templated with {{ author }} and {{ targets }} (user-mention options, present when allowTargets), as plain content or an embed (color, attachmentUrl as embed image, plainContent outside the embed), incrementing a uses counter. Delete removes the guild command too. This is ModMail snippets' commandId situation again, and the same lesson applies — see redesign ledger item 3.

Old schema (migration source)

From ChatSift/Social's prisma/schema.prisma (captured 2026-08-11), verbatim:

enum LevelUpNotificationMode {
  None
  DM
  Channel
}

model GuildSettings {
  guildId                              String                  @id
  requiredMessages                     Int?
  // Stored in seconds
  requiredMessagesTimespan             Int?
  xpGain                               Int?
  requiredXpBase                       Int?
  requiredXpMultiplier                 Int?
  levelUpNotificationMode              LevelUpNotificationMode @default(None)
  levelUpNotificationFallbackChannelId String?
  levelUpNotificationMessage           String?
}

// Leveling
model Reward {
  roleId  String
  guildId String
  level   Int
  clean   Boolean @default(false)

  @@id([roleId, guildId])
}

model User {
  userId  String
  guildId String
  // Total XP the user has. Has no regard to level calculations or anything of the sort
  xp      Int     @default(0)
  ignored Boolean @default(false)

  @@id([userId, guildId])
}

model Channel {
  channelId  String
  guildId    String
  ignored    Boolean @default(false)
  multiplier Int?    @default(1)

  @@id([channelId, guildId])
}

model Role {
  roleId     String
  guildId    String
  multiplier Int?   @default(1)

  @@id([roleId, guildId])
}

// Interactions
model SocialInteraction {
  guildId       String
  commandId     String
  name          String
  content       String
  color         String?
  plainContent  String?
  attachmentUrl String?
  uses          Int     @default(0)
  embed         Boolean @default(false)
  allowTargets  Boolean @default(false)

  @@id([guildId, name])
}

Six models, every primary key a natural composite of snowflakes/names — no serial ids anywhere. This makes the migration materially simpler than ModMail's (06-modmail-port.md item 1): nothing to regenerate, no cross-deployment id collisions, and an accidental re-run fails loudly on PK conflicts instead of silently duplicating history. A seventh table in any dump is Prisma's _prisma_migrations ledger; ignore it.

Redesign ledger

The exhaustive "where warranted" list. Each item is a decision, not an open question — revisit only with the owner.

  1. The /config mega-command dies; config is dashboard-first. The new stack's convention (AMA, ModMail) is: config lives on the dashboard, and the shared /dashboard grant-token command (01-architecture.md §4a) gets you there from Discord. The same goes for /channel, /role, /reward, and /interaction write subcommands — they're all config CRUD wearing a slash-command costume, and the old dashboard already proved this surface works as web UI (its API had exactly these CRUD routes and nothing else). What stays in Discord: /level (the product's actual read surface), the interaction commands themselves, and /dashboard.
  2. The roles.set() reward rebuild is replaced with additive diffing. Compute exactly which reward roles to add and which clean-tier roles to remove, and issue only those changes. The legacy rebuild-everything approach has a confessed bug history (the TODO/screenshot hack around clean roles), races with other bots' role changes, and made failure handling so coarse it once barred whole guilds. Behavior parity target: same resulting role state, different mechanism.
  3. Per-interaction guild commands stay, with resync designed in from day one. The UX (a real /hug command with its own name) is the feature and is kept. But stored commandIds belong to an application, and at cutover every one of them 404s under the new bot's application — the exact lesson ModMail snippets taught (01-architecture.md §8, snippets resync). So: command_id is nullable, a resync routine (re-register all of a guild's interactions, update ids) exists as both a cutover step and a dashboard affordance, and dispatch tolerates a stale id by falling back to a (guild_id, name) lookup against the invoked command's name before declaring the interaction missing.
  4. Multipliers stay integers. Parity: channel multipliers 1–10, role multipliers as-is, int columns. Widening to fractional (0.5×) is a real feature request shape but changes XP math on a hot path — out of scope; file it as a follow-up issue if wanted.
  5. Leaderboard: shipped as a follow-up after P4, still not a cutover dependency. The legacy bot has no leaderboard anywhere (/level is the only read), so all of this is new surface rather than ported behaviour — see the Leaderboard entry under Phases for what landed. Nothing about it blocks P5/P6: it reads social_users and adds one nullable-free boolean to social_guild_settings, both of which the migration already writes.

Explicitly not redesigned: the XP curve (frozen for migration fidelity), the Redis eligibility engine (ports near-verbatim, same keys and semantics), notification modes/templating, the channel→category→thread-grandparent resolution walk, and the interaction content/templating model ({{ author }}/{{ targets }}, embed options).

New-stack mapping

Where each piece lands, following the ModMail port's shape (the most recent full-subsystem precedent):

  • Schema → packages/private/db/schema/schema.sql (Atlas declarative) + generated migration + kanel regen. Tables are social_-prefixed: social_guild_settings, social_users, social_channels, social_roles, social_rewards, social_interactions. (AMA's ama_* prefix is the precedent to follow; ModMail's unprefixed guild_settings/threads are grandfathered, not a pattern — and guild_settings is literally taken.) level_up_notification_mode becomes a CHECK-constrained text column or enum per whatever the schema already does for similar unions; legacy's nullable-config gate (settings row exists but required fields null ⇒ bot inert) is preserved as nullable columns, since "row exists, not yet fully configured" is a real state the dashboard flow needs.
  • API → services/api routes on the defineRoute contract pattern, mirroring the ModMail route set's structure (per-guild config CRUD; see git history of #153): settings get/update, channels list/upsert/delete, roles list/upsert/delete, rewards list/upsert/delete, interactions list/create/update/delete (+ resync, ledger item 3). The legacy packages/api is reference-only for surface area; nothing is copied from it.
  • Bot → new services/social-bot on @chatsift/bot-core, scaffolded from services/modmail-bot (bin.ts + index.ts + commands/ + lib/). Registry additions: 'SOCIAL' in BOTS (packages/private/core/src/lib/constants.ts), SOCIAL_BOT_TOKEN env plumbing, and the bot:SOCIAL Redis guild list (packages/private/backend-core/src/lib/data/bots.ts) so the dashboard sees guild presence. Global commands bulk-overwritten like AMA's (never per-guild — except, uniquely here, the per-interaction commands, which are per-guild by design). P3 includes an explicit gateway-intent audit: message tracking needs guild message events (not message content) and member role state; verify what bot-core's client needs rather than copying legacy's intents.
  • Dashboard → apps/website per-guild Social section following the AMA/ModMail config layout: settings form (curve, gains, eligibility window, notification mode/template), channels & roles multiplier/ignore management, rewards editor, interactions editor with create-flow on a dedicated /new page (the #240 convention). The XP-curve form should surface a small level→required-XP preview table computed with the exact formula, since curve mistakes are the config error that hurts most.

Phases

Additive throughout; nothing touches legacy until P6. Each phase ends verified per workflow.md — run the affected service and exercise the change against the test guild, not just build/lint/test.

  • P1 — Schema. Six social_* tables per the mapping above; Atlas migration; kanel regen. Unit-test nothing here beyond what the schema tooling already enforces; the shape gets exercised by P2/P3. Done. social_guild_settings, social_users, social_channels, social_roles, social_rewards, social_interactions + a social_level_up_notification_mode enum, in schema/schema.sql's Social section (migration 20260811185101_add_social_tables.sql). That section's header comment enumerates the four deviations from the legacy schema P5 has to encode — uppercased notification-mode values, NOT NULL multipliers coalescing legacy's NULL to 1, social_interactions' surrogate id + nullable command_id, and guild-first composite PKs. Config bounds are deliberately not CHECKs (legacy only ever enforced them in slash-command option definitions, so prod data isn't guaranteed to satisfy them); they land in P2's zod schemas. The only CHECKs are the ones bad data would genuinely break: required_xp_base/ required_xp_multiplier >= 1 (a 0 in either makes the level walk non-terminating), multipliers >= 1, and social_rewards.level >= 0. Dispatch's (guild_id, command_id) partial index is UNIQUE — two rows sharing a command id would make dispatch pick one nondeterministically — which obliges the P3/P6 resync to clear a guild's command ids before writing the new ones rather than updating row-by-row (a bulk overwrite preserves a command's id by name, so an in-place order exists that transiently collides). No generated types were exported from @chatsift/db's index.ts yet — that file's convention is to add a table the first time a consumer needs it, which is P2.

  • P2 — API. The route set above, with zod validation mirroring legacy's bounds (config bounds listed in the feature catalog — the dashboard inherits them as its validation source of truth). Vitest coverage per the existing route-test patterns. Done, 16 routes under services/api/src/routes/social/: config get/patch, channels & roles & rewards list/upsert/delete, interactions list/create/patch/delete + resync. Schemas are browser-safe and exported as @chatsift/api/social-schemas for P4, with 17 vitest cases pinning the legacy bounds, the enum casing, the full-representation PUT defaults and the zod-v4 .partial()-keeps-defaults trap. Notes for later phases: - SOCIAL is now a real BotId (packages/private/core), which is what lets social routes use apiForGuild/the per-(bot, guild) channel+role caches like every other product. SOCIAL_BOT_TOKEN is a required env var — the API won't boot without it, locally or in prod. - Marketing is decoupled from BOTS: apps/website's marketingBots is keyed by a new MARKETED_BOTS subset, and every public surface (homepage grid, /bot/[name] + its OG image, cross-bot upsells) iterates that instead. Social therefore has a dashboard identity (icon, label, nav tab once installed) with no public page — move it into MARKETED_BOTS at launch. The dashboard's "invite a bot" affordances are filtered the same way, since /invites/social doesn't exist yet. - Resync is shared machinery now: services/api/src/util/commandResync.ts (+ util/resync.ts for the failure shape, moved out of routes/modmail/resyncShared.ts). ModMail's snippet resync was refactored onto it with its wire shape unchanged; Social's differs only in supplying clearCommandIds (the nullable command_id + UNIQUE index need clear-then-write). Applies to canary↔production movement as much as cutover, which is why it's a permanent route rather than a one-off script. - getModmailApplicationId generalized to getBotApplicationId(botId, guildId); the embed-image URL rule moved to util/schemas.ts as httpUrlSchema (was private to modmail's schemas). - Not verified live — the API needs a real SOCIAL_BOT_TOKEN, and there's no Social bot to issue one for until P3. Build/lint/test green; exercising these against Discord happens with P3/P4.

  • P3 — Bot. Scaffold services/social-bot; port the tracking engine (Redis keys and semantics verbatim, keys documented in code); implement additive role-diffing (ledger 2); /level; /dashboard; interaction dispatch + per-guild command registration with resync (ledger 3 — clear-then-write, see P1's note); level-up notifications; intent audit. This is the phase with real behavioral risk — verify XP gain, window cooldown, multiplier stacking, clean-tier promotion, and each notification mode live in the test guild. Done (build/lint/test green; live verification still outstanding — see the Verification section). services/social-bot on @chatsift/bot-core, plus the four infra edits (Dockerfile, docker-compose.yml, root dev:social-bot, bin-rw/@sapphire/async-queue deps). Notes for later phases:

    • Intent audit result: Guilds | GuildMessages, no privileged intents. No MessageContent — the tracker counts messages and never reads their text. No GuildMembers — the acting member and their roles array arrive inline on MESSAGE_CREATE. Same pair legacy ran on, so nothing about the port widens what Discord grants the bot.
    • The XP curve was audited and deliberately left alone. It looks wrong against the derivation it cites (https://didinele.me/blog/math-journey, live copy gone — read it via web.archive.org), and it isn't. That post contains three mutually inconsistent recurrences; the shipped closed form is a correct solution of the one whose terms it actually lists, and the two typos in its intermediate working both vanish before the final formula. What genuinely differs is the post's opening prose, which describes each level costing base + (k-1)m — totalling x*base + m*x(x-1)/2, i.e. base charged once per level where the code charges it exactly once ever. No (base, multiplier) reconciles them (different quadratic families; they meet only at base = 0, which a CHECK forbids), and the two readings aren't even the same knob: here base is a one-time entry cost for level 1, under the prose it would be a permanent per-level surcharge. Adopting the prose would silently reinterpret every guild's configured value and re-level every migrated user. Frozen; the argument lives here and the code (moved to @chatsift/core's socialLevel.ts in P4) points back at it. P4 should label the field "XP to reach level 1" rather than "base".
    • Reward roles are applied with one PATCH on the member, not per-role PUT/DELETE. The per-role endpoints sit in a far tighter per-guild bucket and a tier promotion needs two of them, which saturates it when several members level up together. The diffing in rewards.ts is unchanged — it just produces an absolute role array (everything held, minus superseded tiers, plus what was earned) instead of a call list.
    • Three legacy bugs fixed in passing: the role-multiplier lookup was missing its guild_id filter; channel resolution picked arbitrarily between a channel's own row and its category's (now most-specific-first); and a grant spanning two levels announced only oldLevel + 1 and swallowed the rest (now derives the true level and grants every reward it crossed).
    • Two deliberate behaviour changes: /level no longer upserts a social_users row for whoever it's pointed at, and a non-embed interaction appends its attachment_url for Discord to unfurl rather than re-uploading the image through the bot on every invocation.
    • Interaction target options renamed target/target2/target3 → user/user2/user3 in the API's routes/social/discordBodies.ts, which is a contract with the bot's dispatch renderer. Legacy had five (target1 required); three, all optional, is a deliberate narrowing that also makes a bare /hug valid.
    • Social templates now use ModMail's syntax, via a shared templateString promoted to @chatsift/core. Whitespace inside the braces is tolerated, so a migrated template containing {{name}} starts resolving where legacy rendered it literally. The shared version also fixes a prototype-chain leak both had ({{ constructor }} resolved to Object.prototype.constructor and stringified into the message).
    • Also promoted while here: withQueueLock/withGuildUserLock into @chatsift/bot-core (ModMail's guildUserQueue.ts now re-exports it), and snowflakeTimestampMs + createInflightDeduper into @chatsift/core. Social serializes each guild+user's messages through the lock, closing a double-grant race legacy had.
    • Guild topology (channel parents for the ignore/multiplier walk, role names and guild name for level-up templating) is a redis cache in lib/discordCache.ts, modelled on the shared user cache — lazily fetched, negatively cached on 403/404, in-flight de-duplicated.

  • P4 — Dashboard. The section described above. Verify each form round-trips against the P2 API and that interaction create/resync reflects in Discord. Done (build/lint/test/format green; live verification is the user's, and P3's is still outstanding too). apps/website/src/app/dashboard/[id]/social/ with five sections — config, channels, roles, rewards, interactions — plus api/routes/social.ts (one hook per endpoint), queryKeys.social, the 16 route exports P2 never added to services/api/src/index.ts, and breadcrumb wiring. Notes for P5/P6:

    • The config form models the tracking gate as one switch. required_messages, required_messages_timespan and xp_gain are nullable as a unit (the bot's isConfigured), so an "Enable XP tracking" checkbox writes or nulls all three together. The curve pair is written with them rather than offered separately: tracking-on-with-no-curve is a state the bot tolerates (XP accrues, nobody levels) and nobody wants, and it's exactly what a P5-migrated row can land in. Turning tracking off omits the curve keys instead of nulling them, so a curve someone thought about survives.
    • Two pieces of logic were promoted to @chatsift/core rather than reimplemented in the dashboard, both because a second copy would let the dashboard confidently describe something the bot doesn't do: the XP curve (socialLevel.ts, moved wholesale out of services/social-bot/src/lib/calculateLevel.ts with its test) and the reward-tier rule (socialRewards.ts's resolveEarnedRewards, which computeRewardRoleDiff now sits on top of). DEFAULT_LEVEL_UP_MESSAGE moved too, and switched to the unspaced {{username}} form — identical output, since the shared templateString tolerates both.
    • The interactions page's resync card is alwaysVisible, unlike ModMail's two. Every row P5 migrates lands with command_id IS NULL, so this is the P6 step an ordinary guild performs for itself; interaction cards in that state carry a "Needs resync" badge keyed off the same null.
    • Shared-component changes, both additive: ChannelSelect now makes categories selectable when the caller puts GuildCategory in allowedTypes (Social's channel rows key the whole category→child→thread walk, so picking a category is the feature), and both selects take disabledIds/disabledReason — the channel/role/reward add flows are upserts, so an already-configured entry has to be visibly unpickable rather than a silent overwrite.
    • social_channels/social_roles/social_rewards branded id types are now exported from @chatsift/db's index.ts; without them TypeScript can't name the dashboard's hook return types.
    • Two /level fixes landed alongside (P3 code, owner-reported while reviewing P4): the reward line now names the next reward's actual level rather than only ever describing level + 1 (which read "None" for everyone not one level short of something), and both reward lists are ordered by the guild's role hierarchy. The guild cache gained rolePositions for it — a versioned recipe change, so existing socialguild: entries are discarded rather than misread.

  • Leaderboard (ledger item 5). Not a phase — an additive follow-up built between P4 and P5, on the same "code written, build/lint/test green, live verification outstanding" footing as P3/P4. Four surfaces over one query:

    • Dashboard /dashboard/[id]/social/leaderboard — ranked page of 25 with level and progress-to-next-level, live over socialLeaderboardChannel, plus the public-page switch.
    • Public page /leaderboard/[guildId] — unauthenticated, outside /dashboard (so proxy.ts's OAuth-redirect matcher never sees it), noindex, and rendering the identical payload. Every row goes through the shared toPublicUserInfo (promoted out of AMA's publicAnswers.ts), so no member snowflake reaches it.
    • /leaderboard command — public, everyone-usable, allowed_mentions: { parse: [] }, and rendering members as raw <@id> mentions rather than resolved names: the client renders those as current nicknames at no API cost, which matters because this bot has no member cache and no GuildMembers intent to build one with. Links the public page when the guild has it on.
    • API — GET /v3/guilds/:guildId/social/leaderboard and GET /v3/social/public/:guildId (+ its ws-ticket), both off one buildLeaderboardPage.

    Decisions worth keeping:

    • The public page is addressed by the guild id, not a share token. An unguessable URL would only make it unlisted — the first person it's given to can forward it — and the cost was a parallel identifier plus a guildless realtime channel keyed on a digest of it, since the page mustn't show a viewer a snowflake it already has. One public_leaderboard boolean replaced all of that, and the toggle is the whole control. Off is indistinguishable from "Social was never set up here". The trade accepted: no middle "unlisted" setting, and enabled guilds are enumerable by guild id.
    • Offset/limit paging, diverging from createPaginationQuerySchema's cursor convention. That convention exists for identity-PK lists where an offset drifts under inserts; a leaderboard orders by a mutable xp no cursor could page stably anyway, and rank is offset + n.
    • Page size caps at 50 because each row is one GET /users/{id} against a 30-per-30s bucket on a cold cache. Rows are filtered to xp > 0 AND NOT ignored — the xp > 0 matters for P5, since legacy's /level upserted a row for anyone it was pointed at and those migrate in as zero-XP entries nobody earned.
    • The bot's broadcast is throttled through a redis SET NX EX gate (lib/leaderboardBroadcast.ts), so a busy guild coalesces to one signal per 5s instead of one per XP grant. Leading-edge, so a watcher can sit one grant stale after a burst ends — invisible on a ranking, and the alternative needs a timer owner.
    • The (guild_id, xp DESC) index schema.sql deliberately held back now exists, since something finally reads in rank order.

  • Reward staff notes. Another additive follow-up, not a phase — requested via Tommy relaying a NASCAR moderator ("a description field would be good for staff so we can have notes of like, hey, this reward lets them share links across the server with native automod"). social_rewards.description TEXT (migration 20260812162554_add_social_reward_description.sql), carried through upsertSocialRewardBodySchema and rendered on the rewards cards plus the reward form.

    • Staff-facing only, and that is the load-bearing decision — the owner's call. services/social-bot never reads the column, so /level is unchanged. The reasoning: moderators write these expecting other moderators to read them, so surfacing them to members later would retroactively publish notes written in private. If a member-visible blurb is ever wanted it gets its own column rather than repurposing this one. Recorded at the column in schema.sql, since that's where someone would go looking before wiring it into an embed.
    • .default(null) rather than .optional() in the body schema, keeping the PUT a genuine full representation (an omitted note clears it, exactly as an omitted clean sets it false). '' is rejected by min(1) so "no note" has one representation in the column, not two.
    • Nothing in P5 changes: the legacy schema has no counterpart, migrated rows land with description NULL, and both --verify signature builders already omit it, so they stay in agreement.

  • Highest reward on the leaderboards. Each row on the dashboard and public leaderboards now carries the reward role the member currently holds, rendered as a chip in the role's own Discord colour. The resolution is @chatsift/core's new resolveHighestReward, and the API builds it once per page in routes/social/leaderboard/util.ts — one social_rewards read plus the already-cached fetchGuildRoles, so nothing is per row.

    • The highest reward of either kind, not resolveEarnedRewards(...).tier. clean defaults to false, so a guild that never touches that checkbox has no tier at all and every row would have shown an empty badge. The clean/stacking split describes what the bot takes back off on a promotion, not which role is the member's most impressive one.
    • All reward tie-breaking now breaks on Discord role position, not the lower role id it used to — the owner's call, and applied to resolveEarnedRewards too rather than only the new function, so the bot, the ladder and the leaderboards can't disagree about which of two rewards at the same level wins. Positions are a required argument: the bot passes discordCache.getRolePositions (its existing hour-long redis guild entry, so the grant path pays a cached read), the dashboard passes useGuildInfo's roles, the API passes fetchGuildRoles. An empty map — a guild that can't be read — falls back to the lower role id, which keeps the answer deterministic rather than dependent on each caller's SELECT order.
    • Rewards whose role Discord no longer has are dropped, not drawn. A social_rewards row outlives its role, and nobody wears a deleted one. When the role list can't be fetched at all, the page carries no badges rather than declaring the guild's whole ladder deleted.
    • Not added to the bot's /leaderboard embed — that surface stays as it is.
    • roleColor (Discord's 0 = default grey rule) was about to be copied a third time, so it moved to apps/website/src/utils/util.ts; RoleSelect and RewardLadder now share it.

  • P5 — Migration script. Landed as three files rather than one: scripts/lib/legacySocial.ts holds the legacy-to-new column mapping, and two entrypoints sit on it — scripts/migrateLegacySocial.ts (yarn migrate:legacy-social) and scripts/copyLegacySocialGuild.ts (yarn copy:legacy-social-guild). The split is deliberate: the two have opposite safety models, so neither one's flags belong on the other. The migration touches every guild, never rewrites a guild id and never deletes; the copy wipes its target guild and rewrites every guild id it writes. Sharing the mapping keeps them from drifting apart from each other or from schema.sql.

    • migrateLegacySocial.ts follows migrateLegacyModmail.ts's conventions: LEGACY_DATABASE_URL, --dry-run (full run in a rolled-back transaction) / --live / --verify. Mapping is 1:1 snake_casing with the four schema.sql deviations applied, plus social_interactions.command_id written NULL (legacy ids belong to the legacy application; the P6 resync assigns real ones) and public_leaderboard left at its false default. Anything Redis (leveling_tracking/leveling_ineligible keys, legacy db 1) is deliberately not migrated — ephemeral by design; worst case a user's cooldown resets once at cutover.
      • --source is kept for operator ergonomics but, unlike ModMail's, is not persisted — there is no migration_source column, because every Social key is natural. It labels the run's output and nothing else.
      • A re-run is a safe no-op, not an abort. Nothing here can duplicate (every insert skips on its natural key), so where ModMail's preflight refuses, this one warns and reports the skips in its stats. What it does abort on is legacy data the target's CHECKs would reject — legacy enforced its bounds only in slash-command options, so they were never true of data at rest. Preflight names the offending guild/channel/role rather than letting it surface as an opaque mid-transaction constraint error.
      • --verify does per-table counts, per-guild XP sums (the headline check for the one table too big to compare row by row), full field-level comparison of the five small tables, and a 50-row social_users sample. It exits 1 on any mismatch.
      • A dry run prints wall-clock, which is what sizes the P6 window.
    • copyLegacySocialGuild.ts copies one legacy guild under a different guild id, for stocking a canary test guild with realistic leveling data. --from/--to, --dry-run/--live, and --xp-only (settings + users only). It deletes --to's existing rows in all six tables first, so re-runs are a clean replace — --xp-only narrows the copy but deliberately not the wipe, so a previous full run can't leave rewards stranded under replaced users. A --from naming a guild with no XP is refused, since with the wipe that can only empty --to.
      • Fidelity is a mixed blessing across a guild boundary, and the script says so on completion: social_users transfers perfectly (ids are global, and the leaderboard resolves names through GET /users/{id}, a global lookup — so migrated members render with real usernames and avatars without being members of --to), while channels/roles are inert and reward role ids don't exist in the target, which is what --xp-only is for.
    • Verified with the two-scratch-database method (workflow.md): a hand-transcribed legacy schema in social_src seeded with four guilds covering every branch (NULL multipliers, all three notification modes, xp = 0 and ignored rows, the nullable-config gate, a guild with no settings row, hostile strings), migrated into social_dst. Confirmed: dry-run leaves the target empty, all four deviations land, a re-run skips everything, preflight aborts on each CHECK violation, and --verify goes red on five separately corrupted rows — a green check that can't go red proves nothing. The copy script was verified to remap ids, leave the source guild untouched, and restore exact XP totals across a tampered re-run. No prod dump has been involved yet.

  • P6 — Cutover. Done 2026-08-13, without incident. Runbook, mirroring 06-modmail-port.md's but simpler — no open-thread concept, no per-guild manual repair step, no comms-critical moderator surface: 1. Dry-run against a restored copy of the prod social database; record wall-clock (the User table is the only unknown-magnitude table; nobody has counted it) and size the window off that. 2. Announce/schedule a short maintenance window if the dry-run warrants one at all — XP accrual pausing for minutes is far less user-visible than ModMail messages dropping. 3. Freeze = stop the legacy bot (XP accrues on every message; a stopped bot is a consistent snapshot — nothing to force-close). Snapshot the database; archive the dump offsite (it's per-user activity data — IDs and counters, no message content). 4. Migrate + --verify against the snapshot into prod. 5. Deploy services/social-bot with the legacy application's token (or a new application if a clean break is preferred — decide before P6; a new application also invalidates nothing extra, since interaction commands get resynced either way). 6. Run the interactions resync for every guild that has any (ledger 3). 7. Smoke test in a real guild: send messages → XP gained; window cooldown enforced; level-up notification; reward role applied; /level shows migrated XP; a custom interaction responds. 8. Keep the legacy deployment + database warm for rollback until confident, then decommission (stack/docker-compose.social.yml, the social database on postgres-old, the DockerHub image pipeline).

What to watch in the logs

Added in 9cc26c49 ahead of the cutover, in place of working through the verification matrix below. None of these sit on the per-message hot path: createLogger pins the level to trace with no env override, so a line per message would be a volume problem on an active guild. Every one of them fires only on a level-up or an actual Discord write.

Message Level What it tells you
Social guild unreadable warn A 403/404 on the guild, negatively cached for 5 minutes. Degrades three things at once and silently: reward ties lose the role hierarchy, {{ guildName }} renders as "this server", and every earned reward is dropped from the level-up message. All three look like config bugs from outside.
Member levelled up info Carries oldLevel, newLevel, increment, rewardsApplied, rewardsConfigured — the last separating "the role write failed" from "this guild has no rewards".
Applied reward roles info Only fires on a non-empty diff, so per-write rather than per-message. The line that answers "did they actually get the role", for level-ups and the self-healing repair alike.
Reward roles skipped, member is barred debug The three-minute bar after a failed role write. Each skip also strips earnedRewards from the announcement, so a member is congratulated with no mention of the role they didn't get.
Some earned reward roles could not be named warn The role was applied but can't be resolved — deleted, or the guild is unreadable. The guild warn above distinguishes the two.
Social channel unreadable debug Routine: the parent walk in resolveChannelChain reaches categories nobody granted access to. Only cost is one message tracked without its category's multiplier.

Known blind spot: the per-message gates — guild not configured, user ignored, channel ignored, not eligible — stay unlogged for the volume reason above. "XP isn't accruing at all" is therefore a database-inspection question, not a log-reading one. A LOG_LEVEL env knob would fix that properly, but it's a backend-core change touching every service.

Verification

Per-phase live verification as listed above (workflow.md is the standard — build/lint/test alone doesn't prove a feature). Most of this was deliberately not worked through — see the status note at the top. It stays here as the checklist to reach for if something surfaces in NASCAR.

Live verification is still outstanding for P3, P4 and the leaderboard, and this is also the first time P2's routes will touch Discord at all (they were written before a Social application existed). Needs a SOCIAL_BOT_TOKEN in .env.private for a fresh application, then yarn dev:social-bot alongside yarn dev:api — and with P4 landed, the guild can now be configured from the dashboard rather than by hand, which is itself the first half of P4's verification:

  1. XP gain — messages move social_users.xp by exactly xp_gain.
  2. Window cooldown — with required_messages = 3, timespan = 10, one grant per window, and the bar expiring on the remainder of the window rather than a flat delay. Watch both redis keys.
  3. Multiplier stacking — a channel multiplier and two role multipliers multiply.
  4. Category/thread inheritance — configure a category, post in a child channel and in a thread; confirm a channel-level row beats its category's.
  5. Clean-tier promotion — old tier removed, new one added, unrelated and managed roles untouched.
  6. Multi-level jump — an xp_gain large enough to cross two levels grants both levels' rewards and announces the higher one.
  7. Each notification mode, including a fallback channel that can't be posted in, and a deleted fallback nulling the column.
  8. Interactions — create via the API, confirm /name renders with and without targets, embed and non-embed, and that uses increments.
  9. Name fallback + resync — UPDATE social_interactions SET command_id = NULL (the exact post-migration state), confirm dispatch still resolves by name and self-heals the id, then run the resync and confirm it does not delete /level (the global-commands constraint).
  10. bot:SOCIAL appears in redis and the guild shows a Social tab.

P4 additionally needs, in a browser: every one of the five config sections round-tripping a save against the API; the curve preview and eligibility example matching what the bot actually does once tracking is on; the channel/role/reward add flows refusing an already-configured entry; and the interactions resync card recreating a command after UPDATE social_interactions SET command_id = NULL (item 9 above, driven from the dashboard).

The leaderboard adds, on top of all of the above:

  1. Live movement — with the dashboard leaderboard open, send messages until a grant lands and watch the list update with no refresh. Then send several in quick succession and confirm the 5s throttle coalesces them (redis social:leaderboard-signal:<guildId> is the gate) rather than producing a signal per grant.
  2. The public page — off by default (/leaderboard/<guildId> 404s), reachable once the switch is on, and updating live in a second browser with no session at all. Turning the switch back off should refetch that tab straight onto its "not found" state rather than leaving it on stale-but-live-looking data.
  3. No id fields — the public page's network responses carry no member id as a field, only display names and avatar URLs. Note the standing caveat rather than testing for something untrue: a Discord avatar URL is /avatars/<userId>/<hash>.png, so any member with a custom avatar has their snowflake inside that string. Removing it means proxying avatars through our own domain or dropping them from public pages — an open decision, applying equally to AMA's public answers page, which has had the same property since #323.
  4. Paging — with more than 25 ranked members, page 2 shows ranks 26+ and the pager disables at both ends.
  5. /leaderboard — renders for a non-admin, pings nobody, shows current nicknames, and its "See the full leaderboard" link appears only while the guild has the public page enabled.
  6. Level agreement — a member's level on the leaderboard matches what /level reports for them, and both disappear (leaving bare XP) when the curve is unconfigured.

The migration script gets the two-scratch-DB treatment in P5 before any prod dump is involved; P6's dry-run wall-clock is the only honest window estimate, same discipline as ModMail's. The XP-curve formula gets a dedicated unit test pinning known (settings, xp) → level values, since it's the one piece of math a refactor could silently break and a migration fidelity guarantee depends on.