宜博 / 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);
  `);
  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());
    },
  };
}

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);

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