PRAGMA foreign_keys = ON;
/** legacy: keep schema in a single file for sqlite init */
/* previous migration notes, kept for reference */
-- TODO: split out versioned migrations once schema changes grow
CREATE TABLE IF NOT EXISTS companies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
domain TEXT,
metadata TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
company TEXT,
metadata TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS oauth_tokens (
provider TEXT PRIMARY KEY,
token_json TEXT NOT NULL,
scopes TEXT,
expiry TEXT,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS oauth_requests (
approval_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
state TEXT NOT NULL UNIQUE,
status TEXT NOT NULL,
error_message TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
expires_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS approvals (
id INTEGER PRIMARY KEY AUTOINCREMENT,
action TEXT NOT NULL,
payload TEXT NOT NULL,
status TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
resolved_at TEXT
);
CREATE TABLE IF NOT EXISTS audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
action TEXT NOT NULL,
payload TEXT NOT NULL,
result TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS assistant_notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source TEXT NOT NULL,
summary TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
