Database Schema

Complete database schema with tables, columns, and relationships

Overview

The Zarna database schema is designed for CRM operations with firm-level isolation via Row Level Security.

Entity Relationship Diagram

firms (1) ────────< (∞) users
  │                     │
  │                     └──> created_by references
  │
  ├─────────< (∞) companies
  │              │
  │              ├──────> contacts (many-to-one)
  │              ├──────> deals (many-to-one)
  │              ├──────> files (many-to-one)
  │              ├──────> interactions (many-to-one)
  │              ├──────> financials (many-to-one)
  │              └──────> notes (many-to-one)
  │
  ├─────────< (∞) deals
  │              └──────> owner_id → users
  │
  └─────────< (∞) files

Core Tables

firms

CREATE TABLE firms (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  domain TEXT,
  settings JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

users

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  role TEXT CHECK (role IN ('admin', 'user', 'viewer')) DEFAULT 'user',
  status TEXT CHECK (status IN ('active', 'inactive')) DEFAULT 'active',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_firm_id ON users(firm_id);
CREATE INDEX idx_users_email ON users(email);

companies

CREATE TABLE companies (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  industry TEXT,
  revenue NUMERIC,
  ebitda NUMERIC,
  employees INTEGER,
  founded_year INTEGER,
  location TEXT,
  website TEXT,
  description TEXT,
  status TEXT CHECK (status IN ('active', 'inactive', 'archived')) DEFAULT 'active',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_by UUID REFERENCES users(id)
);

CREATE INDEX idx_companies_firm_id ON companies(firm_id);
CREATE INDEX idx_companies_industry ON companies(industry);
CREATE INDEX idx_companies_status ON companies(status);
CREATE INDEX idx_companies_name ON companies(name);
CREATE INDEX idx_companies_search ON companies USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- Unique constraint
CREATE UNIQUE INDEX idx_companies_unique_name ON companies(firm_id, LOWER(name));

contacts

CREATE TABLE contacts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  role TEXT,
  is_decision_maker BOOLEAN DEFAULT false,
  linkedin TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_contacts_firm_id ON contacts(firm_id);
CREATE INDEX idx_contacts_company_id ON contacts(company_id);
CREATE INDEX idx_contacts_email ON contacts(email);

deals

CREATE TABLE deals (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
  name TEXT NOT NULL,
  value NUMERIC,
  stage TEXT CHECK (stage IN ('lead', 'qualification', 'proposal', 'negotiation', 'closed_won', 'closed_lost')),
  probability INTEGER CHECK (probability >= 0 AND probability <= 100),
  owner_id UUID REFERENCES users(id),
  close_date DATE,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_deals_firm_id ON deals(firm_id);
CREATE INDEX idx_deals_company_id ON deals(company_id);
CREATE INDEX idx_deals_stage ON deals(stage);
CREATE INDEX idx_deals_owner_id ON deals(owner_id);
CREATE INDEX idx_deals_close_date ON deals(close_date);

files

CREATE TABLE files (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
  filename TEXT NOT NULL,
  size BIGINT,
  content_type TEXT,
  document_type TEXT,
  storage_path TEXT NOT NULL,
  processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed')),
  processing_progress INTEGER DEFAULT 0,
  extracted_data JSONB,
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  processed_at TIMESTAMP WITH TIME ZONE,
  uploaded_by UUID REFERENCES users(id)
);

CREATE INDEX idx_files_firm_id ON files(firm_id);
CREATE INDEX idx_files_company_id ON files(company_id);
CREATE INDEX idx_files_status ON files(processing_status);

interactions

CREATE TABLE interactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  firm_id UUID NOT NULL REFERENCES firms(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
  type TEXT CHECK (type IN ('meeting', 'call', 'email', 'note')),
  subject TEXT NOT NULL,
  notes TEXT,
  date TIMESTAMP WITH TIME ZONE,
  duration_minutes INTEGER,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_interactions_firm_id ON interactions(firm_id);
CREATE INDEX idx_interactions_company_id ON interactions(company_id);
CREATE INDEX idx_interactions_date ON interactions(date);

Integration Tables

email_oauth_tokens

CREATE TABLE email_oauth_tokens (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('google', 'microsoft')),
  email TEXT NOT NULL,
  composio_account_id TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(provider, email)
);

CREATE INDEX idx_email_oauth_user ON email_oauth_tokens(user_id);

email_config

CREATE TABLE email_config (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  connected_emails JSONB DEFAULT '[]'::jsonb,
  mode TEXT CHECK (mode IN ('draft', 'manual', 'auto')) DEFAULT 'draft',
  contact_email TEXT,
  scheduling_email TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Indexes Strategy

Primary Indexes

All tables have indexes on:

• firm_id - For firm isolation queries
• Foreign keys - For join performance
• Status/enum fields - For filtering
• Timestamps - For date range queries

Full-Text Search

-- Companies search
CREATE INDEX idx_companies_search
  ON companies
  USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- Search query
SELECT * FROM companies
WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('english', 'technology');

Composite Indexes

-- Optimize common query patterns
CREATE INDEX idx_companies_firm_industry ON companies(firm_id, industry);
CREATE INDEX idx_deals_firm_stage ON deals(firm_id, stage);
CREATE INDEX idx_files_firm_status ON files(firm_id, processing_status);

Constraints

Foreign Keys

-- CASCADE: Delete children when parent deleted
ALTER TABLE companies
  ADD CONSTRAINT fk_firm
  FOREIGN KEY (firm_id) REFERENCES firms(id)
  ON DELETE CASCADE;

-- SET NULL: Keep record but nullify reference
ALTER TABLE deals
  ADD CONSTRAINT fk_company
  FOREIGN KEY (company_id) REFERENCES companies(id)
  ON DELETE SET NULL;

Check Constraints

-- Ensure valid values
ALTER TABLE deals ADD CONSTRAINT check_probability
  CHECK (probability >= 0 AND probability <= 100);

ALTER TABLE companies ADD CONSTRAINT check_revenue
  CHECK (revenue >= 0);

Unique Constraints

-- Prevent duplicate companies within firm
CREATE UNIQUE INDEX idx_companies_unique
  ON companies(firm_id, LOWER(name));

Triggers

Auto-Update Timestamps

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_companies_updated_at
  BEFORE UPDATE ON companies
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Next Steps

Database Overview

Database architecture

Supabase Integration

Supabase setup

Data Protection

RLS and security