scan-receipts/tests/integration/auth_db.test.ts

/**
 * Auth Database Integration Suite
 *
 * Exercises the parts of the auth system that only mean anything against a real
 * Postgres: the unique email key, verification-token lifecycle, Google account
 * linking, and session lookup.
 *
 * Run with:  npm run test:auth
 *
 * Requires a reachable DATABASE_URL with the migrations applied
 * (`docker compose up -d postgres && npm run db:push`). Without a database the
 * script reports a skip and exits 0 rather than pretending to have passed.
 */

// Keep first: populates DATABASE_URL before the database module below reads it.
import "./loadEnv";

import { and, eq, gt, like } from "drizzle-orm";
import { describe, test, expect, runAllTests } from "../e2e/runner";
import { db, isDatabaseAvailable, pool } from "../../src/lib/db";
import {
  email_verification_tokens,
  oauth_accounts,
  password_reset_tokens,
  sessions,
  users,
} from "../../src/lib/schema/db";
import {
  consumeVerificationToken,
  createUser,
  findUserByEmail,
  findUserByGoogleId,
  isGoogleOnlyAccount,
  isUniqueViolation,
  issuePasswordResetLink,
  issueVerificationLink,
  linkGoogleAccount,
  peekPasswordResetToken,
  resetPasswordWithToken,
} from "../../src/lib/auth/accounts";
import { hashPassword, verifyPassword } from "../../src/lib/auth/password";
import { hashToken, issueToken, newId } from "../../src/lib/auth/tokens";

/** Namespaced so cleanup can never touch a real account. */
const RUN_ID = Date.now().toString(36);
const LOCAL_PART = `authtest-${RUN_ID}`;
const BASE_EMAIL = `${LOCAL_PART}@gmail.com`;

function extractToken(devLink: string | undefined): string {
  if (!devLink) {
    throw new Error(
      "No dev link returned — the mailer tried to actually send. `loadEnv` should have cleared the SMTP variables; check it is still the first import."
    );
  }
  const token = new URL(devLink).searchParams.get("token");
  if (!token) throw new Error(`No token in dev link: ${devLink}`);
  return token;
}

async function cleanup() {
  // Children cascade from users; the LIKE keeps this scoped to this run.
  await db.delete(users).where(like(users.emailKey, `authtest-%`));
}

function registerSuites() {
  describe("Auth DB — one email, one account", () => {
    test("a fresh signup creates a non-guest, unverified account", async () => {
      const user = await createUser({
        email: BASE_EMAIL,
        name: "Auth Test",
        passwordHash: await hashPassword("Sicher1234!"),
      });

      expect(user.isGuest).toBe(false);
      expect(user.emailVerifiedAt).toBe(null);
      expect(user.emailKey).toBe(BASE_EMAIL);
      expect(isGoogleOnlyAccount(user)).toBe(false);
    });

    test("dotted and plus-tagged variants resolve to that same account", async () => {
      const dotted = `${LOCAL_PART.split("").join(".")}@gmail.com`;
      const tagged = `${LOCAL_PART}+throwaway@googlemail.com`;

      const viaDots = await findUserByEmail(dotted);
      const viaTag = await findUserByEmail(tagged);
      const direct = await findUserByEmail(BASE_EMAIL);

      expect(viaDots?.id).toBe(direct?.id);
      expect(viaTag?.id).toBe(direct?.id);
    });

    test("registering an aliased variant is rejected by the unique index", async () => {
      let violated = false;
      try {
        await createUser({
          email: `${LOCAL_PART}+second@gmail.com`,
          passwordHash: await hashPassword("Sicher1234!"),
        });
      } catch (error) {
        violated = isUniqueViolation(error);
      }
      expect(violated).toBe(true);
    });

    test("twenty aliased signup attempts yield exactly one row", async () => {
      for (let index = 0; index < 20; index += 1) {
        try {
          await createUser({
            email: `${LOCAL_PART}+bulk${index}@gmail.com`,
            passwordHash: "x",
          });
        } catch (error) {
          if (!isUniqueViolation(error)) throw error;
        }
      }

      const rows = await db.select().from(users).where(eq(users.emailKey, BASE_EMAIL));
      expect(rows.length).toBe(1);
    });
  });

  describe("Auth DB — confirmation link lifecycle", () => {
    test("issuing a link stores exactly one pending token", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      await issueVerificationLink(user, "en");
      await issueVerificationLink(user, "en");

      const pending = await db
        .select()
        .from(email_verification_tokens)
        .where(eq(email_verification_tokens.userId, user.id));

      // Re-issuing replaces the previous link rather than piling them up.
      expect(pending.length).toBe(1);
    });

    test("opening the link verifies the account", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issueVerificationLink(user, "en");
      const token = extractToken(mail.devLink);

      const outcome = await consumeVerificationToken(token);
      expect(outcome.status).toBe("success");

      const refreshed = await findUserByEmail(BASE_EMAIL);
      expect(refreshed?.emailVerifiedAt !== null).toBe(true);
    });

    test("a token cannot be replayed", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issueVerificationLink(user, "en");
      const token = extractToken(mail.devLink);

      // Already-verified accounts short-circuit; the point is it never re-succeeds.
      const first = await consumeVerificationToken(token);
      const second = await consumeVerificationToken(token);
      expect(first.status === "success" || first.status === "already_verified").toBe(true);
      expect(second.status === "success").toBe(false);
    });

    test("an unknown token is rejected", async () => {
      const outcome = await consumeVerificationToken(issueToken().token);
      expect(outcome.status).toBe("invalid");
    });

    test("an elapsed token reports as expired, not invalid", async () => {
      const expiredUser = await createUser({
        email: `${LOCAL_PART}-expired@example.com`,
        passwordHash: "x",
      });

      const { token, hash } = issueToken();
      await db.insert(email_verification_tokens).values({
        id: hash,
        userId: expiredUser.id,
        email: expiredUser.email ?? "",
        expiresAt: new Date(Date.now() - 60_000),
      });

      const outcome = await consumeVerificationToken(token);
      expect(outcome.status).toBe("expired");
    });

    test("only the digest is stored — the raw token appears nowhere", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issueVerificationLink(user, "en");
      const token = extractToken(mail.devLink);

      const byRaw = await db
        .select()
        .from(email_verification_tokens)
        .where(eq(email_verification_tokens.id, token));
      const byHash = await db
        .select()
        .from(email_verification_tokens)
        .where(eq(email_verification_tokens.id, hashToken(token)));

      expect(byRaw.length).toBe(0);
      expect(byHash.length).toBe(1);
    });
  });

  describe("Auth DB — password reset", () => {
    test("issuing a reset link replaces any earlier pending one", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      await issuePasswordResetLink(user, "en");
      await issuePasswordResetLink(user, "en");

      const pending = await db
        .select()
        .from(password_reset_tokens)
        .where(eq(password_reset_tokens.userId, user.id));

      expect(pending.length).toBe(1);
    });

    test("a live token reads as valid without being spent", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issuePasswordResetLink(user, "en");
      const token = extractToken(mail.devLink);

      expect(await peekPasswordResetToken(token)).toBe("valid");
      // Peeking twice must not consume it — the page loads before the form posts.
      expect(await peekPasswordResetToken(token)).toBe("valid");
    });

    test("the new password replaces the old one", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issuePasswordResetLink(user, "en");
      const token = extractToken(mail.devLink);

      const outcome = await resetPasswordWithToken(token, "BrandNew5678!");
      expect(outcome.status).toBe("success");

      const updated = await findUserByEmail(BASE_EMAIL);
      expect(await verifyPassword("BrandNew5678!", updated?.passwordHash ?? null)).toBe(true);
      expect(await verifyPassword("Sicher1234!", updated?.passwordHash ?? null)).toBe(false);
    });

    test("resetting signs out every existing session", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      await db.insert(sessions).values({
        id: issueToken().hash,
        userId: user.id,
        expiresAt: new Date(Date.now() + 60_000),
      });
      await db.insert(sessions).values({
        id: issueToken().hash,
        userId: user.id,
        expiresAt: new Date(Date.now() + 60_000),
      });

      const mail = await issuePasswordResetLink(user, "en");
      await resetPasswordWithToken(extractToken(mail.devLink), "AnotherOne901!");

      const remaining = await db.select().from(sessions).where(eq(sessions.userId, user.id));
      expect(remaining.length).toBe(0);
    });

    test("a reset token cannot be replayed", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const mail = await issuePasswordResetLink(user, "en");
      const token = extractToken(mail.devLink);

      expect((await resetPasswordWithToken(token, "FirstUse123!")).status).toBe("success");
      expect((await resetPasswordWithToken(token, "SecondUse123!")).status).toBe("invalid");
      expect(await peekPasswordResetToken(token)).toBe("invalid");

      // The second attempt must not have taken effect.
      const updated = await findUserByEmail(BASE_EMAIL);
      expect(await verifyPassword("FirstUse123!", updated?.passwordHash ?? null)).toBe(true);
      expect(await verifyPassword("SecondUse123!", updated?.passwordHash ?? null)).toBe(false);
    });

    test("an elapsed reset token reports expired and changes nothing", async () => {
      const user = await createUser({
        email: `${LOCAL_PART}-reset-expired@example.com`,
        passwordHash: await hashPassword("Original123!"),
      });

      const { token, hash } = issueToken();
      await db.insert(password_reset_tokens).values({
        id: hash,
        userId: user.id,
        expiresAt: new Date(Date.now() - 60_000),
      });

      expect(await peekPasswordResetToken(token)).toBe("expired");
      expect((await resetPasswordWithToken(token, "Replacement123!")).status).toBe("expired");

      const unchanged = await findUserByEmail(user.email ?? "");
      expect(await verifyPassword("Original123!", unchanged?.passwordHash ?? null)).toBe(true);
    });

    test("an unknown reset token is rejected", async () => {
      const token = issueToken().token;
      expect(await peekPasswordResetToken(token)).toBe("invalid");
      expect((await resetPasswordWithToken(token, "Whatever123!")).status).toBe("invalid");
    });

    test("a Google-only account can add a password this way", async () => {
      const googleUser = await createUser({
        email: `${LOCAL_PART}-google-only@example.com`,
        emailVerified: true,
      });
      expect(isGoogleOnlyAccount(googleUser)).toBe(true);

      const mail = await issuePasswordResetLink(googleUser, "en");
      const outcome = await resetPasswordWithToken(extractToken(mail.devLink), "AddedLater123!");
      expect(outcome.status).toBe("success");

      const updated = await findUserByEmail(googleUser.email ?? "");
      expect(isGoogleOnlyAccount(updated!)).toBe(false);
      expect(await verifyPassword("AddedLater123!", updated?.passwordHash ?? null)).toBe(true);
    });

    test("resetting also confirms the address", async () => {
      const pending = await createUser({
        email: `${LOCAL_PART}-unconfirmed@example.com`,
        passwordHash: await hashPassword("Original123!"),
      });
      expect(pending.emailVerifiedAt).toBe(null);

      const mail = await issuePasswordResetLink(pending, "en");
      await resetPasswordWithToken(extractToken(mail.devLink), "Confirmed123!");

      // Opening the emailed link proves inbox control, so the address is verified.
      const updated = await findUserByEmail(pending.email ?? "");
      expect(updated?.emailVerifiedAt !== null).toBe(true);
    });
  });

  describe("Auth DB — Google identity linking", () => {
    test("a Google identity resolves back to the linked account", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const sub = `google-sub-${RUN_ID}`;
      await linkGoogleAccount(user.id, sub);

      const found = await findUserByGoogleId(sub);
      expect(found?.id).toBe(user.id);
    });

    test("linking the same identity twice is idempotent", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const sub = `google-sub-${RUN_ID}`;
      await linkGoogleAccount(user.id, sub);
      await linkGoogleAccount(user.id, sub);

      const rows = await db
        .select()
        .from(oauth_accounts)
        .where(
          and(eq(oauth_accounts.provider, "google"), eq(oauth_accounts.providerAccountId, sub))
        );
      expect(rows.length).toBe(1);
    });

    test("an unknown Google identity resolves to nothing", async () => {
      expect(await findUserByGoogleId(`no-such-sub-${RUN_ID}`)).toBe(null);
    });
  });

  describe("Auth DB — sessions", () => {
    test("a live session is found by its token digest", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const { token, hash } = issueToken();
      await db.insert(sessions).values({
        id: hash,
        userId: user.id,
        expiresAt: new Date(Date.now() + 60_000),
      });

      const rows = await db
        .select({ user: users })
        .from(sessions)
        .innerJoin(users, eq(sessions.userId, users.id))
        .where(and(eq(sessions.id, hashToken(token)), gt(sessions.expiresAt, new Date())));

      expect(rows.length).toBe(1);
      expect(rows[0].user.id).toBe(user.id);
    });

    test("an elapsed session is not returned", async () => {
      const user = await findUserByEmail(BASE_EMAIL);
      if (!user) throw new Error("Fixture user missing");

      const { token, hash } = issueToken();
      await db.insert(sessions).values({
        id: hash,
        userId: user.id,
        expiresAt: new Date(Date.now() - 60_000),
      });

      const rows = await db
        .select()
        .from(sessions)
        .where(and(eq(sessions.id, hashToken(token)), gt(sessions.expiresAt, new Date())));

      expect(rows.length).toBe(0);
    });

    test("deleting the account removes its sessions, tokens and links", async () => {
      const doomed = await createUser({
        email: `${LOCAL_PART}-cascade@example.com`,
        passwordHash: "x",
      });

      await db.insert(sessions).values({
        id: issueToken().hash,
        userId: doomed.id,
        expiresAt: new Date(Date.now() + 60_000),
      });
      await db.insert(email_verification_tokens).values({
        id: issueToken().hash,
        userId: doomed.id,
        email: doomed.email ?? "",
        expiresAt: new Date(Date.now() + 60_000),
      });
      await db.insert(oauth_accounts).values({
        id: newId("oa"),
        userId: doomed.id,
        provider: "google",
        providerAccountId: `cascade-${RUN_ID}`,
      });

      await db.delete(users).where(eq(users.id, doomed.id));

      const leftoverSessions = await db
        .select()
        .from(sessions)
        .where(eq(sessions.userId, doomed.id));
      const leftoverTokens = await db
        .select()
        .from(email_verification_tokens)
        .where(eq(email_verification_tokens.userId, doomed.id));
      const leftoverLinks = await db
        .select()
        .from(oauth_accounts)
        .where(eq(oauth_accounts.userId, doomed.id));

      expect(leftoverSessions.length).toBe(0);
      expect(leftoverTokens.length).toBe(0);
      expect(leftoverLinks.length).toBe(0);
    });
  });
}

async function main() {
  if (!(await isDatabaseAvailable())) {
    console.log(
      [
        "",
        "  SKIPPED — no database reachable at DATABASE_URL.",
        "",
        "  Start one and apply the schema, then re-run:",
        "    docker compose up -d postgres",
        "    npm run db:push",
        "    npm run test:auth",
        "",
      ].join("\n")
    );
    await pool.end();
    return;
  }

  // Leftovers from an interrupted earlier run would break the uniqueness tests.
  await cleanup();
  registerSuites();

  let passed = false;
  try {
    passed = await runAllTests();
  } finally {
    await cleanup();
    await pool.end();
  }

  if (!passed) process.exit(1);
}

main().catch(async (error) => {
  console.error("Auth DB suite crashed:", error);
  await pool.end().catch(() => undefined);
  process.exit(1);
});