90 lines
5.5 KiB
TypeScript
90 lines
5.5 KiB
TypeScript
import Database from "better-sqlite3";
|
|
import { mkdirSync } from "node:fs";
|
|
import { dirname } from "node:path";
|
|
|
|
const path = process.env.HUB_BUILD === "1" ? ":memory:" : (process.env.DATABASE_PATH || "./data/hub.db");
|
|
if (path !== ":memory:") mkdirSync(dirname(path), { recursive: true });
|
|
export const db = new Database(path);
|
|
db.pragma("journal_mode = WAL");
|
|
db.pragma("foreign_keys = ON");
|
|
db.pragma("busy_timeout = 5000");
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, disabled INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS sources (
|
|
id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL, base_url TEXT NOT NULL, token_encrypted TEXT NOT NULL, remote_user TEXT,
|
|
webhook_supported INTEGER NOT NULL DEFAULT 0, sync_status TEXT NOT NULL DEFAULT 'pending', last_synced_at TEXT, last_error TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id, base_url)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id INTEGER PRIMARY KEY, source_id INTEGER REFERENCES sources(id) ON DELETE SET NULL,
|
|
author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, remote_memo_name TEXT, content TEXT NOT NULL,
|
|
visibility TEXT NOT NULL DEFAULT 'PUBLIC', tags_json TEXT NOT NULL DEFAULT '[]', attachments_json TEXT NOT NULL DEFAULT '[]',
|
|
origin TEXT NOT NULL DEFAULT 'memos', remote_created_at TEXT, remote_updated_at TEXT, sync_status TEXT NOT NULL DEFAULT 'synced',
|
|
hidden INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(source_id, remote_memo_name)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id INTEGER PRIMARY KEY, post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, content TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, hidden INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS reactions (
|
|
post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
emoji TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(post_id, user_id, emoji)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS reports (
|
|
id INTEGER PRIMARY KEY, post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE, reporter_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
reason TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, resolved INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS sync_jobs (
|
|
id INTEGER PRIMARY KEY, source_id INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL, payload_json TEXT, status TEXT NOT NULL DEFAULT 'queued', attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, run_after TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS source_members (
|
|
source_id INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL DEFAULT 'member', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY(source_id, user_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS posts_public_idx ON posts(visibility, hidden, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS sync_jobs_idx ON sync_jobs(status, run_after);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS source_remote_identity_unique ON sources(base_url, remote_user) WHERE remote_user IS NOT NULL;
|
|
`);
|
|
|
|
db.exec("INSERT OR IGNORE INTO source_members(source_id,user_id,role) SELECT id,user_id,'owner' FROM sources");
|
|
|
|
const sourceColumns = db.prepare("PRAGMA table_info(sources)").all() as { name: string }[];
|
|
if (!sourceColumns.some((column) => column.name === "webhook_secret_hash")) {
|
|
db.exec("ALTER TABLE sources ADD COLUMN webhook_secret_hash TEXT");
|
|
}
|
|
if (!sourceColumns.some((column) => column.name === "last_webhook_at")) {
|
|
db.exec("ALTER TABLE sources ADD COLUMN last_webhook_at TEXT");
|
|
}
|
|
|
|
const admin = process.env.ADMIN_USERNAME;
|
|
const adminPassword = process.env.ADMIN_PASSWORD;
|
|
if (admin && adminPassword) {
|
|
// Keep bootstrap safe when Next preloads multiple route modules concurrently.
|
|
const bcrypt = require("bcryptjs");
|
|
db.prepare("INSERT OR IGNORE INTO users(username, password_hash, role) VALUES (?, ?, 'admin')").run(admin, bcrypt.hashSync(adminPassword, 12));
|
|
}
|
|
|
|
const seedUrl = process.env.SEED_MEMOS_URL?.replace(/\/$/, "");
|
|
const seedToken = process.env.SEED_MEMOS_TOKEN;
|
|
if (admin && seedUrl && seedToken) {
|
|
const user = db.prepare("SELECT id FROM users WHERE username=?").get(admin) as { id: number } | undefined;
|
|
const source = db.prepare("SELECT id FROM sources WHERE user_id=? AND base_url=?").get(user?.id, seedUrl) as { id: number } | undefined;
|
|
if (user && !source) {
|
|
const { encrypt } = require("./crypto") as typeof import("./crypto");
|
|
db.prepare("INSERT OR IGNORE INTO sources(user_id,name,base_url,token_encrypted,sync_status) VALUES(?,?,?,?, 'queued')").run(user.id, process.env.SEED_MEMOS_NAME || "Initial Memos", seedUrl, encrypt(seedToken));
|
|
const inserted = db.prepare("SELECT id FROM sources WHERE user_id=? AND base_url=?").get(user.id, seedUrl) as { id: number };
|
|
db.prepare("INSERT INTO sync_jobs(source_id,kind) SELECT ?, 'pull' WHERE NOT EXISTS (SELECT 1 FROM sync_jobs WHERE source_id=? AND kind='pull' AND status IN ('queued','running'))").run(inserted.id, inserted.id);
|
|
}
|
|
}
|