server/db.js - master - Poko/kanban

server/db.js
177 строк8 KB
Poko
Update database schema to allow nullable start dates for tasks and enhance task management features
27 июл 2026, 11:15
27 июл 2026, 11:15fa8d641
'use strict';
const { DatabaseSync } = require('node:sqlite');
const fs = require('node:fs');
const path = require('node:path');

const DAY = 86400000;
const DATA_DIR = process.env.DATA_DIR || path.join(__dirname, '..', 'data');
fs.mkdirSync(DATA_DIR, { recursive: true });

// migrate pre-rename installs (potok.db -> neuron.db)
// вместе с базой обязаны переехать -wal/-shm: в WAL могут лежать все данные,
// ещё не слитые в основной файл, — без него база выглядит пустой
const DB_FILE = path.join(DATA_DIR, 'neuron.db');
const OLD_FILE = path.join(DATA_DIR, 'potok.db');
if (!fs.existsSync(DB_FILE) && fs.existsSync(OLD_FILE)) {
  for (const ext of ['', '-wal', '-shm']) {
    if (fs.existsSync(OLD_FILE + ext)) fs.renameSync(OLD_FILE + ext, DB_FILE + ext);
  }
}

const db = new DatabaseSync(DB_FILE);

db.exec(`
PRAGMA journal_mode = WAL;
CREATE TABLE IF NOT EXISTS members (
  id    TEXT PRIMARY KEY,
  name  TEXT NOT NULL,
  role  TEXT NOT NULL DEFAULT '',
  color TEXT NOT NULL,
  pos   INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS tasks (
  id       TEXT PRIMARY KEY,
  title    TEXT NOT NULL,
  user     TEXT,
  col      TEXT NOT NULL DEFAULT 'sprint',
  start    INTEGER,
  dur      INTEGER NOT NULL DEFAULT 5,
  progress INTEGER NOT NULL DEFAULT 0,
  prio     TEXT NOT NULL DEFAULT 'm',
  notes    TEXT NOT NULL DEFAULT '',
  created  INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS accounts (
  login   TEXT PRIMARY KEY,
  hash    TEXT NOT NULL,
  role    TEXT NOT NULL DEFAULT 'user',
  created INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS sessions (
  sid     TEXT PRIMARY KEY,
  login   TEXT NOT NULL,
  created INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v TEXT);
`);

// migration for installs created before the notes feature
if (!db.prepare(`PRAGMA table_info(tasks)`).all().some(c => c.name === 'notes')) {
  db.exec(`ALTER TABLE tasks ADD COLUMN notes TEXT NOT NULL DEFAULT ''`);
}

// migration for installs created before tasks could be dateless (start was NOT NULL)
if (db.prepare(`PRAGMA table_info(tasks)`).all().some(c => c.name === 'start' && c.notnull)) {
  db.exec(`
    CREATE TABLE tasks_new (
      id       TEXT PRIMARY KEY,
      title    TEXT NOT NULL,
      user     TEXT,
      col      TEXT NOT NULL DEFAULT 'sprint',
      start    INTEGER,
      dur      INTEGER NOT NULL DEFAULT 5,
      progress INTEGER NOT NULL DEFAULT 0,
      prio     TEXT NOT NULL DEFAULT 'm',
      notes    TEXT NOT NULL DEFAULT '',
      created  INTEGER NOT NULL DEFAULT 0
    );
    INSERT INTO tasks_new SELECT id, title, user, col, start, dur, progress, prio, notes, created FROM tasks;
    DROP TABLE tasks;
    ALTER TABLE tasks_new RENAME TO tasks;
  `);
}

function todayDay() {
  const d = new Date();
  return Math.round(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()) / DAY);
}

function seed() {
  const done = db.prepare(`SELECT v FROM meta WHERE k = 'seeded'`).get();
  if (done) return;
  db.prepare(`INSERT INTO meta (k, v) VALUES ('seeded', '1')`).run();
  if (process.env.SEED_DEMO !== '1') return;

  const members = [
    ['u1', 'Анна Сорокина', 'Продакт-менеджер', '#6C7CFF'],
    ['u2', 'Максим Ветров', 'Бэкенд-разработчик', '#17B890'],
    ['u3', 'Ольга Ким', 'Продуктовый дизайнер', '#F2A65A'],
    ['u4', 'Денис Царёв', 'DevOps-инженер', '#E36FA0'],
    ['u5', 'Игорь Лапин', 'Фронтенд-разработчик', '#5BB8E8'],
  ];
  const insM = db.prepare(`INSERT INTO members (id, name, role, color, pos) VALUES (?, ?, ?, ?, ?)`);
  members.forEach((m, i) => insM.run(m[0], m[1], m[2], m[3], i));

  // start offsets are relative to the design's window base (today - 10 days)
  const base = todayDay() - 10;
  const tasks = [
    ['t1', 'Дизайн-система: токены и сетка', 'u3', 'done', 0, 5, 100, 'm'],
    ['t2', 'Флоу онбординга', 'u3', 'test', 4, 6, 80, 'h'],
    ['t3', 'API авторизации и сессии', 'u2', 'dev', 6, 7, 55, 'h'],
    ['t4', 'Интеграция платежей', 'u2', 'sprint', 14, 8, 0, 'h'],
    ['t5', 'Экран аналитики', 'u5', 'dev', 8, 6, 45, 'm'],
    ['t6', 'Пуш-уведомления', 'u4', 'global', 16, 5, 0, 'l'],
    ['t7', 'Настройка CI/CD', 'u4', 'done', 2, 4, 100, 'm'],
    ['t8', 'A/B тест лендинга', 'u1', 'test', 7, 4, 90, 'm'],
    ['t9', 'Мобильная адаптация', 'u5', 'dev', 9, 9, 30, 'm'],
    ['t10', 'Документация API', 'u2', 'global', 18, 6, 0, 'l'],
    ['t11', 'Ретро и роадмап Q3', 'u1', 'sprint', 10, 3, 60, 'l'],
    ['t12', 'Бета-релиз 0.9', 'u1', 'global', 24, 4, 0, 'h'],
    ['t13', 'Рефакторинг state-стора', 'u5', 'done', 1, 3, 100, 'm'],
    ['t14', 'Тёмная тема интерфейса', 'u3', 'global', 20, 5, 0, 'm'],
  ];
  const insT = db.prepare(
    `INSERT INTO tasks (id, title, user, col, start, dur, progress, prio, created) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
  );
  tasks.forEach(t => insT.run(t[0], t[1], t[2], t[3], base + t[4], t[5], t[6], t[7], Date.now()));
}
seed();

// prune sessions older than 30 days
db.prepare(`DELETE FROM sessions WHERE created < ?`).run(Date.now() - 30 * DAY);

const q = {
  members: db.prepare(`SELECT id, name, role, color FROM members ORDER BY pos, rowid`),
  tasks: db.prepare(`SELECT id, title, user, col, start, dur, progress, prio, notes FROM tasks ORDER BY created, rowid`),
  memberById: db.prepare(`SELECT id FROM members WHERE id = ?`),
  maxPos: db.prepare(`SELECT COALESCE(MAX(pos), -1) AS p FROM members`),
  insMember: db.prepare(`INSERT INTO members (id, name, role, color, pos) VALUES (?, ?, ?, ?, ?)`),
  delMember: db.prepare(`DELETE FROM members WHERE id = ?`),
  reassign: db.prepare(`UPDATE tasks SET user = ? WHERE user = ?`),
  insTask: db.prepare(
    `INSERT INTO tasks (id, title, user, col, start, dur, progress, prio, notes, created) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
  ),
  delTask: db.prepare(`DELETE FROM tasks WHERE id = ?`),
  taskById: db.prepare(`SELECT id FROM tasks WHERE id = ?`),

  accounts: db.prepare(`SELECT login, role, created FROM accounts ORDER BY created, rowid`),
  accountByLogin: db.prepare(`SELECT login, hash, role FROM accounts WHERE login = ?`),
  countAccounts: db.prepare(`SELECT COUNT(*) AS n FROM accounts`),
  countAdmins: db.prepare(`SELECT COUNT(*) AS n FROM accounts WHERE role = 'admin'`),
  insAccount: db.prepare(`INSERT INTO accounts (login, hash, role, created) VALUES (?, ?, ?, ?)`),
  updAccountHash: db.prepare(`UPDATE accounts SET hash = ? WHERE login = ?`),
  updAccountRole: db.prepare(`UPDATE accounts SET role = ? WHERE login = ?`),
  delAccount: db.prepare(`DELETE FROM accounts WHERE login = ?`),

  sessionBySid: db.prepare(
    `SELECT s.sid AS sid, s.login AS login, a.role AS role
     FROM sessions s JOIN accounts a ON a.login = s.login WHERE s.sid = ?`
  ),
  insSession: db.prepare(`INSERT INTO sessions (sid, login, created) VALUES (?, ?, ?)`),
  delSession: db.prepare(`DELETE FROM sessions WHERE sid = ?`),
  delSessionsByLogin: db.prepare(`DELETE FROM sessions WHERE login = ?`),
  delOtherSessions: db.prepare(`DELETE FROM sessions WHERE login = ? AND sid <> ?`),
};

function getState() {
  return { members: q.members.all(), tasks: q.tasks.all() };
}

function patchRow(table, id, patch) {
  const keys = Object.keys(patch);
  if (!keys.length) return;
  const sets = keys.map(k => `${k} = ?`).join(', ');
  db.prepare(`UPDATE ${table} SET ${sets} WHERE id = ?`).run(...keys.map(k => patch[k]), id);
}

module.exports = { db, q, getState, patchRow, todayDay };