宜博 / h3livestream

// SQLite-backed store for users/sessions (dev-plan-3.md T18; schema:
// dressup-dance-room-mvcp.md M.1/M.10). Separate from store.js's JSON
// files on purpose — architecture.md 1.4 flags credential storage as the
// trigger to move off JSON, other stores (characters/garments/dance-refs/
// library) are unaffected and stay exactly as they are.
//
// node:sqlite (DatabaseSync) is a Node 24 built-in (experimental but
// functional — confirmed working here) and node:crypto's scryptSync
// covers password hashing, so this needs zero new npm dependencies and no
// package.json.
const path = require("path");
const crypto = require("crypto");
const { DatabaseSync } = require("node:sqlite");

function openDb(filePath) {
  const db = new DatabaseSync(filePath);
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT NOT NULL UNIQUE,
      passwordHash TEXT NOT NULL,
      emailVerified INTEGER NOT NULL DEFAULT 0,
      role TEXT NOT NULL DEFAULT 'viewer',
      oauthProviders TEXT NOT NULL DEFAULT '[]',
      nickname TEXT,
      legacyUid TEXT,
      ageConfirmed INTEGER NOT NULL DEFAULT 0,
      ageConfirmedAt INTEGER,
      creditBalance INTEGER NOT NULL DEFAULT 0,
      createdAt INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      userId TEXT NOT NULL REFERENCES users(id),
      kind TEXT NOT NULL,
      token TEXT NOT NULL UNIQUE,
      expiresAt INTEGER NOT NULL,
      createdAt INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
    CREATE INDEX IF NOT EXISTS idx_sessions_userId ON sessions(userId);
    CREATE TABLE IF NOT EXISTS credit_ledger (
      id TEXT PRIMARY KEY,
      userId TEXT NOT NULL REFERENCES users(id),
      delta INTEGER NOT NULL,
      reason TEXT NOT NULL,
      relatedAssetId TEXT,
      balanceAfter INTEGER NOT NULL,
      paymentRef TEXT,
      createdAt INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_userId ON credit_ledger(userId);
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_paymentRef ON credit_ledger(paymentRef);
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_relatedAssetId_reason ON credit_ledger(relatedAssetId, reason);
  `);
  return db;
}

function rowToUser(row) {
  if (!row) return null;
  return {
    ...row,
    emailVerified: !!row.emailVerified,
    ageConfirmed: !!row.ageConfirmed,
    oauthProviders: JSON.parse(row.oauthProviders),
  };
}

// Fields a caller is allowed to patch via users.update() — deliberately a
// fixed list rather than trusting arbitrary keys, since they get spliced
// straight into a SQL UPDATE's column list.
const USER_PATCHABLE_FIELDS = [
  "passwordHash",
  "emailVerified",
  "role",
  "oauthProviders",
  "nickname",
  "legacyUid",
  "ageConfirmed",
  "ageConfirmedAt",
  "creditBalance",
];

function createUsersStore(db) {
  const insert = db.prepare(`
    INSERT INTO users (id, email, passwordHash, emailVerified, role, oauthProviders, nickname, legacyUid, ageConfirmed, ageConfirmedAt, creditBalance, createdAt)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const byId = db.prepare(`SELECT * FROM users WHERE id = ?`);
  const byEmail = db.prepare(`SELECT * FROM users WHERE email = ?`);

  return {
    // Only email/passwordHash are required — everything else (nickname/
    // legacyUid/ageConfirmed/ageConfirmedAt) is how T21 carries an existing
    // anonymous viewer_uid identity onto the new account at registration
    // time, so callers pass what they already have off the cookie.
    create({ email, passwordHash, nickname = null, legacyUid = null, ageConfirmed = false, ageConfirmedAt = null }) {
      const user = {
        id: crypto.randomUUID(),
        email,
        passwordHash,
        emailVerified: false,
        role: "viewer",
        oauthProviders: [],
        nickname,
        legacyUid,
        ageConfirmed,
        ageConfirmedAt,
        creditBalance: 0,
        createdAt: Date.now(),
      };
      try {
        insert.run(
          user.id,
          user.email,
          user.passwordHash,
          user.emailVerified ? 1 : 0,
          user.role,
          JSON.stringify(user.oauthProviders),
          user.nickname,
          user.legacyUid,
          user.ageConfirmed ? 1 : 0,
          user.ageConfirmedAt,
          user.creditBalance,
          user.createdAt
        );
      } catch (err) {
        if (err.code === "ERR_SQLITE_ERROR" && /UNIQUE constraint failed: users\.email/.test(err.message)) {
          const conflict = new Error("email already registered");
          conflict.code = "EMAIL_TAKEN";
          throw conflict;
        }
        throw err;
      }
      return user;
    },
    get(id) {
      return rowToUser(byId.get(id));
    },
    findByEmail(email) {
      return rowToUser(byEmail.get(email));
    },
    update(id, patch) {
      const fields = Object.keys(patch).filter((k) => USER_PATCHABLE_FIELDS.includes(k));
      if (fields.length === 0) return this.get(id);
      const setClause = fields.map((f) => `${f} = ?`).join(", ");
      const values = fields.map((f) => {
        const v = patch[f];
        if (f === "emailVerified" || f === "ageConfirmed") return v ? 1 : 0;
        if (f === "oauthProviders") return JSON.stringify(v);
        return v;
      });
      db.prepare(`UPDATE users SET ${setClause} WHERE id = ?`).run(...values, id);
      return this.get(id);
    },
  };
}

function createSessionsStore(db) {
  const insert = db.prepare(`INSERT INTO sessions (id, userId, kind, token, expiresAt, createdAt) VALUES (?, ?, ?, ?, ?, ?)`);
  const byToken = db.prepare(`SELECT * FROM sessions WHERE token = ?`);
  const del = db.prepare(`DELETE FROM sessions WHERE token = ?`);
  const delExpired = db.prepare(`DELETE FROM sessions WHERE expiresAt < ?`);

  return {
    create({ userId, kind, expiresAt, token = crypto.randomBytes(32).toString("hex") }) {
      const session = { id: crypto.randomUUID(), userId, kind, token, expiresAt, createdAt: Date.now() };
      insert.run(session.id, session.userId, session.kind, session.token, session.expiresAt, session.createdAt);
      return session;
    },
    // Lazily reaps the row if it's expired, same "no separate cron" spirit
    // as the rest of this codebase (e.g. store.js has no TTL sweep either).
    findByToken(token) {
      const row = byToken.get(token);
      if (!row) return null;
      if (row.expiresAt < Date.now()) {
        del.run(token);
        return null;
      }
      return row;
    },
    revoke(token) {
      del.run(token);
    },
    sweepExpired() {
      delExpired.run(Date.now());
    },
  };
}

function rowToLedgerEntry(row) {
  return row || null;
}

// dev-plan-4.md T23: credit_ledger (schema: dressup-dance-room-mvcp.md M.2).
// architecture.md 1.4 flags "扣积分 + 创建生成任务 + 扣减片段配额" as
// needing real atomicity — spend/refund/topup below each read-modify-write
// users.creditBalance inside a SQLite transaction so a crash mid-operation
// can't leave the ledger and the balance disagreeing. node:sqlite's
// DatabaseSync is synchronous and single-connection, so within one process
// there's no interleaving between these calls either way (no `await`
// anywhere in the critical section) — the transaction is defense against a
// mid-function throw leaving a half-applied row, not against concurrent
// callers (there are none, by construction).
function createCreditLedgerStore(db, users) {
  const insert = db.prepare(`
    INSERT INTO credit_ledger (id, userId, delta, reason, relatedAssetId, balanceAfter, paymentRef, createdAt)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const byPaymentRef = db.prepare(`SELECT * FROM credit_ledger WHERE paymentRef = ?`);
  // createdAt alone can tie (millisecond resolution, several writes in one
  // tick) — rowid as a tiebreaker keeps ties in insertion order instead of
  // an arbitrary one.
  const byUser = db.prepare(`SELECT * FROM credit_ledger WHERE userId = ? ORDER BY createdAt DESC, rowid DESC`);
  const byRelatedAssetAndReason = db.prepare(`SELECT * FROM credit_ledger WHERE relatedAssetId = ? AND reason = ?`);

  // Shared by spend/refund/topup: apply `delta` to users.creditBalance and
  // insert the matching ledger row, both inside the caller's transaction.
  function applyDelta({ userId, delta, reason, relatedAssetId = null, paymentRef = null }) {
    const user = users.get(userId);
    if (!user) throw Object.assign(new Error("no such user"), { code: "NO_SUCH_USER" });
    const balanceAfter = user.creditBalance + delta;
    if (balanceAfter < 0) throw Object.assign(new Error("insufficient credit balance"), { code: "INSUFFICIENT_BALANCE" });
    const row = {
      id: crypto.randomUUID(),
      userId,
      delta,
      reason,
      relatedAssetId,
      balanceAfter,
      paymentRef,
      createdAt: Date.now(),
    };
    insert.run(row.id, row.userId, row.delta, row.reason, row.relatedAssetId, row.balanceAfter, row.paymentRef, row.createdAt);
    db.prepare(`UPDATE users SET creditBalance = ? WHERE id = ?`).run(balanceAfter, userId);
    return row;
  }

  function inTransaction(fn) {
    db.exec("BEGIN");
    try {
      const result = fn();
      db.exec("COMMIT");
      return result;
    } catch (err) {
      db.exec("ROLLBACK");
      throw err;
    }
  }

  return {
    // Throws INSUFFICIENT_BALANCE (not a negative balance) when the user
    // can't afford `amount`.
    spend({ userId, amount, reason, relatedAssetId = null }) {
      if (amount <= 0) throw new Error("spend amount must be positive");
      return inTransaction(() => rowToLedgerEntry(applyDelta({ userId, delta: -amount, reason, relatedAssetId })));
    },
    refund({ userId, amount, reason, relatedAssetId = null }) {
      if (amount <= 0) throw new Error("refund amount must be positive");
      return inTransaction(() => rowToLedgerEntry(applyDelta({ userId, delta: amount, reason, relatedAssetId })));
    },
    // paymentRef dedup lives here (not spend/refund) because it's the only
    // reason it's ever set (Stripe's event id, T24) — Stripe guarantees
    // at-least-once webhook delivery, so a second delivery of the same
    // event must be a no-op, not a second credit. Returns the *existing*
    // row on a duplicate rather than throwing, since "already applied" is
    // success from the caller's (webhook handler's) point of view.
    topup({ userId, amount, reason = "topup", paymentRef = null }) {
      if (amount <= 0) throw new Error("topup amount must be positive");
      return inTransaction(() => {
        if (paymentRef) {
          const existing = byPaymentRef.get(paymentRef);
          if (existing) return rowToLedgerEntry(existing);
        }
        return rowToLedgerEntry(applyDelta({ userId, delta: amount, reason, paymentRef }));
      });
    },
    listByUser(userId) {
      return byUser.all(userId);
    },
    // T27's async refund-on-generation-failure guard: ensureDanceSchedule's
    // tick can in principle observe the same "generating" -> "failed"
    // transition twice across a crash/restart (see server.js's comment at
    // the call site) — check here rather than trusting the caller to only
    // call refund() once.
    findByRelatedAssetAndReason(relatedAssetId, reason) {
      return byRelatedAssetAndReason.all(relatedAssetId, reason);
    },
  };
}

const DB_PATH = process.env.DB_PATH || path.join(__dirname, "data.sqlite");
const db = openDb(DB_PATH);
const users = createUsersStore(db);
const sessions = createSessionsStore(db);
const creditLedger = createCreditLedgerStore(db, users);

module.exports = { db, users, sessions, creditLedger, openDb, createUsersStore, createSessionsStore, createCreditLedgerStore };