FTStore/web/prisma/pre-push.sql

-- Everything `prisma db push` refuses to do to a database that already has rows.
--
-- Production deploys by pushing the schema (see `Dockerfile`), and `db push`
-- stops the moment a change *could* destroy data: a dropped column, a dropped
-- table, a unique index over rows it has not seen. It stops for the whole push,
-- so one refused drop leaves every other change unapplied and the app and the
-- worker restart-loop on
--
--     Error: Use the --accept-data-loss flag to ignore the data loss warnings
--
-- `--accept-data-loss` is not the answer, because it does not answer the
-- question: it would wave through every future drop, including the ones nobody
-- meant to make. Instead each such change is written out here in full, once, and
-- applied before the push. The push then only ever sees work it considers safe,
-- and a destructive change is something somebody wrote down and reviewed rather
-- than something a flag allowed through at three in the morning.
--
-- Rules for anything added here:
--
--  • **Idempotent.** Both containers run this on every boot, so every block has
--    to be a no-op the second time.
--  • **Silent on a fresh database.** Nothing exists yet on a first boot and
--    `db push` creates all of it, so every block first checks that what it is
--    about to change is actually there.
--  • **Prisma's own names.** Indexes and constraints created here must be named
--    exactly what `db push` would have named them, or the push reads them as
--    drift and recreates them on the spot.
--  • **Entries stay.** A deployment that skipped four releases has to be able to
--    run the whole file, in order, and arrive where a fresh one starts.
--
-- The matching `prisma/migrations/…` entry stays authoritative for the *shape*
-- of each change; this file is that shape rewritten to survive being run against
-- a database that may already be half-way there.

-- The app and the worker boot together and both run this. The lock makes the
-- second one wait instead of racing the first through the same CREATEs. It is
-- session-scoped, so it is released when the script's connection closes; the
-- number is arbitrary and only has to stay the same between the two containers.
SELECT pg_advisory_lock(8710571);

-- 20260726120000_add_local_ipa_storage — local disk storage for IPA files.
--
-- `download_token` is NULL on every row that predates it and Postgres counts
-- NULLs in a unique index as distinct, so the index cannot collide. `db push`
-- warns anyway, because "unique index over an existing table" is as far as its
-- reasoning goes.
DO $$
BEGIN
  IF to_regclass('"downloaded_ipas"') IS NULL THEN
    RETURN;
  END IF;

  ALTER TABLE "downloaded_ipas" ADD COLUMN IF NOT EXISTS "storage_path" TEXT;
  ALTER TABLE "downloaded_ipas" ADD COLUMN IF NOT EXISTS "download_token" TEXT;

  CREATE UNIQUE INDEX IF NOT EXISTS "downloaded_ipas_download_token_key"
    ON "downloaded_ipas"("download_token");
  CREATE INDEX IF NOT EXISTS "downloaded_ipas_storage_path_idx"
    ON "downloaded_ipas"("storage_path");
END
$$;

-- 20260806160000_add_feedback_identities — the columns behind a registered name.
--
-- Split from the backfill below so the statements that read these columns are
-- parsed after they exist.
DO $$
BEGIN
  IF to_regclass('"feedback_authors"') IS NULL THEN
    RETURN;
  END IF;

  -- Checked by hand rather than with ADD COLUMN IF NOT EXISTS: `SERIAL` is
  -- expanded into a sequence plus a default before the existence check is
  -- reached, and a second boot must not leave an orphan sequence behind.
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = current_schema()
      AND table_name = 'feedback_authors'
      AND column_name = 'handle_number'
  ) THEN
    ALTER TABLE "feedback_authors" ADD COLUMN "handle_number" SERIAL;
  END IF;

  ALTER TABLE "feedback_authors"
    ADD COLUMN IF NOT EXISTS "name_key" TEXT,
    ADD COLUMN IF NOT EXISTS "pin_hash" TEXT,
    ADD COLUMN IF NOT EXISTS "pin_failures" INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS "pin_locked_until" TIMESTAMP(3);
END
$$;

DO $$
BEGIN
  IF to_regclass('"feedback_authors"') IS NULL THEN
    RETURN;
  END IF;

  -- Names that existed before registration did. They keep the name they are
  -- posting under and hold it against anyone else claiming it; their owner is
  -- asked for a PIN the first time they open the name screen. The expression is
  -- `displayNameKey` from `lib/feedback.ts`, spelled in SQL.
  UPDATE "feedback_authors"
  SET "name_key" = trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g'))
  WHERE "name_key" IS NULL
    AND "display_name" IS NOT NULL
    AND trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g')) <> '';

  -- Two installs that picked the same name back when nothing stopped them: the
  -- older row keeps it, the rest go back to their anonymous handle rather than
  -- the unique index below failing and taking the whole boot with it. A no-op
  -- on every later run, since the index makes the duplicate impossible.
  UPDATE "feedback_authors" a
  SET "display_name" = NULL, "name_key" = NULL
  WHERE a."name_key" IS NOT NULL
    AND EXISTS (
      SELECT 1 FROM "feedback_authors" b
      WHERE b."name_key" = a."name_key"
        AND (b."created_at" < a."created_at"
          OR (b."created_at" = a."created_at" AND b."install_id" < a."install_id"))
    );

  CREATE UNIQUE INDEX IF NOT EXISTS "feedback_authors_handle_number_key"
    ON "feedback_authors"("handle_number");
  CREATE UNIQUE INDEX IF NOT EXISTS "feedback_authors_name_key_key"
    ON "feedback_authors"("name_key");
END
$$;

-- 20260806190000_add_feedback_votes — voting replaces reacting.
--
-- The vote table is created here rather than left to the push because the rows
-- of the table being dropped have to land somewhere first.
DO $$
BEGIN
  IF to_regclass('"feedback_tickets"') IS NULL THEN
    RETURN;
  END IF;

  IF to_regclass('"feedback_votes"') IS NOT NULL THEN
    RETURN;
  END IF;

  CREATE TABLE "feedback_votes" (
    "id" SERIAL NOT NULL,
    "ticket_id" INTEGER NOT NULL,
    "comment_id" INTEGER,
    "install_id" TEXT NOT NULL,
    "value" INTEGER NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "feedback_votes_pkey" PRIMARY KEY ("id")
  );

  CREATE INDEX "feedback_votes_ticket_id_comment_id_idx"
    ON "feedback_votes"("ticket_id", "comment_id");
  CREATE INDEX "feedback_votes_install_id_idx" ON "feedback_votes"("install_id");

  ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_ticket_id_fkey"
    FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
  ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_comment_id_fkey"
    FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
END
$$;

-- The two emoji that were already votes carry over; the other six were opinions
-- with no ranking behind them and go with the table. The reaction table's own
-- existence is the guard: once it is dropped this block never runs again.
DO $$
BEGIN
  IF to_regclass('"feedback_reactions"') IS NULL THEN
    RETURN;
  END IF;

  -- One row per install per target. An install that managed to file two `+1`s
  -- on one ticket was always counted once, and `DISTINCT ON` keeps that true;
  -- an install that reacted both 👍 and 👎 ends up with its newest one.
  INSERT INTO "feedback_votes" ("ticket_id", "comment_id", "install_id", "value", "created_at", "updated_at")
  SELECT DISTINCT ON ("ticket_id", "comment_id", "install_id")
    "ticket_id",
    "comment_id",
    "install_id",
    CASE WHEN "emoji" = '+1' THEN 1 ELSE -1 END,
    "created_at",
    "created_at"
  FROM "feedback_reactions"
  WHERE "emoji" IN ('+1', '-1')
  ORDER BY "ticket_id", "comment_id", "install_id", "created_at" DESC;

  DROP TABLE "feedback_reactions";
END
$$;

DO $$
BEGIN
  IF to_regclass('"feedback_tickets"') IS NULL THEN
    RETURN;
  END IF;

  ALTER TABLE "feedback_tickets"
    ADD COLUMN IF NOT EXISTS "vote_up" INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS "vote_down" INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS "vote_score" INTEGER NOT NULL DEFAULT 0;

  -- `feedback_tickets_hidden_upvotes_idx` goes with the column it was on.
  CREATE INDEX IF NOT EXISTS "feedback_tickets_hidden_vote_score_idx"
    ON "feedback_tickets"("hidden", "vote_score");
END
$$;

-- The counters are seeded from the votes exactly once, while the column they
-- replace is still there. Later boots must not touch them: they are recomputed
-- on every write by `refreshTicketCounters`, and rewriting them here would undo
-- whatever the board did between two deploys.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = current_schema()
      AND table_name = 'feedback_tickets'
      AND column_name = 'upvotes'
  ) THEN
    RETURN;
  END IF;

  UPDATE "feedback_tickets" t
  SET "vote_up" = v."up", "vote_down" = v."down", "vote_score" = v."up" - v."down"
  FROM (
    SELECT
      "ticket_id",
      count(*) FILTER (WHERE "value" > 0) AS "up",
      count(*) FILTER (WHERE "value" < 0) AS "down"
    FROM "feedback_votes"
    WHERE "comment_id" IS NULL
    GROUP BY "ticket_id"
  ) v
  WHERE t."id" = v."ticket_id";

  ALTER TABLE "feedback_tickets" DROP COLUMN "upvotes";
END
$$;

-- 20260812090000_tweak_request_group_key — seed the wish grouping key.
--
-- A tweak request may now be filed without a source, so the board can no longer
-- group on `source_id`: every sourceless row would collapse into one wish. It
-- groups on `group_key` instead, which is the source when there is one and the
-- normalised tweak name when there is not.
--
-- Every row that predates the column has a source, so its key is exactly that
-- source. `db push` adds the column with an empty-string default; this is what
-- turns those defaults into the value the code would have written.
--
-- Idempotent by the `group_key = ''` filter: a second run finds nothing left to
-- do. It deliberately does not touch rows that already have a key.
DO $$
BEGIN
  IF to_regclass('"tweak_requests"') IS NULL THEN
    RETURN;
  END IF;

  -- Written by `db push` on the same boot, so on a fresh database this runs
  -- before the column exists and has nothing to seed either way.
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = current_schema()
      AND table_name = 'tweak_requests'
      AND column_name = 'group_key'
  ) THEN
    RETURN;
  END IF;

  UPDATE "tweak_requests"
  SET "group_key" = "source_id"
  WHERE "group_key" = '' AND "source_id" IS NOT NULL;
END
$$;

-- Accounts on the boards: a real password, a picture, and a session per device.
--
-- `password_hash` and `avatar_path` are NULL on every row that predates them,
-- which is exactly what "this install never registered" means to the code, so
-- there is nothing to backfill. The unique index over `avatar_path` cannot
-- collide for the same reason `download_token`'s could not: Postgres counts
-- NULLs in a unique index as distinct. `db push` warns anyway, because "unique
-- index over an existing table" is as far as its reasoning goes.
--
-- `app_sessions` is created here rather than left to the push so that the
-- foreign key onto `feedback_authors` exists before anything tries to sign in
-- against a half-applied schema.
DO $$
BEGIN
  IF to_regclass('"feedback_authors"') IS NULL THEN
    RETURN;
  END IF;

  ALTER TABLE "feedback_authors" ADD COLUMN IF NOT EXISTS "password_hash" TEXT;
  ALTER TABLE "feedback_authors" ADD COLUMN IF NOT EXISTS "avatar_path" TEXT;

  CREATE UNIQUE INDEX IF NOT EXISTS "feedback_authors_avatar_path_key"
    ON "feedback_authors"("avatar_path");

  CREATE TABLE IF NOT EXISTS "app_sessions" (
    "token_hash"   TEXT NOT NULL,
    "install_id"   TEXT NOT NULL,
    "device_name"  TEXT,
    "created_at"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "expires_at"   TIMESTAMP(3) NOT NULL,
    "last_seen_at" TIMESTAMP(3),

    CONSTRAINT "app_sessions_pkey" PRIMARY KEY ("token_hash")
  );

  CREATE INDEX IF NOT EXISTS "app_sessions_install_id_idx"
    ON "app_sessions"("install_id");

  -- Named what `db push` would name it, or the push reads it as drift and
  -- recreates it on the spot.
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_schema = current_schema()
      AND constraint_name = 'app_sessions_install_id_fkey'
  ) THEN
    ALTER TABLE "app_sessions"
      ADD CONSTRAINT "app_sessions_install_id_fkey"
      FOREIGN KEY ("install_id") REFERENCES "feedback_authors"("install_id")
      ON DELETE CASCADE ON UPDATE CASCADE;
  END IF;
END
$$;

-- The setup backup that hangs off an account.
--
-- Nothing to backfill: an account with no row simply has no backup, which is
-- what the route answers. Created here rather than left to the push so the
-- foreign key onto `feedback_authors` exists before anything writes one.
DO $$
BEGIN
  IF to_regclass('"feedback_authors"') IS NULL THEN
    RETURN;
  END IF;

  CREATE TABLE IF NOT EXISTS "app_backups" (
    "install_id"     TEXT NOT NULL,
    "payload"        TEXT NOT NULL,
    "format_version" INTEGER NOT NULL,
    "device_name"    TEXT,
    "bytes"          INTEGER NOT NULL,
    "created_at"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at"     TIMESTAMP(3) NOT NULL,

    CONSTRAINT "app_backups_pkey" PRIMARY KEY ("install_id")
  );

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_schema = current_schema()
      AND constraint_name = 'app_backups_install_id_fkey'
  ) THEN
    ALTER TABLE "app_backups"
      ADD CONSTRAINT "app_backups_install_id_fkey"
      FOREIGN KEY ("install_id") REFERENCES "feedback_authors"("install_id")
      ON DELETE CASCADE ON UPDATE CASCADE;
  END IF;
END
$$;

-- Private mode became account-based, so the codes and the passes they opened go.
--
-- A six-digit code said "you may look" and an account says who you are, and once
-- both existed the code was a second credential doing a worse version of the
-- same job: it could not carry what somebody wrote, it could not be signed out
-- of, and it was one more secret to hand over. `registration_invites` replaced
-- it, and what it hands out is an account rather than a way past the door.
--
-- **This locks out every device that was let in by a code.** There is no way to
-- turn a pass into an account: a pass says a code was answered on some device
-- and nothing about who was holding it, which is exactly the gap accounts close.
-- So on a private deployment everybody signs in once more, with an invitation
-- from the operator. On a public one nothing here is reachable and nothing
-- changes.
--
-- Passes before codes: the foreign key points that way.
DROP TABLE IF EXISTS "access_passes";
DROP TABLE IF EXISTS "access_codes";

SELECT pg_advisory_unlock(8710571);