gold_land_server/scripts/sqlite-app-schema.mjs

/**
 * Validates that a SQLite file matches Child Gold Land backend schema expectations.
 */
import Database from 'better-sqlite3'

export const EXPECTED_TABLES = [
  'users',
  'user_level_progress',
  'reward_ledger',
  'auth_accounts',
]

/** Keep in sync with src/database/migrations/index.ts */
export const EXPECTED_MIGRATION_IDS = ['001_initial_schema']

/**
 * @param {string} dbPath
 * @returns {{ ok: true } | { ok: false; reason: string }}
 */
export function validateAppSqliteFile(dbPath) {
  let db
  try {
    db = new Database(dbPath, { readonly: true })
  } catch (error) {
    return { ok: false, reason: `cannot open database: ${error instanceof Error ? error.message : error}` }
  }

  try {
    const integrity = db.pragma('integrity_check', { simple: true })
    if (integrity !== 'ok') {
      return { ok: false, reason: `integrity_check failed: ${integrity}` }
    }

    const tables = db
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' AND name != 'schema_migrations'",
      )
      .all()
      .map((r) => r.name)

    for (const name of EXPECTED_TABLES) {
      if (!tables.includes(name)) {
        return { ok: false, reason: `missing table: ${name}` }
      }
    }

    const migTable = db
      .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='schema_migrations'")
      .get()
    if (!migTable) {
      return { ok: false, reason: 'missing schema_migrations table' }
    }

    const applied = db.prepare('SELECT id FROM schema_migrations ORDER BY id').all().map((r) => r.id)
    for (const id of EXPECTED_MIGRATION_IDS) {
      if (!applied.includes(id)) {
        return { ok: false, reason: `missing migration: ${id}` }
      }
    }

    return { ok: true }
  } finally {
    db.close()
  }
}