47 lines
1.4 KiB
TypeScript
47 lines
1.4 KiB
TypeScript
import Database from 'better-sqlite3';
|
|
import path from 'path';
|
|
import fs from 'fs';
|
|
|
|
const DATA_DIR = process.env.DATA_DIR ?? path.join(process.cwd(), '.data');
|
|
const UPLOADS_DIR = path.join(DATA_DIR, 'uploads');
|
|
const DB_PATH = path.join(DATA_DIR, 'tally.db');
|
|
|
|
// Ensure directories exist
|
|
fs.mkdirSync(UPLOADS_DIR, { recursive: true });
|
|
|
|
const db = new Database(DB_PATH, { timeout: 5000 });
|
|
|
|
// Performance: WAL mode for concurrent reads + writes
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
// Schema migrations
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS groups (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
order_index INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS counters (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
value INTEGER NOT NULL DEFAULT 0,
|
|
image_path TEXT,
|
|
group_id INTEGER REFERENCES groups(id) ON DELETE SET NULL,
|
|
order_index INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
counter_id INTEGER NOT NULL REFERENCES counters(id) ON DELETE CASCADE,
|
|
delta INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_events_counter ON events(counter_id);
|
|
CREATE INDEX IF NOT EXISTS idx_events_date ON events(created_at);
|
|
`);
|
|
|
|
export { UPLOADS_DIR };
|
|
export default db;
|