66 lines
2.6 KiB
SQL
66 lines
2.6 KiB
SQL
-- +up
|
|
CREATE TABLE IF NOT EXISTS crm_contacts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
owner_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
company TEXT NOT NULL DEFAULT '',
|
|
phone TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'lead' CHECK (status IN ('lead', 'customer', 'inactive')),
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS crm_contacts_owner_idx ON crm_contacts(owner_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS crm_contacts_owner_email_uq ON crm_contacts(owner_id, email);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_deals (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
owner_id TEXT NOT NULL,
|
|
contact_id INTEGER REFERENCES crm_contacts(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
value_cents INTEGER NOT NULL DEFAULT 0 CHECK (value_cents >= 0),
|
|
stage TEXT NOT NULL DEFAULT 'qualified' CHECK (stage IN ('qualified', 'proposal', 'won', 'lost')),
|
|
close_date TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS crm_deals_owner_idx ON crm_deals(owner_id);
|
|
CREATE INDEX IF NOT EXISTS crm_deals_contact_idx ON crm_deals(contact_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_activities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
actor_id TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL CHECK (entity_type IN ('contact', 'deal', 'account')),
|
|
entity_id TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
details_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS crm_activities_entity_idx ON crm_activities(entity_type, entity_id);
|
|
|
|
-- Database-backed authorization assignments.
|
|
CREATE TABLE IF NOT EXISTS _wrn_authz_assignment (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
subject_id VARCHAR(255) NOT NULL,
|
|
scope VARCHAR(255) NOT NULL DEFAULT '',
|
|
role VARCHAR(255) NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT _wrn_authz_assignment_unique UNIQUE (subject_id, scope, role)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS _wrn_authz_grant (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
subject_id VARCHAR(255) NOT NULL,
|
|
scope VARCHAR(255) NOT NULL DEFAULT '',
|
|
permission VARCHAR(255) NOT NULL,
|
|
effect VARCHAR(16) NOT NULL CHECK (effect IN ('allow', 'deny')),
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT _wrn_authz_grant_unique UNIQUE (subject_id, scope, permission)
|
|
);
|
|
|
|
-- +down
|
|
DROP TABLE IF EXISTS _wrn_authz_grant;
|
|
DROP TABLE IF EXISTS _wrn_authz_assignment;
|
|
DROP TABLE IF EXISTS crm_activities;
|
|
DROP TABLE IF EXISTS crm_deals;
|
|
DROP TABLE IF EXISTS crm_contacts;
|