💡 Staff login uses Admin Email + Mobile + Password.
Staff:
-- CUSTOMERS
CREATE TABLE IF NOT EXISTS opm_customers (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
code TEXT,
mobile TEXT,
email TEXT,
area TEXT,
address TEXT,
website TEXT,
status TEXT DEFAULT 'active',
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_cust_code ON opm_customers(code);
CREATE INDEX IF NOT EXISTS opm_cust_name ON opm_customers(name);
-- CONTACTS
CREATE TABLE IF NOT EXISTS opm_contacts (
id TEXT PRIMARY KEY,
"customerId" TEXT NOT NULL,
name TEXT,
dept TEXT,
mobile TEXT,
email TEXT,
status TEXT DEFAULT 'active',
"createdAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_ct_cust ON opm_contacts("customerId");
-- COMMUNICATIONS / COMMENTS
CREATE TABLE IF NOT EXISTS opm_communications (
id TEXT PRIMARY KEY,
"customerId" TEXT NOT NULL,
"staffId" TEXT,
text TEXT,
mode TEXT DEFAULT 'call',
status TEXT DEFAULT 'Followup',
"followupDate" TEXT,
"createdAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_comm_cust ON opm_communications("customerId");
CREATE INDEX IF NOT EXISTS opm_comm_date ON opm_communications("createdAt");
-- INVOICES
CREATE TABLE IF NOT EXISTS opm_invoices (
id TEXT PRIMARY KEY,
"customerId" TEXT NOT NULL,
"invoiceNo" TEXT,
"invoiceDate" TEXT,
amount NUMERIC DEFAULT 0,
"uploadedAt" TEXT,
"uploadedBy" TEXT,
remarks TEXT
);
CREATE INDEX IF NOT EXISTS opm_inv_cust ON opm_invoices("customerId");
CREATE INDEX IF NOT EXISTS opm_inv_date ON opm_invoices("invoiceDate");
-- STAFF
CREATE TABLE IF NOT EXISTS opm_staff (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
username TEXT UNIQUE,
password TEXT,
role TEXT DEFAULT 'staff',
mobile TEXT,
dept TEXT,
status TEXT DEFAULT 'active',
"createdAt" TEXT,
"resetToken" TEXT,
"resetExpiresAt" TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS opm_staff_email ON opm_staff(email) WHERE email IS NOT NULL;
-- ASSIGNMENTS
CREATE TABLE IF NOT EXISTS opm_assignments (
id TEXT PRIMARY KEY,
"customerId" TEXT NOT NULL,
"staffId" TEXT NOT NULL,
"assignedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_asn_cust ON opm_assignments("customerId");
CREATE INDEX IF NOT EXISTS opm_asn_staff ON opm_assignments("staffId");
-- DISABLE RLS (PHP uses service_role which bypasses RLS)
ALTER TABLE opm_customers DISABLE ROW LEVEL SECURITY;
ALTER TABLE opm_contacts DISABLE ROW LEVEL SECURITY;
ALTER TABLE opm_communications DISABLE ROW LEVEL SECURITY;
ALTER TABLE opm_invoices DISABLE ROW LEVEL SECURITY;
ALTER TABLE opm_staff DISABLE ROW LEVEL SECURITY;
ALTER TABLE opm_assignments DISABLE ROW LEVEL SECURITY;
-- AI TOKEN USAGE TRACKING CREATE TABLE IF NOT EXISTS finops_ai_usage ( id BIGSERIAL PRIMARY KEY, owner_id TEXT NOT NULL, provider TEXT NOT NULL, -- 'claude' or 'openai' model TEXT, input_tokens INTEGER DEFAULT 0, output_tokens INTEGER DEFAULT 0, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX IF NOT EXISTS idx_finops_ai_usage_owner ON finops_ai_usage (owner_id); CREATE INDEX IF NOT EXISTS idx_finops_ai_usage_ts ON finops_ai_usage (created_at DESC); ALTER TABLE finops_ai_usage DISABLE ROW LEVEL SECURITY;
-- TALLY LEDGER BALANCES
CREATE TABLE IF NOT EXISTS opm_tally_ledgers (
id TEXT PRIMARY KEY,
"ownerId" TEXT NOT NULL,
"companyName" TEXT NOT NULL,
"ledgerName" TEXT NOT NULL,
"ledgerGroup" TEXT,
"openingBalance" NUMERIC(15,2) DEFAULT 0,
"closingBalance" NUMERIC(15,2) DEFAULT 0,
"debitCredit" TEXT DEFAULT 'Dr',
"syncedAt" TEXT,
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_tl_owner ON opm_tally_ledgers("ownerId");
CREATE INDEX IF NOT EXISTS opm_tl_company ON opm_tally_ledgers("companyName");
ALTER TABLE opm_tally_ledgers DISABLE ROW LEVEL SECURITY;
-- TALLY VOUCHERS
CREATE TABLE IF NOT EXISTS opm_tally_vouchers (
id TEXT PRIMARY KEY,
"ownerId" TEXT NOT NULL,
"companyName" TEXT NOT NULL,
"voucherType" TEXT NOT NULL,
"voucherNumber" TEXT,
"voucherDate" TEXT,
"partyName" TEXT,
"ledgerName" TEXT,
narration TEXT,
amount NUMERIC(15,2) DEFAULT 0,
"debitCredit" TEXT DEFAULT 'Dr',
"syncedAt" TEXT,
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_tv_owner ON opm_tally_vouchers("ownerId");
CREATE INDEX IF NOT EXISTS opm_tv_company ON opm_tally_vouchers("companyName");
CREATE INDEX IF NOT EXISTS opm_tv_type ON opm_tally_vouchers("voucherType");
ALTER TABLE opm_tally_vouchers DISABLE ROW LEVEL SECURITY;
-- TALLY STOCK / INVENTORY
CREATE TABLE IF NOT EXISTS opm_tally_stock (
id TEXT PRIMARY KEY,
"ownerId" TEXT NOT NULL,
"companyName" TEXT NOT NULL,
"itemName" TEXT NOT NULL,
"stockGroup" TEXT,
unit TEXT,
"openingQty" NUMERIC(15,3) DEFAULT 0,
"openingRate" NUMERIC(15,2) DEFAULT 0,
"openingValue" NUMERIC(15,2) DEFAULT 0,
"closingQty" NUMERIC(15,3) DEFAULT 0,
"closingRate" NUMERIC(15,2) DEFAULT 0,
"closingValue" NUMERIC(15,2) DEFAULT 0,
"syncedAt" TEXT,
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_ts_owner ON opm_tally_stock("ownerId");
CREATE INDEX IF NOT EXISTS opm_ts_company ON opm_tally_stock("companyName");
ALTER TABLE opm_tally_stock DISABLE ROW LEVEL SECURITY;
-- TALLY TRIAL BALANCE
CREATE TABLE IF NOT EXISTS opm_tally_trialbalance (
id TEXT PRIMARY KEY,
"ownerId" TEXT NOT NULL,
"companyName" TEXT NOT NULL,
"ledgerName" TEXT NOT NULL,
"ledgerGroup" TEXT,
"openingDebit" NUMERIC(15,2) DEFAULT 0,
"openingCredit" NUMERIC(15,2) DEFAULT 0,
"transactionDebit" NUMERIC(15,2) DEFAULT 0,
"transactionCredit" NUMERIC(15,2) DEFAULT 0,
"closingDebit" NUMERIC(15,2) DEFAULT 0,
"closingCredit" NUMERIC(15,2) DEFAULT 0,
"syncedAt" TEXT,
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_tb_owner ON opm_tally_trialbalance("ownerId");
ALTER TABLE opm_tally_trialbalance DISABLE ROW LEVEL SECURITY;
-- TALLY SYNC LOG
CREATE TABLE IF NOT EXISTS opm_tally_sync_log (
id TEXT PRIMARY KEY,
"ownerId" TEXT NOT NULL,
"companyName" TEXT NOT NULL,
"syncType" TEXT NOT NULL,
status TEXT DEFAULT 'ok',
"recordCount" INTEGER DEFAULT 0,
detail TEXT,
"syncedAt" TEXT,
"createdAt" TEXT,
"updatedAt" TEXT
);
CREATE INDEX IF NOT EXISTS opm_sl_owner ON opm_tally_sync_log("ownerId");
CREATE INDEX IF NOT EXISTS opm_sl_type ON opm_tally_sync_log("syncType");
ALTER TABLE opm_tally_sync_log DISABLE ROW LEVEL SECURITY;