BillTracker/db/schema.sql

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS bills (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  due_day INTEGER NOT NULL CHECK(due_day BETWEEN 1 AND 31),
  override_due_date TEXT,
  bucket TEXT CHECK(bucket IN ('1st', '15th')),
  expected_amount REAL NOT NULL DEFAULT 0,
  interest_rate REAL CHECK(interest_rate IS NULL OR (interest_rate >= 0 AND interest_rate <= 100)),
  billing_cycle TEXT DEFAULT 'monthly' CHECK(billing_cycle IN ('monthly', 'quarterly', 'annually', 'irregular')),
  autopay_enabled INTEGER NOT NULL DEFAULT 0,
  autodraft_status TEXT NOT NULL DEFAULT 'none' CHECK(autodraft_status IN ('none', 'pending', 'assumed_paid', 'confirmed')),
  website TEXT,
  username TEXT,
  account_info TEXT,
  has_2fa INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  notes TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  bill_id INTEGER NOT NULL REFERENCES bills(id) ON DELETE CASCADE,
  amount REAL NOT NULL,
  paid_date TEXT NOT NULL,
  method TEXT,
  notes TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE COLLATE NOCASE,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin', 'user')),
  must_change_password INTEGER NOT NULL DEFAULT 0,
  first_login INTEGER NOT NULL DEFAULT 1,
  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 REFERENCES users(id) ON DELETE CASCADE,
  expires_at TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS notifications (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  bill_id     INTEGER NOT NULL REFERENCES bills(id) ON DELETE CASCADE,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  year        INTEGER NOT NULL,
  month       INTEGER NOT NULL,
  type        TEXT NOT NULL,   -- due_3d | due_1d | due_today | overdue
  sent_date   TEXT NOT NULL DEFAULT (date('now')),
  UNIQUE(bill_id, user_id, year, month, type, sent_date)
);

CREATE INDEX IF NOT EXISTS idx_notifications_lookup ON notifications(bill_id, user_id, year, month);
CREATE INDEX IF NOT EXISTS idx_bills_active ON bills(active);
CREATE INDEX IF NOT EXISTS idx_payments_bill_id ON payments(bill_id);
CREATE INDEX IF NOT EXISTS idx_payments_paid_date ON payments(paid_date);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

CREATE TABLE IF NOT EXISTS monthly_bill_state (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  bill_id       INTEGER NOT NULL REFERENCES bills(id) ON DELETE CASCADE,
  year          INTEGER NOT NULL CHECK(year BETWEEN 2000 AND 2100),
  month         INTEGER NOT NULL CHECK(month BETWEEN 1 AND 12),
  actual_amount REAL,           -- NULL = use bill.expected_amount for this month
  notes         TEXT,           -- month-specific notes, NULL = no notes
  is_skipped    INTEGER NOT NULL DEFAULT 0,  -- 1 = hidden/removed for this month only
  created_at    TEXT DEFAULT (datetime('now')),
  updated_at    TEXT DEFAULT (datetime('now')),
  UNIQUE(bill_id, year, month)
);

CREATE INDEX IF NOT EXISTS idx_monthly_bill_state_lookup
  ON monthly_bill_state(bill_id, year, month);