46 lines
1.2 KiB
TypeScript
46 lines
1.2 KiB
TypeScript
import Database from 'better-sqlite3'
|
|
import path from 'path'
|
|
import { fileURLToPath } from 'url'
|
|
import fs from 'fs'
|
|
|
|
const __dirname = path.dirname(fileURLToPath(import.meta.url))
|
|
const DB_PATH = path.join(__dirname, '..', 'data', 'dashboard.db')
|
|
|
|
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true })
|
|
|
|
const db = new Database(DB_PATH)
|
|
db.pragma('journal_mode = WAL')
|
|
db.pragma('foreign_keys = ON')
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS sites (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
access_token TEXT NOT NULL,
|
|
site_id INTEGER NOT NULL,
|
|
site_url TEXT NOT NULL,
|
|
user_info TEXT DEFAULT '{}',
|
|
is_admin INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
expires_at TEXT NOT NULL,
|
|
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
|
|
);
|
|
`)
|
|
|
|
// Migration: add is_admin column if missing
|
|
try {
|
|
db.exec(`ALTER TABLE sessions ADD COLUMN is_admin INTEGER DEFAULT 0`)
|
|
} catch {
|
|
// column already exists
|
|
}
|
|
|
|
export default db
|