76 lines
2.3 KiB
SQL
76 lines
2.3 KiB
SQL
-- SQLite init (reference)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
timezone TEXT NOT NULL DEFAULT 'UTC',
|
|
created_at DATETIME,
|
|
updated_at DATETIME
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
color TEXT,
|
|
created_at DATETIME,
|
|
updated_at DATETIME,
|
|
FOREIGN KEY(user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
category_id INTEGER NOT NULL,
|
|
quantity REAL NOT NULL,
|
|
unit_price REAL NOT NULL,
|
|
total_value REAL NOT NULL,
|
|
currency TEXT NOT NULL,
|
|
expiry_date DATETIME,
|
|
note TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at DATETIME,
|
|
updated_at DATETIME,
|
|
FOREIGN KEY(user_id) REFERENCES users(id),
|
|
FOREIGN KEY(category_id) REFERENCES categories(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reminders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
asset_id INTEGER NOT NULL,
|
|
remind_at DATETIME NOT NULL,
|
|
channel TEXT NOT NULL DEFAULT 'in_app',
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
dedupe_key TEXT NOT NULL UNIQUE,
|
|
retry_count INTEGER NOT NULL DEFAULT 0,
|
|
next_retry_at DATETIME,
|
|
last_error TEXT,
|
|
sent_at DATETIME,
|
|
created_at DATETIME,
|
|
updated_at DATETIME,
|
|
FOREIGN KEY(user_id) REFERENCES users(id),
|
|
FOREIGN KEY(asset_id) REFERENCES assets(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id INTEGER NOT NULL,
|
|
action TEXT NOT NULL,
|
|
before_json TEXT,
|
|
after_json TEXT,
|
|
created_at DATETIME,
|
|
FOREIGN KEY(user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_categories_user_id ON categories(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_user_status_category ON assets(user_id, status, category_id);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_expiry_date ON assets(expiry_date);
|
|
CREATE INDEX IF NOT EXISTS idx_reminders_status_remind_at ON reminders(status, remind_at);
|
|
CREATE INDEX IF NOT EXISTS idx_reminders_next_retry_status ON reminders(next_retry_at, status);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_user_entity_action ON audit_logs(user_id, entity_type, action);
|