/retool-migration

name: retool-migration
description: Port a Retool app's functionality into a page in apps/moderator. Use when given a Retool JSON export (or asked to migrate/replace a Retool moderation tool) and a page needs to be built in the moderator app. Decodes the export, inventories its queries, and builds the page with shadcn + Tailwind under the Retool nav group.

Retool → moderator app migration

Ports what a Retool app **does** into `apps/moderator`. Retool's layout, styling and component tree are **not** ported — the JSON is a spec for behaviour, not a design.

Sibling skill: [`moderator-page-migration`](../moderator-page-migration/SKILL.md), for the *other* inbound path — pages coming from the main Next.js app's `src/pages/moderator/**`. Same app, same conventions, same reviews; different source, and a cutover step this path does not have (deleting the legacy page and trimming what it orphans). If the thing you are porting is a `/moderator/*` page rather than a Retool export, use that one.

Setup (once per checkout)

cd .claude/skills/retool-migration && npm install

The export is not plain JSON: `page.data.appState` is a **transit-js** encoded string (`~#iR` tag, `["^ ", k, v, …]` maps, `^N` back-references). `JSON.parse` alone gets you an opaque blob. `extract.mjs` handles it; don't try to read the raw file.

0. Check the scope and tracker first

[`CLICKUP-SCOPE.md`](CLICKUP-SCOPE.md) holds the content of the two ClickUp tickets that define this work — what to build (868kkxqpn) and which tables to move (868kn67aq) — as checklists, so the work does not depend on ClickUp access. Read it for *why*; read the tracker for *how far*.

[`MIGRATIONS.md`](MIGRATIONS.md) in this directory lists every app handed to us and how far each has got. **Read it before starting** — a slice may already be shipped, blocked, or deliberately dropped.

Keep it current as part of the work:

• a new export arrives → add its row (status `not started`) before writing any code
• a slice ships and verifies → tick it
• something is skipped → record it with the reason

Do not tick a slice because the page renders — an app is not `done` until the Retool original is switched off. Moderator-database slices *can* be ticked once they work: the app reads and writes that data live (see below), so there is no data migration to wait for.

The moderator database (`retool_db` in the exports)

User notes, model notes and image help requests live in a database of their own, never in Civitai's. (Legacy `UserStrikes` and `TimedMutes` are there too but are **not** the live implementations — strikes go through `strike/create` and timed mutes are `User.muteExpiresAt`. `TimedMutes` is not even typed.)

**The app reads and writes this database through `getModeratorDb()`** — port these queries like any other; there is no need to wait for a data migration.

import { getModeratorDb } from '$lib/server/moderator-db';

const notes = await getModeratorDb()
  .selectFrom('UserNotes')
  .select(['id', 'notes', 'lastUpdate', 'lastUpdateBy'])
  .where('userId', '=', userId)
  .orderBy('lastUpdate', 'desc')
  .execute();

Since 2026-08-21 it points at the **moderator** database (`MODERATOR_DATABASE_URL`), not Retool's — the two were consolidated. Retool may still be live for tables it owns, so keep writes compatible with what it expects to read back until it is switched off; see [`retool-db-cutover.md`](../../../docs/moderator-app/retool-db-cutover.md).

Attribution: write the name, ids come later

`createdBy` / `lastUpdateBy` / `handledBy` are free text holding Retool *display names*, and the name → id map is being assembled separately — 53 distinct names across nine columns; see [`moderator-db-backfill-tasks.md`](../../../docs/moderator-app/moderator-db-backfill-tasks.md).

Until it lands, **write `locals.user.username` into those columns** and do not invent an id column. New rows are then at least resolvable (a Civitai username maps to an account trivially), while historical rows wait for the mapping. Record in the slice's tracker entry that its writes use usernames, so the backfill knows there are two naming schemes to reconcile.

Do not block a slice on this. Functionality first; attribution is a follow-up migration.

Querying it directly

For scoping and schema questions, outside the app:

cp .env.example .env      # then fill in MODERATOR_DATABASE_URL
node .claude/skills/retool-migration/retool-db.mjs --tables
node .claude/skills/retool-migration/retool-db.mjs --describe UserStrikes
node .claude/skills/retool-migration/retool-db.mjs "SELECT * FROM \"UserStrikes\" LIMIT 5"

**`retool-db.mjs` is read-only, not gated behind a flag** — writes are refused before the connection is even opened. It exists for scoping and schema questions; the app itself goes through `getModeratorDb()`, which does write.

1. Inventory the app

Committed inventories for the apps handed over so far live in [`docs/moderator-app/retool-exports/`](../../../docs/moderator-app/retool-exports/) — read those first; you may not need the raw export at all.

**Never commit a raw export.** `User Lookup v2.json` contains a hardcoded `Authorization: Bearer <token>` header repeated seven times; the others may too. Keep exports outside the repo (`~/Downloads/Retool/`) and commit the generated inventory instead — it carries the SQL and no auth config.

node .claude/skills/retool-migration/extract.mjs "<export.json>"           # full inventory
node .claude/skills/retool-migration/extract.mjs "<export.json>" --queries # SQL only
node .claude/skills/retool-migration/extract.mjs "<export.json>" --json    # machine-readable

You get: query count, component count, backing resources, a component-type histogram (a scale signal only — do not reproduce it), and every query with its SQL/URL plus the `{{ … }}` bindings it depends on.

**The queries are most of the spec, but NOT all of it.** Read them first and work out what q