Jyotisha/frontend/tests/database-redeem-security.test.ts

import assert from "node:assert/strict";
import { spawnSync } from "node:child_process";
import { createHash } from "node:crypto";
import { fileURLToPath } from "node:url";
import test from "node:test";

import {
  closeLocalPostgresDataPools,
  createLocalPostgresDataClient,
} from "../src/lib/db/local-postgres-client-core.ts";
import { formatPaymentOrders, PAYMENT_ORDERS_SELECT } from "../src/lib/payment-orders.ts";
import { startPostgresFixture } from "./helpers/postgres-fixture.ts";

const runnerPath = fileURLToPath(
  new URL("../scripts/db-migrate.mjs", import.meta.url),
);

function hash(code: string): string {
  return createHash("sha256").update(code, "utf8").digest("hex");
}

test("redeem security: case-sensitive hashing, rate limiting, idempotency and order ownership", async () => {
  const fixture = startPostgresFixture();
  try {
    const migration = spawnSync(process.execPath, [runnerPath], {
      encoding: "utf8",
      env: {
        ...process.env,
        SCHEMA_DATABASE_URL: fixture.connectionUrl(
          "schema_owner",
          "schema-owner-test-password",
        ),
      },
    });
    assert.equal(migration.status, 0, migration.stderr);
    // Only the supabase compatibility migration exists; the db/migrations
    // copy was removed because identity-only fixtures apply that directory
    // without the business schema (see BUG-144).
    assert.match(migration.stdout, /applied 20260807030000_redeem_security\.sql/);
    assert.doesNotMatch(migration.stdout, /20260807020000_redeem_security/);

    fixture.psqlAs("identity_runtime", "identity-runtime-test-password", `
      insert into identity.users (name, email, email_verified, email_verified_at)
      values ('User A', 'redeem-a@example.com', true, now()),
             ('User B', 'redeem-b@example.com', true, now())
    `);
    const userA = fixture.psql(
      "select id from identity.users where email = 'redeem-a@example.com'",
    );
    const userB = fixture.psql(
      "select id from identity.users where email = 'redeem-b@example.com'",
    );

    const validCodeA = "JYOTISH-TEST-ABCD";
    const validCodeB = "JYOTISH-TEST-WXYZ";
    fixture.psql(`
      insert into public.redemption_codes (code_hash, code_mask, credits)
      values ('${hash(validCodeA)}', 'JYOTISH-****-ABCD', 3),
             ('${hash(validCodeB)}', 'JYOTISH-****-WXYZ', 5)
    `);

    const local = createLocalPostgresDataClient(
      fixture.connectionUrl("app_runtime", "app-runtime-test-password"),
      { id: userA, email: "redeem-a@example.com" },
    );

    // 1) Case-sensitive matching: the lowercase hash must not match.
    const lowercase = await local.rpc("redeem_code", {
      p_code_hash: hash("jyotish-test-abcd"),
    });
    assert.deepEqual(lowercase.data, [
      { success: false, credits: null, awarded_credits: null, error_code: "invalid_code" },
    ]);

    // 2) The exact uppercase hash redeems: awarded credits and balance delta.
    const redeemed = await local.rpc("redeem_code", {
      p_code_hash: hash(validCodeA),
    });
    assert.deepEqual(redeemed.data, [
      { success: true, credits: 3, awarded_credits: 3, error_code: null },
    ]);
    // A successful redemption deletes all of the account's attempts; no
    // permanent success row is kept.
    assert.equal(
      fixture.psql(
        `select count(*) from public.redemption_attempts where user_id='${userA}'`,
      ),
      "0",
    );

    // 3) Idempotency: reusing the same code is already_redeemed and never
    //    double-credits (redemption row lock + credit_transactions unique).
    const again = await local.rpc("redeem_code", {
      p_code_hash: hash(validCodeA),
    });
    assert.deepEqual(again.data, [
      { success: false, credits: null, awarded_credits: null, error_code: "already_redeemed" },
    ]);
    assert.equal(
      fixture.psql(
        `select count(*) from public.credit_transactions where user_id='${userA}' and transaction_type='redeem'`,
      ),
      "1",
    );
    assert.equal(
      fixture.psql(`select credits from public.profiles where id='${userA}'`),
      "3",
    );

    // 4) Rate limiting: the 5th failure is recorded, the 6th attempt is
    //    blocked even with a valid unused code, and nothing is credited.
    for (let index = 0; index < 4; index += 1) {
      const failed = await local.rpc("redeem_code", {
        p_code_hash: hash(`NO-SUCH-${index}`),
      });
      assert.deepEqual(failed.data, [
        { success: false, credits: null, awarded_credits: null, error_code: "invalid_code" },
      ]);
    }
    assert.equal(
      fixture.psql(
        `select count(*) from public.redemption_attempts where user_id='${userA}'`,
      ),
      "5",
    );
    const blocked = await local.rpc("redeem_code", {
      p_code_hash: hash(validCodeB),
    });
    assert.deepEqual(blocked.data, [
      { success: false, credits: null, awarded_credits: null, error_code: "rate_limited" },
    ]);
    assert.equal(
      fixture.psql(`select credits from public.profiles where id='${userA}'`),
      "3",
    );
    // rate_limited itself is not a new business failure.
    assert.equal(
      fixture.psql(
        `select count(*) from public.redemption_attempts where user_id='${userA}'`,
      ),
      "5",
    );

    // 5) The window is rolling: aging failures past 10 minutes unblocks.
    fixture.psql(
      `update public.redemption_attempts set created_at = now() - interval '11 minutes' where user_id='${userA}'`,
    );
    const afterWindow = await local.rpc("redeem_code", {
      p_code_hash: hash(validCodeB),
    });
    assert.deepEqual(afterWindow.data, [
      { success: true, credits: 8, awarded_credits: 5, error_code: null },
    ]);
    assert.equal(
      fixture.psql(
        `select count(*) from public.redemption_attempts where user_id='${userA}'`,
      ),
      "0",
    );

    // 6) Per-account isolation: user B is unaffected by user A's failures.
    const userBClient = createLocalPostgresDataClient(
      fixture.connectionUrl("app_runtime", "app-runtime-test-password"),
      { id: userB, email: "redeem-b@example.com" },
    );
    for (let index = 0; index < 2; index += 1) {
      const bAttempt = await userBClient.rpc("redeem_code", {
        p_code_hash: "f".repeat(64),
      });
      assert.equal(
        (bAttempt.data as Array<{ error_code: string }>)[0]?.error_code,
        "invalid_code",
      );
    }
    assert.equal(
      fixture.psql(
        `select count(*) from public.redemption_attempts where user_id='${userB}'`,
      ),
      "2",
    );

    // 7) Orders: the route's exact query returns only the current user's
    //    recent orders, most recent first.
    fixture.psql(`
      insert into public.payment_orders (
        order_no, user_id, money_cents, product_code, product_snapshot,
        status, grant_status, created_at, paid_at
      ) values
        ('${"JY" + "A1".padEnd(14, "0")}', '${userA}', 9900, 'standard_monthly', '{"name":"标准月卡"}'::jsonb, 'paid', 'granted', now() - interval '1 day', now() - interval '1 day'),
        ('${"JY" + "A2".padEnd(14, "0")}', '${userA}', 990, 'trial_7d', '{"name":"体验卡"}'::jsonb, 'paid', 'granted', now(), now()),
        ('${"JY" + "B1".padEnd(14, "0")}', '${userB}', 9900, 'standard_monthly', '{"name":"标准月卡"}'::jsonb, 'paid', 'granted', now(), now())
    `);
    const orders = await local
      .from("payment_orders")
      .select(PAYMENT_ORDERS_SELECT.join(","))
      .eq("user_id", userA)
      .order("created_at", { ascending: false })
      .limit(20);
    assert.equal(orders.error, null);
    const orderRows = orders.data as Array<{ order_no: string }>;
    assert.deepEqual(
      orderRows.map((row) => row.order_no),
      ["JY" + "A2".padEnd(14, "0"), "JY" + "A1".padEnd(14, "0")],
    );
    assert.deepEqual(
      formatPaymentOrders(orders.data as never).map((order) => order.orderNo),
      ["JY" + "A2".padEnd(14, "0"), "JY" + "A1".padEnd(14, "0")],
    );

    // RLS still filters direct selects to the authenticated account.
    assert.equal(
      fixture.psqlAs(
        "app_runtime",
        "app-runtime-test-password",
        `set role authenticated;
         select set_config('request.jwt.claim.sub', '${userA}', true);
         select count(*) from public.payment_orders`,
      ),
      `SET\n${userA}\n2`,
    );

    // 8) The audit table stores no code material and has least privilege.
    assert.equal(
      fixture.psql(`
        select string_agg(column_name, ',' order by ordinal_position)
        from information_schema.columns
        where table_schema = 'public' and table_name = 'redemption_attempts'
      `),
      "id,user_id,created_at",
    );
    assert.equal(
      fixture.psql(
        `select relrowsecurity from pg_class where relname = 'redemption_attempts'`,
      ),
      "t",
    );
    assert.equal(
      fixture.psql(
        `select has_table_privilege('authenticated', 'public.redemption_attempts', 'select')`,
      ),
      "f",
    );
    assert.equal(
      fixture.psql(
        `select has_table_privilege('authenticated', 'public.redemption_attempts', 'insert')`,
      ),
      "f",
    );
    assert.equal(
      fixture.psql(
        `select has_table_privilege('anon', 'public.redemption_attempts', 'select')`,
      ),
      "f",
    );
    assert.equal(
      fixture.psql(
        `select has_table_privilege('service_role', 'public.redemption_attempts', 'select')`,
      ),
      "t",
    );
    // The identity sequence needs no authenticated USAGE grant: inserts flow
    // through the security-definer redeem_code which runs as its owner. The
    // count assertions above already prove the definer can insert.
    assert.equal(
      fixture.psql(
        `select has_sequence_privilege('authenticated', 'public.redemption_attempts_id_seq', 'USAGE')`,
      ),
      "f",
    );
    assert.equal(
      fixture.psql(
        `select has_function_privilege('authenticated', 'public.redeem_code(text)', 'execute')`,
      ),
      "t",
    );
    assert.equal(
      fixture.psql(
        `select has_function_privilege('anon', 'public.redeem_code(text)', 'execute')`,
      ),
      "f",
    );
  } finally {
    await closeLocalPostgresDataPools();
    fixture.stop();
  }
});