CRM SaaS Template Documentation

# Firestore layout (reference)

Maps SQL/Drizzle entities to suggested Firestore paths. Use **`tenants/{tenantId}`** as the top-level segment so data is isolated per customer.

| Collection (under tenant) | Document ID | Notes |
|---------------------------|-------------|--------|
| `users` | user id | Mirrors `users` table |
| `companies` | company id | |
| `contacts` | contact id | `ownerId` → `users` |
| `leads` | lead id | Pipeline stage enum matches app |
| `deals` | deal id | |
| `pipelines` | pipeline id | Subcollection `stages` optional vs flat `pipelineStages` |
| `tasks` | task id | `taskAssignees` as subcollection or array |
| `invoices` / `invoiceLineItems` | id | Line items often subcollection `invoices/{id}/items/{lineId}` |
| `payments` | payment id | |
| `projects` | project id | |
| `calendar_events` | event id | |
| `email_threads` / `email_messages` | thread / message id | Messages as subcollection under thread |
| `notifications` | notification id | |
| `integrations` | integration id | |
| `api_keys` | key id | Store hashes only; never store raw secrets |
| `webhooks` | webhook id | |
| `team_members` | membership id | |
| `blog_posts` / `blog_subscribers` | id | CMS module |
| `ecommerce_products` | product id | Catalog rows for `/e-commerce` |
| `ecommerce_orders` | order id | Delivery tracking; `product_id` → product |
| `ecommerce_metrics` | metric id | Dashboard KPI cards (optional denormalized doc) |
| `ecommerce_earnings` | doc id | Weekly chart + earning/profit/expense breakdown |
| `segments` | segment id | Audience filters for campaigns |
| `campaigns` | campaign id | `segment_id` → segment |
| `campaign_steps` | step id | Subcollection under campaign or flat with `campaign_id` |
| `marketing_forms` | form id | Lead capture forms with embed codes |
| `form_submissions` | submission id | `form_id` → marketing form |
| `subscription_plans` | plan id | Pricing tiers and features |
| `subscriptions` | subscription id | `plan_id` → subscription plan |
| `subscription_events` | event id | Billing history; `subscription_id` → subscription |
| `api_request_logs` | log id | API Logs module — method, path, status, latency |
| `support_tickets` | ticket id | Subcollection `messages` for thread |
| `audit_log_entries` | entry id | Immutable audit trail |
| `saved_reports` / `report_templates` | id | Reports module |
| `help_articles` | article id | Help center FAQ (optional CMS) |
| `record_files` | file id | File manager — metadata + storage URL |
| `activities` | activity id | Polymorphic `activityType`; also powers Activity feed |
| `lead_notes` / `contact_notes` | note id | |
| `contact_embedded_emails` | id | |

**`data.json`:** Top-level `app*` keys describe **API-shaped** payloads for each route; they are not a 1:1 Firestore export but field names should align when you model documents.

**Indexes:** Add composite indexes when you query by multiple fields (e.g. `leads` by `pipelineId` + `updatedAt`).

rules_version = '2';

/**
 * Starter rules for a multi-tenant CRM aligned with `schemas/sql-schemas/drizzle/schema.ts`.
 * Replace with your auth provider (Firebase Auth UID) and tighten per-collection access.
 *
 * Suggested layout: `tenants/{tenantId}/{collection}/{docId}`
 */
service cloud.firestore {
  match /databases/{database}/documents {

    function signedIn() {
      return request.auth != null;
    }

    // Example: restrict all CRM data to authenticated users on their tenant
    match /tenants/{tenantId}/{collection}/{document=**} {
      allow read, write: if signedIn()
        && request.auth.token.tenantId == tenantId;
    }

    // Deny everything else by default
    match /{document=**} {
      allow read, write: if false;
    }
  }
}

-- Supabase Auth & identity
-- Run first in the Supabase SQL editor or via `supabase db push`.
-- Aligns login with auth.users; app tables reference profiles(id) as owner/rep.

-- ---------------------------------------------------------------------------
-- Tenants (workspaces)
-- ---------------------------------------------------------------------------

create table if not exists public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Profiles (one row per auth.users — replaces drizzle `users` for Supabase)
-- ---------------------------------------------------------------------------

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text not null unique,
  name text,
  avatar_url text,
  role text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists profiles_email_idx on public.profiles (email);

-- ---------------------------------------------------------------------------
-- Tenant membership (multi-tenant access control)
-- ---------------------------------------------------------------------------

create table if not exists public.tenant_members (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  user_id uuid not null references public.profiles (id) on delete cascade,
  role text not null default 'member' check (role in ('owner', 'admin', 'member')),
  created_at timestamptz not null default now(),
  unique (tenant_id, user_id)
);

create index if not exists tenant_members_user_id_idx on public.tenant_members (user_id);
create index if not exists tenant_members_tenant_id_idx on public.tenant_members (tenant_id);

-- ---------------------------------------------------------------------------
-- Sign-up hook: create profile when a user registers
-- ---------------------------------------------------------------------------

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email, name, avatar_url)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data ->> 'name', new.raw_user_meta_data ->> 'full_name'),
    new.raw_user_meta_data ->> 'avatar_url'
  );
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function public.handle_new_user();

-- ---------------------------------------------------------------------------
-- RLS helpers (used by rls-policies.sql)
-- ---------------------------------------------------------------------------

create or replace function public.user_tenant_ids()
returns setof uuid
language sql
stable
security definer
set search_path = public
as $$
  select tenant_id
  from public.tenant_members
  where user_id = auth.uid();
$$;

create or replace function public.is_tenant_member(p_tenant_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.tenant_members
    where tenant_id = p_tenant_id
      and user_id = auth.uid()
  );
$$;

-- ---------------------------------------------------------------------------
-- Auth table RLS (profiles readable by self; tenants via membership)
-- ---------------------------------------------------------------------------

alter table public.tenants enable row level security;
alter table public.profiles enable row level security;
alter table public.tenant_members enable row level security;

create policy "tenants_select_member"
  on public.tenants for select
  using (id in (select public.user_tenant_ids()));

create policy "profiles_select_self"
  on public.profiles for select
  using (id = auth.uid());

create policy "profiles_update_self"
  on public.profiles for update
  using (id = auth.uid());

create policy "tenant_members_select_same_tenant"
  on public.tenant_members for select
  using (tenant_id in (select public.user_tenant_ids()));

-- CRM app tables (all pages)
-- Run after 01-auth.sql. Mirrors schemas/sql-schemas/drizzle/schema.ts with tenant_id on every table.

-- ---------------------------------------------------------------------------
-- Enums
-- ---------------------------------------------------------------------------

do $$ begin
  create type public.deal_stage as enum ('prospect', 'qualified', 'proposal', 'won', 'lost');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.invoice_status as enum ('Paid', 'Pending', 'Overdue');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.payment_status as enum ('Completed', 'Pending', 'Failed');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.task_progress as enum ('NotStarted', 'InProgress', 'Completed');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.task_priority as enum ('Low', 'Medium', 'High');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.team_member_status as enum ('Active', 'Invited', 'Inactive');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.blog_post_status as enum ('Published', 'Draft');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.api_key_status as enum ('Active', 'Revoked');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.webhook_status as enum ('Active', 'Inactive');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.activity_type as enum ('note', 'email', 'deal', 'call');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type public.notification_category as enum ('Ticket', 'Team', 'Message');
exception when duplicate_object then null;
end $$;

-- ---------------------------------------------------------------------------
-- CRM core
-- ---------------------------------------------------------------------------

create table if not exists public.companies (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  industry text,
  rating numeric(3, 1),
  location text,
  logo_url text,
  owner_id uuid references public.profiles (id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists companies_tenant_id_idx on public.companies (tenant_id);
create index if not exists companies_owner_id_idx on public.companies (owner_id);

create table if not exists public.pipelines (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null default 'Default',
  created_at timestamptz not null default now()
);

create index if not exists pipelines_tenant_id_idx on public.pipelines (tenant_id);

create table if not exists public.pipeline_stages (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  pipeline_id varchar(32) not null references public.pipelines (id) on delete cascade,
  slug text not null,
  label text not null,
  sort_order integer not null default 0,
  unique (pipeline_id, slug)
);

create index if not exists pipeline_stages_tenant_id_idx on public.pipeline_stages (tenant_id);
create index if not exists pipeline_stages_pipeline_id_idx on public.pipeline_stages (pipeline_id);

create table if not exists public.contacts (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  company_id varchar(32) references public.companies (id) on delete set null,
  owner_id uuid references public.profiles (id) on delete set null,
  name text not null,
  email text not null,
  phone text,
  title text,
  lifecycle text,
  image_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists contacts_tenant_id_idx on public.contacts (tenant_id);
create index if not exists contacts_company_id_idx on public.contacts (company_id);
create index if not exists contacts_email_idx on public.contacts (email);

create table if not exists public.leads (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  company_id varchar(32) references public.companies (id) on delete set null,
  owner_id uuid references public.profiles (id) on delete set null,
  pipeline_stage_id varchar(32) references public.pipeline_stages (id) on delete set null,
  name text not null,
  email text not null,
  phone text,
  title text,
  source text,
  status text,
  estimated_value numeric(14, 2),
  image_url text,
  last_contacted_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists leads_tenant_id_idx on public.leads (tenant_id);
create index if not exists leads_company_id_idx on public.leads (company_id);
create index if not exists leads_pipeline_stage_id_idx on public.leads (pipeline_stage_id);
create index if not exists leads_email_idx on public.leads (email);

create table if not exists public.deals (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  company_id varchar(32) references public.companies (id) on delete set null,
  lead_id varchar(32) references public.leads (id) on delete set null,
  rep_id uuid references public.profiles (id) on delete set null,
  name text not null,
  value numeric(14, 2) not null,
  stage public.deal_stage not null,
  close_date timestamptz,
  priority text,
  tag text,
  last_activity text,
  days_in_stage integer,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists deals_tenant_id_idx on public.deals (tenant_id);
create index if not exists deals_company_id_idx on public.deals (company_id);
create index if not exists deals_stage_idx on public.deals (stage);
create index if not exists deals_lead_id_idx on public.deals (lead_id);

-- ---------------------------------------------------------------------------
-- Tasks
-- ---------------------------------------------------------------------------

create table if not exists public.tasks (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  section_slug text not null,
  section_label text,
  title text not null,
  description text,
  progress public.task_progress,
  priority public.task_priority,
  progress_percent integer not null default 0,
  icon text,
  attachments integer not null default 0,
  comments_count integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists tasks_tenant_id_idx on public.tasks (tenant_id);

create table if not exists public.task_assignees (
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  task_id varchar(32) not null references public.tasks (id) on delete cascade,
  user_id uuid not null references public.profiles (id) on delete cascade,
  primary key (task_id, user_id)
);

create index if not exists task_assignees_tenant_id_idx on public.task_assignees (tenant_id);

-- ---------------------------------------------------------------------------
-- Finance — invoices & payments
-- ---------------------------------------------------------------------------

create table if not exists public.invoices (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  invoice_number text not null,
  client_label text not null,
  client_email text,
  client_id varchar(32),
  amount numeric(14, 2) not null,
  status public.invoice_status not null,
  issue_date timestamptz not null,
  due_date timestamptz not null,
  description text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (tenant_id, invoice_number)
);

create index if not exists invoices_tenant_id_idx on public.invoices (tenant_id);
create index if not exists invoices_status_idx on public.invoices (status);

create table if not exists public.invoice_line_items (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  invoice_id varchar(32) not null references public.invoices (id) on delete cascade,
  name text not null,
  quantity integer not null default 1,
  price numeric(14, 2) not null
);

create index if not exists invoice_line_items_tenant_id_idx on public.invoice_line_items (tenant_id);
create index if not exists invoice_line_items_invoice_id_idx on public.invoice_line_items (invoice_id);

create table if not exists public.payments (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  transaction_id text not null,
  invoice_id varchar(32) references public.invoices (id) on delete set null,
  amount numeric(14, 2) not null,
  currency text not null default 'USD',
  status public.payment_status not null,
  payment_method text not null,
  customer_label text not null,
  customer_email text not null,
  description text,
  fee numeric(14, 2),
  net_amount numeric(14, 2),
  occurred_at timestamptz not null,
  occurred_time text,
  created_at timestamptz not null default now(),
  unique (tenant_id, transaction_id)
);

create index if not exists payments_tenant_id_idx on public.payments (tenant_id);
create index if not exists payments_invoice_id_idx on public.payments (invoice_id);
create index if not exists payments_status_idx on public.payments (status);

-- ---------------------------------------------------------------------------
-- Ops / misc pages
-- ---------------------------------------------------------------------------

create table if not exists public.projects (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  project_name text not null,
  client_name text not null,
  client_avatar_color text,
  start_date timestamptz,
  deadline timestamptz,
  status text,
  progress integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists projects_tenant_id_idx on public.projects (tenant_id);

create table if not exists public.calendar_events (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  title text not null,
  time_label text,
  color text,
  starts_at timestamptz,
  day_of_month integer,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists calendar_events_tenant_id_idx on public.calendar_events (tenant_id);

create table if not exists public.email_threads (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  subject text not null,
  preview text,
  from_name text not null,
  from_email text not null,
  last_time text,
  unread boolean not null default true,
  starred boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists email_threads_tenant_id_idx on public.email_threads (tenant_id);

create table if not exists public.email_messages (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  thread_id varchar(32) not null references public.email_threads (id) on delete cascade,
  from_name text not null,
  from_email text not null,
  to_address text,
  sent_at_label text,
  body text not null,
  created_at timestamptz not null default now()
);

create index if not exists email_messages_tenant_id_idx on public.email_messages (tenant_id);
create index if not exists email_messages_thread_id_idx on public.email_messages (thread_id);

create table if not exists public.notifications (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  user_id uuid references public.profiles (id) on delete cascade,
  type text not null,
  title text not null,
  body text not null,
  category public.notification_category not null,
  time_label text,
  unread boolean not null default true,
  avatar_url text,
  has_actions boolean not null default false,
  created_at timestamptz not null default now()
);

create index if not exists notifications_tenant_id_idx on public.notifications (tenant_id);
create index if not exists notifications_user_id_idx on public.notifications (user_id);

create table if not exists public.team_members (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  email text not null,
  role text not null,
  status public.team_member_status not null default 'Active',
  joined_at timestamptz,
  image_url text,
  unique (tenant_id, email)
);

create index if not exists team_members_tenant_id_idx on public.team_members (tenant_id);

-- ---------------------------------------------------------------------------
-- Integrations, API keys, webhooks
-- ---------------------------------------------------------------------------

create table if not exists public.integrations (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  enabled boolean not null default false,
  tag text,
  last_updated timestamptz,
  created_at timestamptz not null default now(),
  unique (tenant_id, name)
);

create index if not exists integrations_tenant_id_idx on public.integrations (tenant_id);

create table if not exists public.api_keys (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  key_hash text,
  status public.api_key_status not null default 'Active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists api_keys_tenant_id_idx on public.api_keys (tenant_id);
create index if not exists api_keys_status_idx on public.api_keys (status);

create table if not exists public.webhooks (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  url text not null,
  events jsonb not null default '[]'::jsonb,
  secret_hash text,
  status public.webhook_status not null default 'Active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists webhooks_tenant_id_idx on public.webhooks (tenant_id);

-- ---------------------------------------------------------------------------
-- CMS
-- ---------------------------------------------------------------------------

create table if not exists public.blog_posts (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  title text not null,
  author_name text not null,
  author_avatar text,
  category text,
  status public.blog_post_status not null default 'Draft',
  publish_date timestamptz,
  views integer not null default 0,
  likes integer not null default 0,
  content text not null,
  tags text[] not null default '{}',
  featured_image text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists blog_posts_tenant_id_idx on public.blog_posts (tenant_id);
create index if not exists blog_posts_status_idx on public.blog_posts (status);

create table if not exists public.blog_subscribers (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  email text not null,
  name text,
  subscribed_date timestamptz,
  status text,
  avatar_color text,
  unique (tenant_id, email)
);

create index if not exists blog_subscribers_tenant_id_idx on public.blog_subscribers (tenant_id);

-- ---------------------------------------------------------------------------
-- Record detail (leads / contacts)
-- ---------------------------------------------------------------------------

create table if not exists public.activities (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  type public.activity_type not null,
  title text not null,
  body text not null,
  occurred_at timestamptz,
  actor_label text,
  lead_id varchar(32) references public.leads (id) on delete cascade,
  contact_id varchar(32) references public.contacts (id) on delete cascade,
  created_at timestamptz not null default now()
);

create index if not exists activities_tenant_id_idx on public.activities (tenant_id);
create index if not exists activities_lead_id_idx on public.activities (lead_id);
create index if not exists activities_contact_id_idx on public.activities (contact_id);

create table if not exists public.lead_notes (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  lead_id varchar(32) not null references public.leads (id) on delete cascade,
  body text not null,
  authored_at timestamptz,
  author text
);

create index if not exists lead_notes_tenant_id_idx on public.lead_notes (tenant_id);
create index if not exists lead_notes_lead_id_idx on public.lead_notes (lead_id);

create table if not exists public.contact_notes (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  contact_id varchar(32) not null references public.contacts (id) on delete cascade,
  body text not null,
  authored_at timestamptz,
  author text
);

create index if not exists contact_notes_tenant_id_idx on public.contact_notes (tenant_id);
create index if not exists contact_notes_contact_id_idx on public.contact_notes (contact_id);

create table if not exists public.record_files (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  size_label text,
  mime_family text,
  lead_id varchar(32) references public.leads (id) on delete cascade,
  contact_id varchar(32) references public.contacts (id) on delete cascade
);

create index if not exists record_files_tenant_id_idx on public.record_files (tenant_id);
create index if not exists record_files_lead_id_idx on public.record_files (lead_id);
create index if not exists record_files_contact_id_idx on public.record_files (contact_id);

create table if not exists public.contact_embedded_emails (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  contact_id varchar(32) not null references public.contacts (id) on delete cascade,
  subject text,
  body text,
  sent_at timestamptz
);

create index if not exists contact_embedded_emails_tenant_id_idx on public.contact_embedded_emails (tenant_id);
create index if not exists contact_embedded_emails_contact_id_idx on public.contact_embedded_emails (contact_id);

-- E-commerce (`appEcommerce`)
create type public.ecommerce_product_category as enum (
  'Smartphone', 'Laptop', 'Headphone', 'Smartwatch'
);
create type public.ecommerce_product_status as enum ('Publish', 'Inactive');
create type public.ecommerce_order_tab as enum ('New', 'Pending', 'Shipping');

create table if not exists public.ecommerce_products (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  brand text not null,
  image_url text,
  category public.ecommerce_product_category not null,
  in_stock boolean not null default true,
  price numeric(12, 2) not null,
  quantity integer not null default 0,
  status public.ecommerce_product_status not null default 'Publish',
  description text,
  sku text,
  visitors_label text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists ecommerce_products_tenant_id_idx on public.ecommerce_products (tenant_id);
create index if not exists ecommerce_products_category_idx on public.ecommerce_products (category);

create table if not exists public.ecommerce_orders (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  product_id varchar(32) not null references public.ecommerce_products (id) on delete cascade,
  tab public.ecommerce_order_tab not null default 'New',
  sender_name text not null,
  sender_address text not null,
  receiver_name text not null,
  receiver_address text not null,
  created_at timestamptz not null default now()
);

create index if not exists ecommerce_orders_tenant_id_idx on public.ecommerce_orders (tenant_id);
create index if not exists ecommerce_orders_product_id_idx on public.ecommerce_orders (product_id);

-- Marketing (`appMarketing`)
create type public.campaign_status as enum ('Draft', 'Scheduled', 'Active', 'Paused', 'Completed');
create type public.campaign_step_type as enum ('email', 'wait', 'branch');
create type public.segment_entity_type as enum ('contact', 'lead', 'both');
create type public.marketing_form_status as enum ('Draft', 'Active');

create table if not exists public.segments (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  description text,
  entity_type public.segment_entity_type not null default 'both',
  filters jsonb not null,
  contact_count integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists segments_tenant_id_idx on public.segments (tenant_id);
create index if not exists segments_entity_type_idx on public.segments (entity_type);

create table if not exists public.campaigns (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  description text,
  status public.campaign_status not null default 'Draft',
  segment_id varchar(32) references public.segments (id) on delete set null,
  scheduled_at timestamptz,
  started_at timestamptz,
  completed_at timestamptz,
  sent_count integer not null default 0,
  delivered_count integer not null default 0,
  open_rate numeric(5, 2),
  click_rate numeric(5, 2),
  bounce_rate numeric(5, 2),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists campaigns_tenant_id_idx on public.campaigns (tenant_id);
create index if not exists campaigns_status_idx on public.campaigns (status);
create index if not exists campaigns_segment_id_idx on public.campaigns (segment_id);

create table if not exists public.campaign_steps (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  campaign_id varchar(32) not null references public.campaigns (id) on delete cascade,
  step_order integer not null,
  step_type public.campaign_step_type not null,
  name text not null,
  config jsonb,
  created_at timestamptz not null default now()
);

create index if not exists campaign_steps_tenant_id_idx on public.campaign_steps (tenant_id);
create index if not exists campaign_steps_campaign_id_idx on public.campaign_steps (campaign_id);

create table if not exists public.marketing_forms (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  description text,
  fields jsonb not null,
  embed_code text,
  status public.marketing_form_status not null default 'Draft',
  submission_count integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists marketing_forms_tenant_id_idx on public.marketing_forms (tenant_id);
create index if not exists marketing_forms_status_idx on public.marketing_forms (status);

create table if not exists public.form_submissions (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  form_id varchar(32) not null references public.marketing_forms (id) on delete cascade,
  payload jsonb not null,
  source_url text,
  created_at timestamptz not null default now()
);

create index if not exists form_submissions_tenant_id_idx on public.form_submissions (tenant_id);
create index if not exists form_submissions_form_id_idx on public.form_submissions (form_id);

-- Subscriptions (`appSubscriptions`)
create type public.subscription_billing_interval as enum ('Monthly', 'Yearly');
create type public.subscription_plan_status as enum ('Active', 'Archived');
create type public.subscription_status as enum ('Active', 'Trialing', 'Past Due', 'Canceled', 'Paused');
create type public.subscription_event_type as enum (
  'created', 'renewed', 'upgraded', 'downgraded', 'canceled', 'payment_failed', 'reactivated'
);

create table if not exists public.subscription_plans (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null,
  sku text not null,
  tier text not null,
  price numeric(12, 2) not null,
  currency text not null default 'USD',
  billing_interval public.subscription_billing_interval not null,
  features jsonb not null,
  max_seats integer not null default 0,
  trial_days integer not null default 0,
  status public.subscription_plan_status not null default 'Active',
  active_subscriptions integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists subscription_plans_tenant_id_idx on public.subscription_plans (tenant_id);
create index if not exists subscription_plans_status_idx on public.subscription_plans (status);

create table if not exists public.subscriptions (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  plan_id varchar(32) not null references public.subscription_plans (id) on delete restrict,
  customer_name text not null,
  customer_email text not null,
  company_name text,
  status public.subscription_status not null default 'Active',
  mrr numeric(12, 2) not null,
  billing_interval public.subscription_billing_interval not null,
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean not null default false,
  canceled_at timestamptz,
  started_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists subscriptions_tenant_id_idx on public.subscriptions (tenant_id);
create index if not exists subscriptions_plan_id_idx on public.subscriptions (plan_id);
create index if not exists subscriptions_status_idx on public.subscriptions (status);

create table if not exists public.subscription_events (
  id varchar(32) primary key,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  subscription_id varchar(32) not null references public.subscriptions (id) on delete cascade,
  event_type public.subscription_event_type not null,
  description text not null,
  metadata jsonb,
  created_at timestamptz not null default now()
);

create index if not exists subscription_events_tenant_id_idx on public.subscription_events (tenant_id);
create index if not exists subscription_events_subscription_id_idx on public.subscription_events (subscription_id);

-- Row Level Security for CRM app tables
-- Run after 02-app-tables.sql.
-- Starter policies: authenticated users can read/write rows in tenants they belong to.
-- Tighten per-table (e.g. api_keys insert only for admin role) in your deployment.

-- ---------------------------------------------------------------------------
-- Enable RLS on all app tables
-- ---------------------------------------------------------------------------

alter table public.companies enable row level security;
alter table public.pipelines enable row level security;
alter table public.pipeline_stages enable row level security;
alter table public.contacts enable row level security;
alter table public.leads enable row level security;
alter table public.deals enable row level security;
alter table public.tasks enable row level security;
alter table public.task_assignees enable row level security;
alter table public.invoices enable row level security;
alter table public.invoice_line_items enable row level security;
alter table public.payments enable row level security;
alter table public.projects enable row level security;
alter table public.calendar_events enable row level security;
alter table public.email_threads enable row level security;
alter table public.email_messages enable row level security;
alter table public.notifications enable row level security;
alter table public.team_members enable row level security;
alter table public.integrations enable row level security;
alter table public.api_keys enable row level security;
alter table public.webhooks enable row level security;
alter table public.blog_posts enable row level security;
alter table public.blog_subscribers enable row level security;
alter table public.activities enable row level security;
alter table public.lead_notes enable row level security;
alter table public.contact_notes enable row level security;
alter table public.record_files enable row level security;
alter table public.contact_embedded_emails enable row level security;
alter table public.ecommerce_products enable row level security;
alter table public.ecommerce_orders enable row level security;
alter table public.segments enable row level security;
alter table public.campaigns enable row level security;
alter table public.campaign_steps enable row level security;
alter table public.marketing_forms enable row level security;
alter table public.form_submissions enable row level security;
alter table public.subscription_plans enable row level security;
alter table public.subscriptions enable row level security;
alter table public.subscription_events enable row level security;

-- ---------------------------------------------------------------------------
-- Macro: tenant-scoped CRUD for tables with tenant_id
-- ---------------------------------------------------------------------------

-- Companies
create policy "companies_tenant_select" on public.companies for select using (tenant_id in (select public.user_tenant_ids()));
create policy "companies_tenant_insert" on public.companies for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "companies_tenant_update" on public.companies for update using (tenant_id in (select public.user_tenant_ids()));
create policy "companies_tenant_delete" on public.companies for delete using (tenant_id in (select public.user_tenant_ids()));

-- Pipelines
create policy "pipelines_tenant_select" on public.pipelines for select using (tenant_id in (select public.user_tenant_ids()));
create policy "pipelines_tenant_insert" on public.pipelines for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "pipelines_tenant_update" on public.pipelines for update using (tenant_id in (select public.user_tenant_ids()));
create policy "pipelines_tenant_delete" on public.pipelines for delete using (tenant_id in (select public.user_tenant_ids()));

-- Pipeline stages
create policy "pipeline_stages_tenant_select" on public.pipeline_stages for select using (tenant_id in (select public.user_tenant_ids()));
create policy "pipeline_stages_tenant_insert" on public.pipeline_stages for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "pipeline_stages_tenant_update" on public.pipeline_stages for update using (tenant_id in (select public.user_tenant_ids()));
create policy "pipeline_stages_tenant_delete" on public.pipeline_stages for delete using (tenant_id in (select public.user_tenant_ids()));

-- Contacts
create policy "contacts_tenant_select" on public.contacts for select using (tenant_id in (select public.user_tenant_ids()));
create policy "contacts_tenant_insert" on public.contacts for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "contacts_tenant_update" on public.contacts for update using (tenant_id in (select public.user_tenant_ids()));
create policy "contacts_tenant_delete" on public.contacts for delete using (tenant_id in (select public.user_tenant_ids()));

-- Leads
create policy "leads_tenant_select" on public.leads for select using (tenant_id in (select public.user_tenant_ids()));
create policy "leads_tenant_insert" on public.leads for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "leads_tenant_update" on public.leads for update using (tenant_id in (select public.user_tenant_ids()));
create policy "leads_tenant_delete" on public.leads for delete using (tenant_id in (select public.user_tenant_ids()));

-- Deals
create policy "deals_tenant_select" on public.deals for select using (tenant_id in (select public.user_tenant_ids()));
create policy "deals_tenant_insert" on public.deals for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "deals_tenant_update" on public.deals for update using (tenant_id in (select public.user_tenant_ids()));
create policy "deals_tenant_delete" on public.deals for delete using (tenant_id in (select public.user_tenant_ids()));

-- Tasks
create policy "tasks_tenant_select" on public.tasks for select using (tenant_id in (select public.user_tenant_ids()));
create policy "tasks_tenant_insert" on public.tasks for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "tasks_tenant_update" on public.tasks for update using (tenant_id in (select public.user_tenant_ids()));
create policy "tasks_tenant_delete" on public.tasks for delete using (tenant_id in (select public.user_tenant_ids()));

-- Task assignees
create policy "task_assignees_tenant_select" on public.task_assignees for select using (tenant_id in (select public.user_tenant_ids()));
create policy "task_assignees_tenant_insert" on public.task_assignees for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "task_assignees_tenant_update" on public.task_assignees for update using (tenant_id in (select public.user_tenant_ids()));
create policy "task_assignees_tenant_delete" on public.task_assignees for delete using (tenant_id in (select public.user_tenant_ids()));

-- Invoices
create policy "invoices_tenant_select" on public.invoices for select using (tenant_id in (select public.user_tenant_ids()));
create policy "invoices_tenant_insert" on public.invoices for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "invoices_tenant_update" on public.invoices for update using (tenant_id in (select public.user_tenant_ids()));
create policy "invoices_tenant_delete" on public.invoices for delete using (tenant_id in (select public.user_tenant_ids()));

-- Invoice line items
create policy "invoice_line_items_tenant_select" on public.invoice_line_items for select using (tenant_id in (select public.user_tenant_ids()));
create policy "invoice_line_items_tenant_insert" on public.invoice_line_items for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "invoice_line_items_tenant_update" on public.invoice_line_items for update using (tenant_id in (select public.user_tenant_ids()));
create policy "invoice_line_items_tenant_delete" on public.invoice_line_items for delete using (tenant_id in (select public.user_tenant_ids()));

-- Payments
create policy "payments_tenant_select" on public.payments for select using (tenant_id in (select public.user_tenant_ids()));
create policy "payments_tenant_insert" on public.payments for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "payments_tenant_update" on public.payments for update using (tenant_id in (select public.user_tenant_ids()));
create policy "payments_tenant_delete" on public.payments for delete using (tenant_id in (select public.user_tenant_ids()));

-- Projects
create policy "projects_tenant_select" on public.projects for select using (tenant_id in (select public.user_tenant_ids()));
create policy "projects_tenant_insert" on public.projects for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "projects_tenant_update" on public.projects for update using (tenant_id in (select public.user_tenant_ids()));
create policy "projects_tenant_delete" on public.projects for delete using (tenant_id in (select public.user_tenant_ids()));

-- Calendar
create policy "calendar_events_tenant_select" on public.calendar_events for select using (tenant_id in (select public.user_tenant_ids()));
create policy "calendar_events_tenant_insert" on public.calendar_events for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "calendar_events_tenant_update" on public.calendar_events for update using (tenant_id in (select public.user_tenant_ids()));
create policy "calendar_events_tenant_delete" on public.calendar_events for delete using (tenant_id in (select public.user_tenant_ids()));

-- Email
create policy "email_threads_tenant_select" on public.email_threads for select using (tenant_id in (select public.user_tenant_ids()));
create policy "email_threads_tenant_insert" on public.email_threads for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "email_threads_tenant_update" on public.email_threads for update using (tenant_id in (select public.user_tenant_ids()));
create policy "email_threads_tenant_delete" on public.email_threads for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "email_messages_tenant_select" on public.email_messages for select using (tenant_id in (select public.user_tenant_ids()));
create policy "email_messages_tenant_insert" on public.email_messages for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "email_messages_tenant_update" on public.email_messages for update using (tenant_id in (select public.user_tenant_ids()));
create policy "email_messages_tenant_delete" on public.email_messages for delete using (tenant_id in (select public.user_tenant_ids()));

-- Notifications
create policy "notifications_tenant_select" on public.notifications for select using (tenant_id in (select public.user_tenant_ids()));
create policy "notifications_tenant_insert" on public.notifications for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "notifications_tenant_update" on public.notifications for update using (tenant_id in (select public.user_tenant_ids()));
create policy "notifications_tenant_delete" on public.notifications for delete using (tenant_id in (select public.user_tenant_ids()));

-- Team members
create policy "team_members_tenant_select" on public.team_members for select using (tenant_id in (select public.user_tenant_ids()));
create policy "team_members_tenant_insert" on public.team_members for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "team_members_tenant_update" on public.team_members for update using (tenant_id in (select public.user_tenant_ids()));
create policy "team_members_tenant_delete" on public.team_members for delete using (tenant_id in (select public.user_tenant_ids()));

-- Integrations
create policy "integrations_tenant_select" on public.integrations for select using (tenant_id in (select public.user_tenant_ids()));
create policy "integrations_tenant_insert" on public.integrations for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "integrations_tenant_update" on public.integrations for update using (tenant_id in (select public.user_tenant_ids()));
create policy "integrations_tenant_delete" on public.integrations for delete using (tenant_id in (select public.user_tenant_ids()));

-- API keys — restrict writes in production to service role or admin role
create policy "api_keys_tenant_select" on public.api_keys for select using (tenant_id in (select public.user_tenant_ids()));
create policy "api_keys_tenant_insert" on public.api_keys for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "api_keys_tenant_update" on public.api_keys for update using (tenant_id in (select public.user_tenant_ids()));
create policy "api_keys_tenant_delete" on public.api_keys for delete using (tenant_id in (select public.user_tenant_ids()));

-- Webhooks
create policy "webhooks_tenant_select" on public.webhooks for select using (tenant_id in (select public.user_tenant_ids()));
create policy "webhooks_tenant_insert" on public.webhooks for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "webhooks_tenant_update" on public.webhooks for update using (tenant_id in (select public.user_tenant_ids()));
create policy "webhooks_tenant_delete" on public.webhooks for delete using (tenant_id in (select public.user_tenant_ids()));

-- CMS
create policy "blog_posts_tenant_select" on public.blog_posts for select using (tenant_id in (select public.user_tenant_ids()));
create policy "blog_posts_tenant_insert" on public.blog_posts for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "blog_posts_tenant_update" on public.blog_posts for update using (tenant_id in (select public.user_tenant_ids()));
create policy "blog_posts_tenant_delete" on public.blog_posts for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "blog_subscribers_tenant_select" on public.blog_subscribers for select using (tenant_id in (select public.user_tenant_ids()));
create policy "blog_subscribers_tenant_insert" on public.blog_subscribers for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "blog_subscribers_tenant_update" on public.blog_subscribers for update using (tenant_id in (select public.user_tenant_ids()));
create policy "blog_subscribers_tenant_delete" on public.blog_subscribers for delete using (tenant_id in (select public.user_tenant_ids()));

-- Record detail
create policy "activities_tenant_select" on public.activities for select using (tenant_id in (select public.user_tenant_ids()));
create policy "activities_tenant_insert" on public.activities for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "activities_tenant_update" on public.activities for update using (tenant_id in (select public.user_tenant_ids()));
create policy "activities_tenant_delete" on public.activities for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "lead_notes_tenant_select" on public.lead_notes for select using (tenant_id in (select public.user_tenant_ids()));
create policy "lead_notes_tenant_insert" on public.lead_notes for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "lead_notes_tenant_update" on public.lead_notes for update using (tenant_id in (select public.user_tenant_ids()));
create policy "lead_notes_tenant_delete" on public.lead_notes for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "contact_notes_tenant_select" on public.contact_notes for select using (tenant_id in (select public.user_tenant_ids()));
create policy "contact_notes_tenant_insert" on public.contact_notes for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "contact_notes_tenant_update" on public.contact_notes for update using (tenant_id in (select public.user_tenant_ids()));
create policy "contact_notes_tenant_delete" on public.contact_notes for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "record_files_tenant_select" on public.record_files for select using (tenant_id in (select public.user_tenant_ids()));
create policy "record_files_tenant_insert" on public.record_files for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "record_files_tenant_update" on public.record_files for update using (tenant_id in (select public.user_tenant_ids()));
create policy "record_files_tenant_delete" on public.record_files for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "contact_embedded_emails_tenant_select" on public.contact_embedded_emails for select using (tenant_id in (select public.user_tenant_ids()));
create policy "contact_embedded_emails_tenant_insert" on public.contact_embedded_emails for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "contact_embedded_emails_tenant_update" on public.contact_embedded_emails for update using (tenant_id in (select public.user_tenant_ids()));
create policy "contact_embedded_emails_tenant_delete" on public.contact_embedded_emails for delete using (tenant_id in (select public.user_tenant_ids()));

-- E-commerce
create policy "ecommerce_products_tenant_select" on public.ecommerce_products for select using (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_products_tenant_insert" on public.ecommerce_products for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_products_tenant_update" on public.ecommerce_products for update using (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_products_tenant_delete" on public.ecommerce_products for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "ecommerce_orders_tenant_select" on public.ecommerce_orders for select using (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_orders_tenant_insert" on public.ecommerce_orders for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_orders_tenant_update" on public.ecommerce_orders for update using (tenant_id in (select public.user_tenant_ids()));
create policy "ecommerce_orders_tenant_delete" on public.ecommerce_orders for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "segments_tenant_select" on public.segments for select using (tenant_id in (select public.user_tenant_ids()));
create policy "segments_tenant_insert" on public.segments for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "segments_tenant_update" on public.segments for update using (tenant_id in (select public.user_tenant_ids()));
create policy "segments_tenant_delete" on public.segments for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "campaigns_tenant_select" on public.campaigns for select using (tenant_id in (select public.user_tenant_ids()));
create policy "campaigns_tenant_insert" on public.campaigns for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "campaigns_tenant_update" on public.campaigns for update using (tenant_id in (select public.user_tenant_ids()));
create policy "campaigns_tenant_delete" on public.campaigns for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "campaign_steps_tenant_select" on public.campaign_steps for select using (tenant_id in (select public.user_tenant_ids()));
create policy "campaign_steps_tenant_insert" on public.campaign_steps for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "campaign_steps_tenant_update" on public.campaign_steps for update using (tenant_id in (select public.user_tenant_ids()));
create policy "campaign_steps_tenant_delete" on public.campaign_steps for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "marketing_forms_tenant_select" on public.marketing_forms for select using (tenant_id in (select public.user_tenant_ids()));
create policy "marketing_forms_tenant_insert" on public.marketing_forms for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "marketing_forms_tenant_update" on public.marketing_forms for update using (tenant_id in (select public.user_tenant_ids()));
create policy "marketing_forms_tenant_delete" on public.marketing_forms for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "form_submissions_tenant_select" on public.form_submissions for select using (tenant_id in (select public.user_tenant_ids()));
create policy "form_submissions_tenant_insert" on public.form_submissions for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "form_submissions_tenant_update" on public.form_submissions for update using (tenant_id in (select public.user_tenant_ids()));
create policy "form_submissions_tenant_delete" on public.form_submissions for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "subscription_plans_tenant_select" on public.subscription_plans for select using (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_plans_tenant_insert" on public.subscription_plans for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_plans_tenant_update" on public.subscription_plans for update using (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_plans_tenant_delete" on public.subscription_plans for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "subscriptions_tenant_select" on public.subscriptions for select using (tenant_id in (select public.user_tenant_ids()));
create policy "subscriptions_tenant_insert" on public.subscriptions for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "subscriptions_tenant_update" on public.subscriptions for update using (tenant_id in (select public.user_tenant_ids()));
create policy "subscriptions_tenant_delete" on public.subscriptions for delete using (tenant_id in (select public.user_tenant_ids()));

create policy "subscription_events_tenant_select" on public.subscription_events for select using (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_events_tenant_insert" on public.subscription_events for insert with check (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_events_tenant_update" on public.subscription_events for update using (tenant_id in (select public.user_tenant_ids()));
create policy "subscription_events_tenant_delete" on public.subscription_events for delete using (tenant_id in (select public.user_tenant_ids()));

# Supabase layout (reference)

PostgreSQL tables for this CRM template, aligned with `schemas/sql-schemas/drizzle/schema.ts` and Zod `app*` payloads in `data.json`. Use **`tenant_id`** on every app table so data is isolated per customer (same intent as Firestore `tenants/{tenantId}`).

Apply migrations in order: **`01-auth.sql`** → **`02-app-tables.sql`** → **`rls-policies.sql`**.

## Auth & identity (`01-auth.sql`)

| Table | Purpose |
|-------|---------|
| `tenants` | Workspace / organization |
| `profiles` | App profile row per `auth.users` (login identity) |
| `tenant_members` | Which users belong to which tenant and their role |

Supabase Auth (`auth.users`) handles sign-up, sign-in, OAuth, and sessions. `profiles` is populated by the `handle_new_user` trigger.

## App tables by route (`02-app-tables.sql`)

| App page / `data.json` key | Tables |
|----------------------------|--------|
| Leads — `appLeads` | `pipelines`, `pipeline_stages`, `leads`, `lead_notes`, `activities` (lead) |
| CRM record detail — `appCrmRecordDetail` | `activities`, `lead_notes`, `contact_notes`, `record_files` |
| Companies — `appCompanies` | `companies` |
| Contacts — `appContacts` | `contacts`, `contact_embedded_emails` |
| Deals / Pipelines — `appDeals`, `appPipelines` | `deals`, `pipelines`, `pipeline_stages` |
| Invoices — `appInvoices` | `invoices`, `invoice_line_items` |
| Payments — `appPayments` | `payments` |
| Tasks — `appTasks` | `tasks`, `task_assignees` |
| Home / Sales — `appHome`, `appSales` | `deals`, `leads`, `companies` (aggregates) |
| Analytics — `appAnalytics` | read-only aggregates over finance + CRM tables |
| Calendar / Email — `appCalendar`, `appEmail` | `calendar_events`, `email_threads`, `email_messages` |
| Notifications / Teams — `appNotifications`, `appTeams` | `notifications`, `team_members` |
| Projects — `appProjects` | `projects` |
| Settings — `appSettings` | `profiles`, `tenants`, `tenant_members` |
| Integrations — `appIntegrations` | `integrations` |
| API keys — `appApiKeys` | `api_keys` (store **hashes** only) |
| Webhooks — `appWebhooks` | `webhooks` (store **secret hashes** only) |
| CMS — `appCms` | `blog_posts`, `blog_subscribers` |
| E-commerce — `appEcommerce` | `ecommerce_products`, `ecommerce_orders`, `ecommerce_metrics`, `ecommerce_earnings` |
| Marketing — `appMarketing` | `segments`, `campaigns`, `campaign_steps`, `marketing_forms`, `form_submissions` |
| Subscriptions — `appSubscriptions` | `subscription_plans`, `subscriptions`, `subscription_events` |
| API logs — `appApiLogs` | `api_request_logs` (reference) |
| Support — `appSupport` | `support_tickets`, `support_messages` |
| Users — `appUsers` | `tenant_members`, `profiles` |
| Roles — `appRoles` | `tenant_members.role`, permission matrix (app config) |
| Audit log — `appAuditLog` | `audit_log_entries` |
| Help — `appHelp` | `help_articles` (reference CMS) |
| Reports — `appReports` | `saved_reports`, `report_templates` |
| Activity — `appActivity` | `activities` (aggregated feed) |
| Files — `appFiles` | `record_files`, `file_folders`, storage quotas |

**`data.json`:** Top-level `app*` keys describe **API-shaped** payloads for each route; column names should align when you model rows (e.g. invoice `client` in JSON → `client_label` in SQL).

**Indexes:** Composite indexes on `(tenant_id, …)` are included for common list filters. Add more when you query by additional fields.

**RLS:** `rls-policies.sql` restricts reads/writes to rows whose `tenant_id` is in `tenant_members` for `auth.uid()`.

import { z } from "zod";
import {
	ForecastPeriodRowSchema,
	FunnelStageSchema,
	LeadSourceDatumSchema,
	RepPerformanceRowSchema,
} from "./common.js";

export const AnalyticsRangeLabelsSchema = z.record(z.string(), z.string());

export const AppAnalyticsDataSchema = z.object({
	source: z.string().optional(),
	rangeLabels: AnalyticsRangeLabelsSchema,
	forecastBase: z.array(ForecastPeriodRowSchema),
	leadSourcesBase: z.array(LeadSourceDatumSchema),
	repPerformanceBase: z.array(RepPerformanceRowSchema),
	funnelBase: z.array(FunnelStageSchema),
});

import { z } from "zod";

export const CalendarDummyEventSchema = z.object({
	id: z.string(),
	title: z.string(),
	time: z.string(),
	color: z.string(),
	dayOfMonth: z.number(),
});

export const AppCalendarDataSchema = z.object({
	source: z.string().optional(),
	note: z.string().optional(),
	dummyEventsTemplate: z.array(CalendarDummyEventSchema),
});

export const EmailMessageSchema = z.object({
	id: z.string(),
	from: z.string(),
	fromEmail: z.string(),
	to: z.string(),
	time: z.string(),
	body: z.string(),
});

export const EmailThreadSchema = z.object({
	id: z.string(),
	subject: z.string(),
	preview: z.string(),
	from: z.string(),
	fromEmail: z.string(),
	time: z.string(),
	unread: z.boolean(),
	starred: z.boolean(),
	messages: z.array(EmailMessageSchema),
});

export const AppEmailDataSchema = z.object({
	source: z.string().optional(),
	threads: z.array(EmailThreadSchema),
});

import { z } from "zod";

export const CmsBlogPostSchema = z.object({
	id: z.number(),
	title: z.string(),
	author: z.string(),
	authorAvatar: z.string(),
	category: z.string(),
	status: z.string(),
	publishDate: z.string(),
	views: z.number(),
	likes: z.number(),
	content: z.string(),
	tags: z.array(z.string()),
	featuredImage: z.string(),
});

export const AppCmsDataSchema = z.object({
	source: z.string().optional(),
	blogs: z.array(CmsBlogPostSchema),
	subscribers: z.array(
		z.object({
			id: z.number(),
			name: z.string(),
			email: z.string(),
			subscribedDate: z.string(),
			status: z.string(),
			avatarColor: z.string(),
		})
	),
	newBlogFormDefaults: z.object({
		title: z.string(),
		content: z.string(),
		author: z.string(),
		category: z.string(),
		tags: z.string(),
		featuredImage: z.string(),
		publishDate: z.string(),
		status: z.string(),
		excerpt: z.string(),
	}),
});

import { z } from "zod";

export const NamedCountSchema = z.object({
	name: z.string(),
	count: z.number(),
});

export const NamedAmountSchema = z.object({
	name: z.string(),
	amount: z.number(),
});

export const NamedValueSchema = z.object({
	name: z.string(),
	value: z.number(),
});

/** Analytics / forecasting row */
export const ForecastPeriodRowSchema = z.object({
	period: z.string(),
	actual: z.number(),
	forecast: z.number(),
	target: z.number(),
});

export const LeadSourceDatumSchema = z.object({
	name: z.string(),
	value: z.number(),
});

export const RepPerformanceRowSchema = z.object({
	name: z.string(),
	deals: z.number(),
	pipeline: z.number(),
	revenue: z.number(),
	winRate: z.number(),
	avgDeal: z.number(),
	activities: z.number(),
});

export const FunnelStageSchema = z.object({
	label: z.string(),
	count: z.number(),
});

/** Home dashboard multi-series chart snapshot */
export const HomeChartMonthRowSchema = z.object({
	name: z.string(),
	leads: z.number(),
	conversion: z.number(),
	sales: z.number(),
	revenue: z.number(),
	companies: z.number(),
});

/** Sales dashboard time-series point */
export const SalesTrendPointSchema = z.object({
	date: z.string(),
	sales: z.number(),
	revenue: z.number(),
});

export const CountryShareSchema = z.object({
	name: z.string(),
	percentage: z.number(),
});

/** Projects page chart row (traffic by channel) */
export const ProjectTrafficRowSchema = z.object({
	date: z.string(),
	Mobile: z.number(),
	Desktop: z.number(),
});

import { z } from "zod";
import { NamedCountSchema } from "./common.js";

export const CompanyRowSchema = z.object({
	name: z.string(),
	owner: z.string(),
	logo: z.string(),
	industry: z.string(),
	rating: z.number(),
	location: z.string(),
});

export const CompaniesDashboardStatsSchema = z.object({
	description: z.string().optional(),
	totalCompanies: z.number(),
	averageRating: z.number(),
	uniqueIndustries: z.number(),
	uniqueLocations: z.number(),
});

export const CompaniesChartSeriesSchema = z.object({
	description: z.string().optional(),
	companiesByIndustry: z.array(NamedCountSchema),
	companiesByLocationTop: z.array(NamedCountSchema),
});

export const AppCompaniesDataSchema = z.object({
	source: z.string().optional(),
	companies: z.array(CompanyRowSchema),
	dashboardStats: CompaniesDashboardStatsSchema,
	chartSeries: CompaniesChartSeriesSchema,
});

import { z } from "zod";

export const ContactEmailStubSchema = z.object({
	subject: z.string(),
	body: z.string(),
	date: z.string(),
});

export const ContactRowSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	phone: z.string(),
	company: z.string(),
	createdAt: z.string(),
	image: z.string(),
	emails: z.array(ContactEmailStubSchema),
});

export const AppContactsDataSchema = z.object({
	source: z.string().optional(),
	contacts: z.array(ContactRowSchema),
});

import { z } from "zod";

export const TimelineEntryTypeSchema = z.enum(["note", "email", "deal", "call"]);

export const TimelineEntrySchema = z.object({
	id: z.string(),
	type: TimelineEntryTypeSchema,
	title: z.string(),
	body: z.string(),
	at: z.string(),
	user: z.string(),
});

export const RecordNoteSchema = z.object({
	id: z.string(),
	body: z.string(),
	at: z.string(),
	author: z.string(),
});

/** Deal row on record detail sidebar */
export const RecordDealBriefSchema = z.object({
	id: z.string(),
	name: z.string(),
	amount: z.number(),
	stage: z.string(),
	closeDate: z.string(),
	rep: z.string(),
});

export const AttachmentFileSchema = z.object({
	id: z.string(),
	name: z.string(),
	size: z.string(),
	type: z.string(),
});

export const LeadDetailProfileSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	phone: z.string(),
	company: z.string(),
	title: z.string(),
	location: z.string(),
	image: z.string(),
	source: z.string(),
	status: z.string(),
	value: z.number(),
	owner: z.string(),
});

export const ContactDetailProfileSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	phone: z.string(),
	company: z.string(),
	title: z.string(),
	location: z.string(),
	image: z.string(),
	owner: z.string(),
	lifecycle: z.string(),
	createdAt: z.string(),
});

export const CrmRecordDetailDataSchema = z.object({
	source: z.string().optional(),
	defaultTimeline: z.array(TimelineEntrySchema),
	timelinesByKey: z.record(z.string(), z.array(TimelineEntrySchema)),
	notesSeedByKey: z.record(z.string(), z.array(RecordNoteSchema)),
	dealsByRecordKey: z.record(z.string(), z.array(RecordDealBriefSchema)),
	filesByRecordKey: z.record(z.string(), z.array(AttachmentFileSchema)),
	leadDb: z.record(z.string(), LeadDetailProfileSchema),
	contactDb: z.record(z.string(), ContactDetailProfileSchema),
});

import { z } from "zod";

export const DealTableRowSchema = z.object({
	id: z.string(),
	name: z.string(),
	company: z.string(),
	value: z.number(),
	stage: z.string(),
	closeDate: z.string(),
	rep: z.string(),
});

export const AppDealsDataSchema = z.object({
	source: z.string().optional(),
	deals: z.array(DealTableRowSchema),
});

export const PipelineStageSchema = z.object({
	id: z.string(),
	label: z.string(),
});

export const PipelineKanbanDealSchema = z.object({
	id: z.string(),
	title: z.string(),
	company: z.string(),
	value: z.number(),
	owner: z.string(),
	stage: z.string(),
	priority: z.string(),
	lastActivity: z.string(),
	daysInStage: z.number(),
	tag: z.string().nullable(),
});

export const AppPipelinesDataSchema = z.object({
	source: z.string().optional(),
	pipelineStages: z.array(PipelineStageSchema),
	initialDealsKanban: z.array(PipelineKanbanDealSchema),
});

import { z } from "zod";

export const EcommerceMetricCardSchema = z.object({
	id: z.string(),
	label: z.string(),
	value: z.string(),
	change: z.number(),
	positive: z.boolean(),
	period: z.string(),
	icon: z.string(),
});

export const EcommerceWeeklyEarningSchema = z.object({
	day: z.string(),
	value: z.number(),
	highlight: z.boolean().optional(),
});

export const EcommerceEarningBreakdownSchema = z.object({
	label: z.string(),
	value: z.string(),
	pct: z.number(),
	icon: z.string(),
});

export const EcommerceOrderStatusProfileSchema = z.object({
	handle: z.string(),
	role: z.string(),
	avatar: z.string(),
	totalOrders: z.number(),
});

export const EcommerceOrderStatusRowSchema = z.object({
	label: z.string(),
	value: z.number(),
	pct: z.number(),
});

export const EcommerceDeliveryPartySchema = z.object({
	name: z.string(),
	address: z.string(),
});

export const EcommerceDeliveryOrderSchema = z.object({
	id: z.string(),
	productId: z.string(),
	sender: EcommerceDeliveryPartySchema,
	receiver: EcommerceDeliveryPartySchema,
});

export const EcommercePopularProductSchema = z.object({
	id: z.string(),
	name: z.string(),
	price: z.number(),
	visitors: z.string(),
	image: z.string(),
});

export const EcommerceProductCategorySchema = z.enum([
	"Smartphone",
	"Laptop",
	"Headphone",
	"Smartwatch",
]);

export const EcommerceProductStatusSchema = z.enum(["Publish", "Inactive"]);

export const EcommerceProductSchema = z.object({
	id: z.string(),
	name: z.string(),
	brand: z.string(),
	imageURL: z.string(),
	category: EcommerceProductCategorySchema,
	inStock: z.boolean(),
	price: z.number(),
	quantity: z.number(),
	status: EcommerceProductStatusSchema,
	description: z.string(),
	sku: z.string(),
	visitors: z.string(),
	createdAt: z.string(),
});

export const EcommerceStoreOrderSchema = z.object({
	id: z.string(),
	orderNumber: z.string(),
	customer: z.string(),
	email: z.string(),
	productId: z.string(),
	productName: z.string(),
	amount: z.number(),
	status: z.string(),
	paymentStatus: z.string(),
	createdAt: z.string(),
	items: z.number(),
	shippingAddress: z.string(),
});

export const AppEcommerceDataSchema = z.object({
	source: z.string().optional(),
	metrics: z.array(EcommerceMetricCardSchema),
	weeklyEarnings: z.array(EcommerceWeeklyEarningSchema),
	earningBreakdown: z.array(EcommerceEarningBreakdownSchema),
	orderStatusProfile: EcommerceOrderStatusProfileSchema,
	orderStatusTabs: z.record(z.string(), z.array(EcommerceOrderStatusRowSchema)),
	deliveryOrders: z.record(z.string(), z.array(EcommerceDeliveryOrderSchema)),
	popularProducts: z.array(EcommercePopularProductSchema),
	products: z.array(EcommerceProductSchema),
	storeOrders: z.array(EcommerceStoreOrderSchema),
});

import { z } from "zod";

export const FileCategorySummarySchema = z.object({
	id: z.string(),
	label: z.string(),
	count: z.number(),
	usedLabel: z.string(),
	percent: z.number(),
	color: z.string(),
	icon: z.string(),
});

export const FileFolderSchema = z.object({
	id: z.string(),
	name: z.string(),
	itemCount: z.number(),
	lastUpdate: z.string(),
	starred: z.boolean(),
});

export const StorageSummarySchema = z.object({
	usedGb: z.number(),
	totalGb: z.number(),
	label: z.string(),
	subtitle: z.string(),
});

export const MonthlyTransferRowSchema = z.object({
	month: z.string(),
	desktop: z.number(),
	mobile: z.number(),
});

export const FileRecordSchema = z.object({
	id: z.string(),
	name: z.string(),
	size: z.string(),
	sizeBytes: z.number(),
	category: z.string(),
	folder: z.string(),
	type: z.string(),
	uploadDate: z.string(),
	uploadedBy: z.string(),
	description: z.string(),
});

export const AppFilesDataSchema = z.object({
	source: z.string().optional(),
	categories: z.array(FileCategorySummarySchema),
	folders: z.array(FileFolderSchema),
	storage: StorageSummarySchema,
	monthlyTransfer: z.array(MonthlyTransferRowSchema),
	files: z.array(FileRecordSchema),
});

import { z } from "zod";
import { NamedAmountSchema, NamedCountSchema } from "./common.js";

export const InvoiceLineItemSchema = z.object({
	name: z.string(),
	quantity: z.number(),
	price: z.number(),
});

export const InvoiceSchema = z.object({
	id: z.string(),
	invoiceNumber: z.string(),
	client: z.string(),
	clientEmail: z.string(),
	amount: z.number(),
	status: z.string(),
	issueDate: z.string(),
	dueDate: z.string(),
	description: z.string(),
	items: z.array(InvoiceLineItemSchema),
});

export const InvoicesDashboardStatsSchema = z.object({
	description: z.string().optional(),
	totalInvoices: z.number(),
	totalBilledUsd: z.number(),
	collectedPaidUsd: z.number(),
	outstandingUsd: z.number(),
});

export const InvoicesChartSeriesSchema = z.object({
	description: z.string().optional(),
	invoiceCountByStatus: z.array(NamedCountSchema),
	amountUsdByStatus: z.array(NamedAmountSchema),
});

export const AppInvoicesDataSchema = z.object({
	source: z.string().optional(),
	initialInvoices: z.array(InvoiceSchema),
	dashboardStats: InvoicesDashboardStatsSchema,
	chartSeries: InvoicesChartSeriesSchema,
});

export const PaymentRecordSchema = z.object({
	id: z.string(),
	transactionId: z.string(),
	amount: z.number(),
	currency: z.string(),
	status: z.string(),
	paymentMethod: z.string(),
	customer: z.string(),
	customerEmail: z.string(),
	description: z.string(),
	date: z.string(),
	time: z.string(),
	fee: z.number(),
	netAmount: z.number(),
	invoiceId: z.string(),
});

export const AppPaymentsDataSchema = z.object({
	source: z.string().optional(),
	initialPayments: z.array(PaymentRecordSchema),
});

import { z } from "zod";
import {
	CountryShareSchema,
	HomeChartMonthRowSchema,
	SalesTrendPointSchema,
} from "./common.js";
import { LeadTableRowSchema } from "./leads.js";

export const HomeDashboardStatsSchema = z.object({
	totalLeads: z.number(),
	conversionRate: z.string(),
	sales: z.string(),
	revenue: z.string(),
});

export const HomeUpcomingMeetingSchema = z.object({
	title: z.string(),
	date: z.string(),
	contact: z.string(),
	subject: z.string(),
	iconType: z.enum(["up", "down"]),
});

export const AppHomeDataSchema = z.object({
	source: z.string().optional(),
	dashboardData: z.object({
		stats: HomeDashboardStatsSchema,
		upcomingMeetings: z.array(HomeUpcomingMeetingSchema),
	}),
	homeLeadsTable: z.array(LeadTableRowSchema),
	sampleChartData: z.array(HomeChartMonthRowSchema),
});

export const SalesDataBundleSchema = z.object({
	daily: z.array(SalesTrendPointSchema),
	weekly: z.array(SalesTrendPointSchema),
	monthly: z.array(SalesTrendPointSchema),
});

export const SalesProductRowSchema = z.object({
	id: z.number(),
	name: z.string(),
	soldAt: z.string(),
	price: z.number(),
	category: z.string(),
	description: z.string(),
});

export const AppSalesDataSchema = z.object({
	source: z.string().optional(),
	salesData: SalesDataBundleSchema,
	topCountries: z.array(CountryShareSchema),
	products: z.array(SalesProductRowSchema),
});

export {
	NamedCountSchema,
	NamedAmountSchema,
	NamedValueSchema,
	ForecastPeriodRowSchema,
	LeadSourceDatumSchema,
	RepPerformanceRowSchema,
	FunnelStageSchema,
	HomeChartMonthRowSchema,
	SalesTrendPointSchema,
	CountryShareSchema,
	ProjectTrafficRowSchema,
} from "./common.js";

export {
	LeadKanbanCardSchema,
	LeadPipelineColumnSchema,
	LeadTableRowSchema,
	LeadsDashboardStatsSchema,
	LeadsChartSeriesSchema,
	AppLeadsDataSchema,
} from "./leads.js";

export {
	TimelineEntryTypeSchema,
	TimelineEntrySchema,
	RecordNoteSchema,
	RecordDealBriefSchema,
	AttachmentFileSchema,
	LeadDetailProfileSchema,
	ContactDetailProfileSchema,
	CrmRecordDetailDataSchema,
} from "./crm-record-detail.js";

export {
	CompanyRowSchema,
	CompaniesDashboardStatsSchema,
	CompaniesChartSeriesSchema,
	AppCompaniesDataSchema,
} from "./companies.js";

export { ContactEmailStubSchema, ContactRowSchema, AppContactsDataSchema } from "./contacts.js";

export {
	InvoiceLineItemSchema,
	InvoiceSchema,
	InvoicesDashboardStatsSchema,
	InvoicesChartSeriesSchema,
	AppInvoicesDataSchema,
	PaymentRecordSchema,
	AppPaymentsDataSchema,
} from "./finance.js";

export {
	DealTableRowSchema,
	AppDealsDataSchema,
	PipelineStageSchema,
	PipelineKanbanDealSchema,
	AppPipelinesDataSchema,
} from "./deals-pipelines.js";

export {
	AnalyticsRangeLabelsSchema,
	AppAnalyticsDataSchema,
} from "./analytics.js";

export {
	NotificationItemSchema,
	AppNotificationsDataSchema,
	TeamRoleOptionSchema,
	TeamMemberSchema,
	AppTeamsDataSchema,
} from "./notifications-teams.js";

export {
	TaskAssigneeSchema,
	TaskCardSchema,
	TaskSectionSchema,
	AppTasksDataSchema,
} from "./tasks.js";

export {
	HomeDashboardStatsSchema,
	HomeUpcomingMeetingSchema,
	AppHomeDataSchema,
	SalesDataBundleSchema,
	SalesProductRowSchema,
	AppSalesDataSchema,
} from "./home-sales.js";

export {
	CalendarDummyEventSchema,
	AppCalendarDataSchema,
	EmailMessageSchema,
	EmailThreadSchema,
	AppEmailDataSchema,
} from "./calendar-email.js";

export {
	ProjectRowSchema,
	ProjectsKpiStripSchema,
	ProjectsChartSeriesSchema,
	AppProjectsDataSchema,
} from "./projects.js";

export {
	SettingsProfileSchema,
	SettingsAccountSchema,
	SettingsNotificationsSchema,
	SettingsPreferencesSchema,
	SettingsBillingSchema,
	SettingsTeamMemberRowSchema,
	SettingsTabSchema,
	AppSettingsDataSchema,
} from "./settings.js";

export {
	IntegrationCardSchema,
	AppIntegrationsDataSchema,
	ApiKeyRecordSchema,
	ApiKeysStatsDisplaySchema,
	AppApiKeysDataSchema,
	WebhookRecordSchema,
	AppWebhooksDataSchema,
} from "./integrations-api.js";

export { CmsBlogPostSchema, AppCmsDataSchema } from "./cms.js";

export {
	EcommerceMetricCardSchema,
	EcommerceWeeklyEarningSchema,
	EcommerceEarningBreakdownSchema,
	EcommerceOrderStatusProfileSchema,
	EcommerceOrderStatusRowSchema,
	EcommerceDeliveryPartySchema,
	EcommerceDeliveryOrderSchema,
	EcommercePopularProductSchema,
	EcommerceProductCategorySchema,
	EcommerceProductStatusSchema,
	EcommerceProductSchema,
	EcommerceStoreOrderSchema,
	AppEcommerceDataSchema,
} from "./ecommerce.js";

export {
	CampaignStatusSchema,
	CampaignStepTypeSchema,
	SegmentEntityTypeSchema,
	MarketingFormStatusSchema,
	MarketingMetricCardSchema,
	SegmentFilterSchema,
	SegmentSchema,
	CampaignStepSchema,
	CampaignSchema,
	MarketingFormFieldSchema,
	MarketingFormSchema,
	FormSubmissionSchema,
	AppMarketingDataSchema,
} from "./marketing.js";

export {
	SubscriptionBillingIntervalSchema,
	SubscriptionPlanStatusSchema,
	SubscriptionStatusSchema,
	SubscriptionEventTypeSchema,
	SubscriptionMetricCardSchema,
	SubscriptionPlanSchema,
	SubscriptionSchema,
	SubscriptionEventSchema,
	AppSubscriptionsDataSchema,
} from "./subscriptions.js";

export {
	ApiLogEntrySchema,
	AppApiLogsDataSchema,
	SupportMessageSchema,
	SupportTicketSchema,
	AppSupportDataSchema,
	AppUserRowSchema,
	AppUsersDataSchema,
	AppRolesDataSchema,
	AuditLogEntrySchema,
	AppAuditLogDataSchema,
	HelpArticleSchema,
	HelpSectionSchema,
	AppHelpDataSchema,
	SavedReportSchema,
	ReportTemplateSchema,
	AppReportsDataSchema,
	ActivityFeedItemSchema,
	AppActivityDataSchema,
} from "./platform.js";

export {
	FileCategorySummarySchema,
	FileFolderSchema,
	StorageSummarySchema,
	MonthlyTransferRowSchema,
	FileRecordSchema,
	AppFilesDataSchema,
} from "./files.js";

import { z } from "zod";

export const IntegrationCardSchema = z.object({
	id: z.string(),
	name: z.string(),
	enabled: z.boolean(),
	tag: z.string(),
	lastUpdated: z.string(),
});

export const AppIntegrationsDataSchema = z.object({
	source: z.string().optional(),
	integrations: z.array(IntegrationCardSchema),
});

export const ApiKeyRecordSchema = z.object({
	id: z.string(),
	name: z.string(),
	key: z.string(),
	createdAt: z.string(),
	updatedAt: z.string(),
	status: z.string(),
});

export const ApiKeysStatsDisplaySchema = z.record(z.string(), z.string());

export const AppApiKeysDataSchema = z.object({
	source: z.string().optional(),
	initialApiKeys: z.array(ApiKeyRecordSchema),
	statsDisplay: ApiKeysStatsDisplaySchema,
});

export const WebhookRecordSchema = z.object({
	id: z.string(),
	name: z.string(),
	url: z.string(),
	events: z.array(z.string()),
	createdAt: z.string(),
	updatedAt: z.string(),
	status: z.string(),
	secret: z.string(),
});

export const AppWebhooksDataSchema = z.object({
	source: z.string().optional(),
	initialWebhooks: z.array(WebhookRecordSchema),
	availableEvents: z.array(z.string()),
});

import { z } from "zod";
import { NamedCountSchema } from "./common.js";

/** Card inside a Kanban column */
export const LeadKanbanCardSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	company: z.string(),
	image: z.string(),
});

export const LeadPipelineColumnSchema = z.object({
	id: z.string(),
	name: z.string(),
	leads: z.array(LeadKanbanCardSchema),
});

export const LeadTableRowSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	lastContacted: z.string(),
	image: z.string(),
});

export const LeadsDashboardStatsSchema = z.object({
	description: z.string().optional(),
	totalLeads: z.number(),
	uniqueAccounts: z.number(),
	activeInPipeline: z.number(),
	closedDone: z.number(),
});

export const LeadsChartSeriesSchema = z.object({
	description: z.string().optional(),
	leadsByPipelineStage: z.array(NamedCountSchema),
	leadsByAccountTop: z.array(NamedCountSchema),
});

export const AppLeadsDataSchema = z.object({
	source: z.string().optional(),
	initialPipelines: z.array(LeadPipelineColumnSchema),
	initialLeadsTable: z.array(LeadTableRowSchema),
	dashboardStats: LeadsDashboardStatsSchema,
	chartSeries: LeadsChartSeriesSchema,
});

import { z } from "zod";

export const CampaignStatusSchema = z.enum(["Draft", "Scheduled", "Active", "Paused", "Completed"]);

export const CampaignStepTypeSchema = z.enum(["email", "wait", "branch"]);

export const SegmentEntityTypeSchema = z.enum(["contact", "lead", "both"]);

export const MarketingFormStatusSchema = z.enum(["Draft", "Active"]);

export const MarketingMetricCardSchema = z.object({
	id: z.string(),
	label: z.string(),
	value: z.string(),
	change: z.number(),
	positive: z.boolean(),
	period: z.string(),
	icon: z.string(),
});

export const SegmentFilterSchema = z.object({
	field: z.string(),
	operator: z.string(),
	value: z.string(),
});

export const SegmentSchema = z.object({
	id: z.string(),
	name: z.string(),
	description: z.string().optional(),
	entityType: SegmentEntityTypeSchema,
	filters: z.array(SegmentFilterSchema),
	contactCount: z.number(),
	createdAt: z.string(),
	updatedAt: z.string(),
});

export const CampaignStepSchema = z.object({
	id: z.string(),
	campaignId: z.string(),
	stepOrder: z.number(),
	stepType: CampaignStepTypeSchema,
	name: z.string(),
	config: z.record(z.string(), z.unknown()).optional(),
});

export const CampaignSchema = z.object({
	id: z.string(),
	name: z.string(),
	description: z.string().optional(),
	status: CampaignStatusSchema,
	segmentId: z.string().nullable(),
	segmentName: z.string().optional(),
	scheduledAt: z.string().nullable(),
	startedAt: z.string().nullable(),
	completedAt: z.string().nullable(),
	sentCount: z.number(),
	deliveredCount: z.number(),
	openRate: z.number().nullable(),
	clickRate: z.number().nullable(),
	bounceRate: z.number().nullable(),
	createdAt: z.string(),
	updatedAt: z.string(),
	steps: z.array(CampaignStepSchema).optional(),
});

export const MarketingFormFieldSchema = z.object({
	id: z.string(),
	label: z.string(),
	type: z.string(),
	required: z.boolean(),
});

export const MarketingFormSchema = z.object({
	id: z.string(),
	name: z.string(),
	description: z.string().optional(),
	fields: z.array(MarketingFormFieldSchema),
	embedCode: z.string(),
	status: MarketingFormStatusSchema,
	submissionCount: z.number(),
	createdAt: z.string(),
	updatedAt: z.string(),
});

export const FormSubmissionSchema = z.object({
	id: z.string(),
	formId: z.string(),
	payload: z.record(z.string(), z.string()),
	sourceUrl: z.string().optional(),
	createdAt: z.string(),
});

export const AppMarketingDataSchema = z.object({
	source: z.string().optional(),
	metrics: z.array(MarketingMetricCardSchema),
	campaigns: z.array(CampaignSchema),
	segments: z.array(SegmentSchema),
	forms: z.array(MarketingFormSchema),
	formSubmissions: z.array(FormSubmissionSchema),
});

import { z } from "zod";

export const NotificationItemSchema = z.object({
	id: z.string(),
	type: z.string(),
	title: z.string(),
	body: z.string(),
	category: z.string(),
	time: z.string(),
	unread: z.boolean(),
	avatar: z.string().optional(),
	actions: z.boolean().optional(),
});

export const AppNotificationsDataSchema = z.object({
	source: z.string().optional(),
	initialNotifications: z.array(NotificationItemSchema),
});

export const TeamRoleOptionSchema = z.object({
	value: z.string(),
	label: z.string(),
});

export const TeamMemberSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	role: z.string(),
	status: z.string(),
	joinedAt: z.string(),
	image: z.string(),
});

export const AppTeamsDataSchema = z.object({
	source: z.string().optional(),
	roleOptions: z.array(TeamRoleOptionSchema),
	initialMembers: z.array(TeamMemberSchema),
});

import { z } from "zod";

export const ApiLogEntrySchema = z.object({
	id: z.string(),
	method: z.string(),
	path: z.string(),
	status: z.number(),
	latencyMs: z.number(),
	apiKey: z.string(),
	timestamp: z.string(),
});

export const AppApiLogsDataSchema = z.object({
	source: z.string().optional(),
	logs: z.array(ApiLogEntrySchema),
	stats: z
		.object({
			total: z.number(),
			errors: z.number(),
			avgLatency: z.number(),
		})
		.optional(),
});

export const SupportMessageSchema = z.object({
	from: z.string(),
	body: z.string(),
	at: z.string(),
});

export const SupportTicketSchema = z.object({
	id: z.string(),
	subject: z.string(),
	customer: z.string(),
	email: z.string(),
	priority: z.string(),
	status: z.string(),
	assignee: z.string(),
	createdAt: z.string(),
	updatedAt: z.string(),
	category: z.string(),
	description: z.string(),
	messages: z.array(SupportMessageSchema),
});

export const AppSupportDataSchema = z.object({
	source: z.string().optional(),
	tickets: z.array(SupportTicketSchema),
});

export const AppUserRowSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	role: z.string(),
	department: z.string(),
	status: z.string(),
	lastActive: z.string(),
});

export const AppUsersDataSchema = z.object({
	source: z.string().optional(),
	users: z.array(AppUserRowSchema),
});

export const AppRolesDataSchema = z.object({
	source: z.string().optional(),
	roles: z.array(z.string()),
	permissions: z.array(z.object({ id: z.string(), label: z.string() })),
	matrix: z.record(z.string(), z.record(z.string(), z.boolean())),
});

export const AuditLogEntrySchema = z.object({
	id: z.string(),
	actor: z.string(),
	action: z.string(),
	resource: z.string(),
	module: z.string(),
	ip: z.string(),
	timestamp: z.string(),
});

export const AppAuditLogDataSchema = z.object({
	source: z.string().optional(),
	entries: z.array(AuditLogEntrySchema),
});

export const HelpArticleSchema = z.object({
	id: z.string(),
	q: z.string(),
	a: z.string(),
});

export const HelpSectionSchema = z.object({
	id: z.string(),
	title: z.string(),
	articles: z.array(HelpArticleSchema),
});

export const AppHelpDataSchema = z.object({
	source: z.string().optional(),
	sections: z.array(HelpSectionSchema),
});

export const SavedReportSchema = z.object({
	id: z.string(),
	name: z.string(),
	type: z.string(),
	schedule: z.string(),
	lastRun: z.string(),
	format: z.string(),
	status: z.string(),
});

export const ReportTemplateSchema = z.object({
	id: z.string(),
	name: z.string(),
	desc: z.string(),
});

export const AppReportsDataSchema = z.object({
	source: z.string().optional(),
	savedReports: z.array(SavedReportSchema),
	templates: z.array(ReportTemplateSchema),
});

export const ActivityFeedItemSchema = z.object({
	id: z.string(),
	type: z.string(),
	title: z.string(),
	body: z.string(),
	actor: z.string(),
	time: z.string(),
	module: z.string(),
});

export const AppActivityDataSchema = z.object({
	source: z.string().optional(),
	feed: z.array(ActivityFeedItemSchema),
});

import { z } from "zod";
import { ProjectTrafficRowSchema } from "./common.js";

export const ProjectRowSchema = z.object({
	id: z.number(),
	projectName: z.string(),
	clientName: z.string(),
	clientAvatarColor: z.string(),
	startDate: z.string(),
	deadline: z.string(),
	status: z.string(),
	progress: z.number(),
});

export const ProjectsKpiStripSchema = z.object({
	activeProjects: z.number(),
	changePercentage: z.number(),
	totalRevenue: z.number(),
	revenueChange: z.number(),
	totalLeads: z.number(),
	leadsChange: z.number(),
});

export const ProjectsChartSeriesSchema = z.object({
	"3months": z.array(ProjectTrafficRowSchema),
	"30days": z.array(ProjectTrafficRowSchema),
	"7days": z.array(ProjectTrafficRowSchema),
});

export const AppProjectsDataSchema = z.object({
	source: z.string().optional(),
	initialProjects: z.array(ProjectRowSchema),
	kpiStrip: ProjectsKpiStripSchema,
	chartSeries: ProjectsChartSeriesSchema,
});

import { z } from "zod";

export const SettingsProfileSchema = z.object({
	name: z.string(),
	email: z.string(),
	phone: z.string(),
	company: z.string(),
	role: z.string(),
	avatar: z.string(),
	bio: z.string(),
});

export const SettingsAccountSchema = z.object({
	currentPassword: z.string(),
	newPassword: z.string(),
	confirmPassword: z.string(),
	twoFactorEnabled: z.boolean(),
	apiKey: z.string(),
});

export const SettingsNotificationsSchema = z.object({
	emailNotifications: z.boolean(),
	smsNotifications: z.boolean(),
	leadAlerts: z.boolean(),
	dealUpdates: z.boolean(),
	weeklyReports: z.boolean(),
	marketingEmails: z.boolean(),
});

export const SettingsPreferencesSchema = z.object({
	theme: z.string(),
	language: z.string(),
	timezone: z.string(),
	dateFormat: z.string(),
	currency: z.string(),
});

export const SettingsBillingSchema = z.object({
	plan: z.string(),
	status: z.string(),
	nextBillingDate: z.string(),
	paymentMethod: z.string(),
});

export const SettingsTeamMemberRowSchema = z.object({
	id: z.string(),
	name: z.string(),
	email: z.string(),
	role: z.string(),
	status: z.string(),
	image: z.string(),
});

export const SettingsTabSchema = z.object({
	id: z.string(),
	label: z.string(),
});

export const AppSettingsDataSchema = z.object({
	source: z.string().optional(),
	profileSettings: SettingsProfileSchema,
	accountSettings: SettingsAccountSchema,
	notificationSettings: SettingsNotificationsSchema,
	preferenceSettings: SettingsPreferencesSchema,
	billingSettings: SettingsBillingSchema,
	teamMembers: z.array(SettingsTeamMemberRowSchema),
	tabs: z.array(SettingsTabSchema),
});

import { z } from "zod";

export const SubscriptionBillingIntervalSchema = z.enum(["Monthly", "Yearly"]);

export const SubscriptionPlanStatusSchema = z.enum(["Active", "Archived"]);

export const SubscriptionStatusSchema = z.enum([
	"Active",
	"Trialing",
	"Past Due",
	"Canceled",
	"Paused",
]);

export const SubscriptionEventTypeSchema = z.enum([
	"created",
	"renewed",
	"upgraded",
	"downgraded",
	"canceled",
	"payment_failed",
	"reactivated",
]);

export const SubscriptionMetricCardSchema = z.object({
	id: z.string(),
	label: z.string(),
	value: z.string(),
	change: z.number(),
	positive: z.boolean(),
	period: z.string(),
	icon: z.string(),
});

export const SubscriptionPlanSchema = z.object({
	id: z.string(),
	name: z.string(),
	sku: z.string(),
	tier: z.string(),
	price: z.number(),
	currency: z.string(),
	billingInterval: SubscriptionBillingIntervalSchema,
	features: z.array(z.string()),
	maxSeats: z.number(),
	trialDays: z.number(),
	status: SubscriptionPlanStatusSchema,
	activeSubscriptions: z.number(),
	createdAt: z.string(),
	updatedAt: z.string(),
});

export const SubscriptionSchema = z.object({
	id: z.string(),
	planId: z.string(),
	planName: z.string(),
	customerName: z.string(),
	customerEmail: z.string(),
	companyName: z.string().optional(),
	status: SubscriptionStatusSchema,
	mrr: z.number(),
	billingInterval: SubscriptionBillingIntervalSchema,
	currentPeriodStart: z.string().nullable(),
	currentPeriodEnd: z.string().nullable(),
	cancelAtPeriodEnd: z.boolean(),
	canceledAt: z.string().nullable(),
	startedAt: z.string().nullable(),
	createdAt: z.string(),
	updatedAt: z.string(),
});

export const SubscriptionEventSchema = z.object({
	id: z.string(),
	subscriptionId: z.string(),
	eventType: SubscriptionEventTypeSchema,
	description: z.string(),
	metadata: z.record(z.string(), z.unknown()).optional(),
	createdAt: z.string(),
});

export const AppSubscriptionsDataSchema = z.object({
	source: z.string().optional(),
	metrics: z.array(SubscriptionMetricCardSchema),
	plans: z.array(SubscriptionPlanSchema),
	subscriptions: z.array(SubscriptionSchema),
	subscriptionEvents: z.array(SubscriptionEventSchema),
});

import { z } from "zod";

export const TaskAssigneeSchema = z.object({
	initials: z.string(),
});

export const TaskCardSchema = z.object({
	id: z.string(),
	name: z.string(),
	description: z.string(),
	progress: z.string(),
	icon: z.string(),
	progressPercent: z.number(),
	priority: z.string(),
	attachments: z.number(),
	comments: z.number(),
	assignees: z.array(TaskAssigneeSchema),
});

export const TaskSectionSchema = z.object({
	id: z.string(),
	name: z.string(),
	tasks: z.array(TaskCardSchema),
});

export const AppTasksDataSchema = z.object({
	source: z.string().optional(),
	taskSections: z.array(TaskSectionSchema),
});