Spaces:
Paused
Paused
| // Ядро: база данных + миграции. Ведёт Claude. | |
| // Кодекс импортирует { db } и работает через него (API: prepare().get/run/all, exec). | |
| // Используется встроенный node:sqlite (Node >= 22.5), нативная сборка не нужна. | |
| import { DatabaseSync } from 'node:sqlite'; | |
| import { fileURLToPath } from 'node:url'; | |
| import { dirname, join } from 'node:path'; | |
| const __dirname = dirname(fileURLToPath(import.meta.url)); | |
| export const db = new DatabaseSync(join(__dirname, '../../data.sqlite')); | |
| db.exec('PRAGMA journal_mode = WAL'); | |
| db.exec('PRAGMA foreign_keys = ON'); | |
| db.exec(` | |
| CREATE TABLE IF NOT EXISTS users ( | |
| id TEXT PRIMARY KEY, | |
| email TEXT UNIQUE, -- null для Telegram-юзеров | |
| password_hash TEXT, -- null для Telegram-юзеров | |
| telegram_id INTEGER UNIQUE, -- null для email-юзеров | |
| name TEXT NOT NULL, | |
| created_at INTEGER NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS spaces ( | |
| id TEXT PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| type TEXT NOT NULL CHECK (type IN ('couple','family','friends','flat')), | |
| owner_id TEXT NOT NULL REFERENCES users(id), | |
| created_at INTEGER NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS space_members ( | |
| id TEXT PRIMARY KEY, | |
| space_id TEXT NOT NULL REFERENCES spaces(id), | |
| user_id TEXT NOT NULL REFERENCES users(id), | |
| role TEXT NOT NULL CHECK (role IN ('owner','member')), | |
| joined_at INTEGER NOT NULL, | |
| UNIQUE (space_id, user_id) | |
| ); | |
| CREATE TABLE IF NOT EXISTS transactions ( | |
| id TEXT PRIMARY KEY, | |
| space_id TEXT NOT NULL REFERENCES spaces(id), | |
| author_id TEXT NOT NULL REFERENCES users(id), | |
| title TEXT NOT NULL, | |
| amount INTEGER NOT NULL CHECK (amount > 0), | |
| paid_by TEXT NOT NULL REFERENCES users(id), | |
| split TEXT NOT NULL, | |
| created_at INTEGER NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS tasks ( | |
| id TEXT PRIMARY KEY, | |
| space_id TEXT NOT NULL REFERENCES spaces(id), | |
| title TEXT NOT NULL, | |
| assignee_id TEXT REFERENCES users(id), | |
| done INTEGER NOT NULL DEFAULT 0, | |
| due_at INTEGER, | |
| created_at INTEGER NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS posts ( | |
| id TEXT PRIMARY KEY, | |
| space_id TEXT NOT NULL REFERENCES spaces(id), | |
| author_id TEXT NOT NULL REFERENCES users(id), | |
| text TEXT NOT NULL, | |
| created_at INTEGER NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS events ( | |
| id TEXT PRIMARY KEY, | |
| space_id TEXT NOT NULL REFERENCES spaces(id), | |
| title TEXT NOT NULL, | |
| start_at INTEGER NOT NULL, | |
| created_at INTEGER NOT NULL | |
| ); | |
| `); | |
| // Миграция: БД, созданные до появления telegram_id, не имеют этой колонки | |
| // (CREATE TABLE IF NOT EXISTS существующую таблицу не меняет). Добавляем на лету. | |
| const userColumns = db.prepare('PRAGMA table_info(users)').all(); | |
| if (!userColumns.some((c) => c.name === 'telegram_id')) { | |
| db.exec('ALTER TABLE users ADD COLUMN telegram_id INTEGER'); | |
| db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id)'); | |
| } | |