# Buddy CRM — Database Schema Reference

Last updated: 2026-05-06

Database: **Supabase (PostgreSQL)**, shared with the original Buddy app.

This document describes the tables this CRM fork actually reads or writes. The shared Supabase project also contains `project_buddy_*`, `learn_*`, and `quote_buddy_signing_audit` tables used by the original Buddy app — the CRM does not touch those, and they are not documented here.

The authoritative DDL for CRM-specific tables is in the `supabase-migration-*.sql` files at the repo root. Tables created before the CRM fork (`leads`, `salespeople`, `pricebook`, `feature_flags`, `user_permissions`, `mrr_*`, `quote_buddy_quotes`) were originally created via the Supabase Dashboard; their column lists below are reconstructed from the migration files that ALTER them and from the function code.

---

## Table Overview

| Domain | Table | Created by | Notes |
|---|---|---|---|
| **CRM core** | `leads` | dashboard (legacy) | Lead records |
| | `lead_activities` | dashboard (legacy) | Activity log; extended by email & calendar migrations |
| | `salespeople` | dashboard (legacy) | BDM directory |
| **Lead reference data** | `lead_sources` | dashboard | CRUD via `lead-sources.js` |
| | `lead_regions` | dashboard | CRUD via `lead-regions.js` |
| | `lead_products` | dashboard | Interested-products list |
| | `lead_lost_reasons` | dashboard | Closed-lost reasons list |
| | `partner_accounts` | `supabase-migration-partner-accounts.sql` | 3rd-party reseller list — drives the Partner Account dropdown on Lead + Opp forms |
| **Opportunities** | `opportunities` | `supabase-migration-opportunities.sql` | Deal pipeline |
| | `opportunity_activities` | same | Activity log per opp |
| | `opportunity_stage_history` | same | Stage durations |
| | `opportunity_contact_roles` | same | Multiple contacts per opp |
| | `opportunity_competitors` | same | Competitor tracking |
| | `opportunity_line_items` | `supabase-migration-opportunity-line-items.sql` | Estimated-value builder |
| **Quotes** | `quote_buddy_quotes` | dashboard (legacy) | Extended by versioning + opp link migrations |
| **Email sync** | `email_messages` | `supabase-migration-email-sync-2-1.sql` | Full body of emails sent/received |
| | `user_graph_tokens` | `supabase-migration-email-sync-2-2.sql` | AES-256-GCM-encrypted refresh tokens |
| | `email_sync_log` | same | One row per scheduled run |
| | `email_subscriptions` | `supabase-migration-email-sync-3.sql` | Microsoft Graph webhook subscriptions |
| | `email_review_queue` | same | Messages from `@Buddy/Auto-Log` folder needing manual match |
| **Calendar sync** | `meeting_events` | `supabase-migration-calendar-sync-phase-3.sql` + `-meeting-outcomes.sql` | Imported customer meetings + outcomes |
| | `meeting_review_queue` | `supabase-migration-calendar-sync-phase-3.sql` | Ambiguous-match queue |
| **Reports** | `saved_reports` | `supabase-migration-saved-reports.sql` | Report Builder specs |
| **Sales home** | `salesperson_targets` | `supabase-migration-salesperson-targets.sql` | Per-BDM monthly goals |
| **Skill queue** | `skill_queue_skills` | `supabase-migration-skill-queue.sql` | Registered skills |
| | `skill_queue_tasks` | same | Work queue |
| **Access control** | `feature_flags` | dashboard (legacy) | Feature gates |
| | `user_permissions` | dashboard (legacy) | Permission levels |
| **Pricebook** | `pricebook` | dashboard (legacy) | Product catalog |
| **Finance (legacy)** | `mrr_months`, `mrr_entries`, `mrr_audit` | dashboard (legacy) | MRR tracking |

Roughly 28 CRM-touched tables.

---

## Detailed Schemas

### CRM Core

#### `leads`

Authoritative columns are inferred from `leads.js` plus the additions made by `supabase-migration-opportunities.sql`. Required fields for create: `last_name`, `company`, `lead_channel`.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `salutation`, `first_name`, `middle_name`, `last_name`, `suffix` | TEXT | `last_name` required |
| `title` | TEXT | Job title |
| `company` | TEXT | Required |
| `email`, `phone`, `mobile` | TEXT | |
| `lead_type` | TEXT | Default `'Business Customer'` |
| `no_of_employees` | TEXT/INT | |
| `lead_owner_email`, `lead_owner_name` | TEXT | Assigned BDM |
| `lead_status` | TEXT | Enum: `New`, `Working`, `On Hold`, `Closed/Lost`, `Converted` (TitleCase strings; `Closed/Lost` requires `lost_reasons`) |
| `lost_reasons` | TEXT | Required when `lead_status = 'Closed/Lost'` |
| `lead_channel` | TEXT | Required |
| `lead_source` | TEXT | Default `'Self-sourced Referral'` |
| `priority` | TEXT | Temperature: `Cold` / `Warm` / `Hot` (current naming). Legacy values `Low` / `Medium` / `High` still mapped for backwards compat. Column kept as `priority` for compat. |
| `rating` | TEXT | Deprecated — superseded by `priority` (Temperature). Kept on existing rows; not written by current UI. |
| `industry_level_1` … `_4`, `industry_code`, `industry_additional_info` | TEXT | ANZSIC classification |
| `permission_to_call`, `agreed_to_contact_timeframe`, `best_time_to_call` | mixed | |
| `product_interests` | TEXT/JSONB | |
| `referrer_*`, `partner_account` | TEXT | Referral fields |
| `description` | TEXT | |
| `now_account_id`, `legacy_sf_id`, `billing_zip`, `customer_status`, `account_tier` | TEXT | Added by `supabase-migration-leads-mailchimp-fields.sql` (2026-05-19). `now_account_id` parallels `opportunities.now_account_id` (Spice bridge). `legacy_sf_id` parallels SF Account ID reference. Populated by the Mailchimp import flow from `NOWACCTID` / `SFACCTID` / `BILLZIP` / `CSTATUS` / `ACCTIER` merge fields. |
| `mailchimp_tags` | TEXT[] | Added by same migration. Verbatim copy of every Mailchimp tag the subscriber had at import time (e.g. `Capricorn Event Sign Up 2026`, `SF Product: Mobile`, `Marketing Sign Up`). Mapped tags (`SF Product:`, `Partner:`) also populate `product_interests` / `partner_account`; this stays as the raw source-of-truth for Report Builder filtering. Carried through Lead → Opp conversion. |
| `is_converted` | BOOLEAN | Added by opps migration |
| `converted_opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE SET NULL |
| `created_by_*`, `last_modified_by_*`, `last_activity_date`, `last_transfer_date` | mixed | Audit fields |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

#### `lead_activities`

Base columns plus extensions from email-sync 2-1 and calendar-sync phase 3.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `lead_id` | UUID | FK → `leads(id)` |
| `activity_type` | TEXT | e.g. `created`, `status_change`, `email_sent`, `email_received`, `meeting_logged`, `meeting_outcome` |
| `description`, `field`, `old_value`, `new_value` | TEXT | |
| `performed_by_email`, `performed_by_name` | TEXT | |
| `message_id`, `conversation_id`, `internet_message_id` | TEXT | Microsoft Graph identifiers |
| `received_at` | TIMESTAMPTZ | |
| `email_message_id` | UUID | FK → `email_messages(id)` (lazy-fetch body) |
| `meeting_event_id` | UUID | FK → `meeting_events(id)` (lazy-fetch detail) |
| `created_at` | TIMESTAMPTZ | |

Indexes: `idx_lead_acts_conv`, `idx_lead_acts_msg`, `idx_lead_acts_meeting` (partial, where the column is non-null).

#### `salespeople`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `name`, `email` | TEXT | `email` UNIQUE |
| `title`, `phone`, `location`, `department` | TEXT | `department` used for feature-flag dept filtering |
| `is_active` | BOOLEAN | DEFAULT true (soft delete) |
| `sort_order` | INTEGER | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

---

### Lead Reference Data

`lead_sources`, `lead_regions`, `lead_products`, `lead_lost_reasons`, `partner_accounts` all follow the same shape (managed via dedicated `manage-*.html` pages and `lead-*.js` / `partner-accounts.js` functions):

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `name` | TEXT | NOT NULL, UNIQUE |
| `is_active` | BOOLEAN | DEFAULT true (soft delete) |
| `sort_order` | INTEGER | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

---

### Opportunities

Source: `supabase-migration-opportunities.sql` plus `-line-items`, `-contact-role`, and the quote denormalisation columns added by `-quote-versioning`.

#### `opportunities`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `opportunity_name` | TEXT | NOT NULL |
| `account_name` | TEXT | |
| `amount` | NUMERIC | DEFAULT 0 |
| `expected_delivery_date` | DATE | |
| `stage` | TEXT | NOT NULL DEFAULT `'Qualification'`. Values: `Qualification`, `Needs Analysis`, `Quote/Proposal`, `Negotiation`, `Contracted`, `Sent to Onboarding`, `On Hold`, `Closed Lost`. **2026-05-14 rename:** `Closed Won` merged INTO `Contracted` (Contracted is now the won terminal state); `Fulfilment` renamed to `Sent to Onboarding`. Historical `opportunity_activities` rows preserve the old names as audit trail. |
| `probability` | INTEGER | 0–100 (CHECK), DEFAULT 10 |
| `expected_revenue` | NUMERIC | **Generated** as `amount * probability / 100.0`, STORED |
| `forecast_category` | TEXT | DEFAULT `'Pipeline'`. Values: `Pipeline`, `Best Case`, `Commit`, `Closed`, `Omitted` |
| `opportunity_type` | TEXT | DEFAULT `'New Business'` |
| `lead_source`, `lead_channel`, `next_step`, `description` | TEXT | |
| `partner_account` | TEXT | Optional 3rd-party reseller attribution. Free-text storage matching the `lead_sources`/`lead_channel` pattern; values constrained by app code to names in `partner_accounts.name`. Added by `supabase-migration-partner-accounts.sql` (2026-05-14). Persists from Lead → Opp conversion. |
| `mailchimp_tags` | TEXT[] | Added by `supabase-migration-leads-mailchimp-fields.sql` (2026-05-19). Inherited from the originating Lead at convert time — same shape as `leads.mailchimp_tags`. Lets Sales Ops filter opps by marketing-tagged segments via Report Builder. |
| `owner_email`, `owner_name` | TEXT | |
| `lead_id` | UUID | FK → `leads(id)` ON DELETE SET NULL |
| `contact_name`, `contact_email`, `contact_phone`, `contact_role` | TEXT | Primary contact denormalised here; `contact_role` added by `-contact-role` migration |
| `is_closed`, `is_won` | BOOLEAN | |
| `closed_at` | TIMESTAMPTZ | |
| `contract_signed_date` | TIMESTAMPTZ | Canonical "sale won" timestamp — set when an opp enters a won stage. Added by `-opportunity-contract-signed-date`. Indexed (`idx_opportunities_contract_signed_date`, partial `WHERE NOT NULL`). |
| `signed_contract_path` | TEXT | Supabase Storage path (`quotes` bucket, `manual/<opp_id>.pdf`) of a manually-uploaded signed contract PDF. Added by `-opportunity-manual-contract`. |
| `signed_contract_file_name` | TEXT | Original filename of the uploaded contract. |
| `signed_contract_uploaded_at` | TIMESTAMPTZ | When the manual contract was uploaded. |
| `signed_contract_uploaded_by` | TEXT | Email of the BDM who uploaded it. |
| `contract_sign_method` | TEXT | How the deal was signed: `manual` \| `esign` \| `docusign` (NULL = unsigned / legacy). CHECK `opportunities_contract_sign_method_check`. Added by `-opportunity-manual-contract`. |
| `lost_reason` | TEXT | |
| `created_by_email`, `created_by_name`, `last_modified_by_*`, `last_activity_date` | mixed | |
| `quote_count` | INTEGER | NOT NULL DEFAULT 0 — maintained by `trg_opp_quote_stats` |
| `first_quote_sent_at` | TIMESTAMPTZ | maintained by trigger |
| `last_quote_sent_at` | TIMESTAMPTZ | maintained by trigger |
| `time_to_first_quote_hours` | NUMERIC | **Generated** column, STORED |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

Indexes: `idx_opp_stage`, `idx_opp_owner`, `idx_opp_lead_id`, `idx_opp_delivery_date`, `idx_opp_is_closed`, `idx_opp_forecast`, `idx_opp_last_quote_sent_at`.

#### `opportunity_activities`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE CASCADE |
| `activity_type`, `description`, `field`, `old_value`, `new_value` | TEXT | |
| `performed_by_email`, `performed_by_name` | TEXT | |
| `message_id`, `conversation_id`, `internet_message_id`, `received_at`, `email_message_id`, `meeting_event_id` | mixed | Same email/calendar extensions as `lead_activities` |
| `created_at` | TIMESTAMPTZ | |

#### `opportunity_stage_history`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE CASCADE |
| `stage` | TEXT | NOT NULL |
| `entered_at` | TIMESTAMPTZ | NOT NULL DEFAULT now() |
| `exited_at` | TIMESTAMPTZ | |
| `duration_hours` | NUMERIC | |
| `performed_by_email` | TEXT | |
| `created_at` | TIMESTAMPTZ | |

#### `opportunity_contact_roles`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE CASCADE |
| `contact_name` | TEXT | NOT NULL |
| `contact_email`, `contact_phone`, `contact_title` | TEXT | |
| `role` | TEXT | NOT NULL DEFAULT `'Influencer'` |
| `is_primary` | BOOLEAN | |
| `notes` | TEXT | |
| `created_at` | TIMESTAMPTZ | |

#### `opportunity_competitors`

`(id, opportunity_id FK CASCADE, competitor_name, strengths, weaknesses, notes, created_at)`.

#### `opportunity_line_items`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE CASCADE |
| `category` | TEXT | NOT NULL CHECK in `('monthly', 'oneoff', 'hardware')` |
| `description` | TEXT | |
| `qty` | NUMERIC | NOT NULL DEFAULT 1 |
| `unit_price` | NUMERIC | NOT NULL DEFAULT 0 |
| `discount_pct` | NUMERIC | 0–100 (CHECK) |
| `line_total` | NUMERIC | **Generated** as `qty * unit_price * (1 - discount_pct/100)`, STORED |
| `sort_order` | INTEGER | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

Indexes: `idx_opp_line_items_opp_id`, `idx_opp_line_items_opp_cat_sort`.

---

### Quotes

#### `quote_buddy_quotes`

Original columns (from the legacy quote schema) plus extensions from `-opportunities` and `-quote-versioning`. Note: e-signature columns (`signing_token`, `signed_pdf_path`, etc.) are still present on the table but are not used by the CRM fork.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `company_name`, `contact_name`, `contact_email` | TEXT | |
| `salesperson_id` | UUID | FK → `salespeople(id)` |
| `status` | TEXT | Canonical values: `draft`, `sent`, `accepted`, `rejected`, `expired`, `superseded` |
| `pdf_path` | TEXT | |
| `quote_total` | NUMERIC | |
| `valid_days` | INTEGER | DEFAULT 30 |
| `expires_at`, `sent_at`, `viewed_at` | TIMESTAMPTZ | |
| `version` | INTEGER | NOT NULL DEFAULT 1 (added by `-quote-versioning`) |
| `first_sent_at`, `last_sent_at`, `accepted_at`, `rejected_at`, `expired_at` | TIMESTAMPTZ | added by `-quote-versioning` |
| `cloned_from_quote_id` | UUID | FK → `quote_buddy_quotes(id)` |
| `opportunity_id` | UUID | FK → `opportunities(id)` ON DELETE SET NULL |
| `is_primary_quote` | BOOLEAN | DEFAULT false |
| `quote_mode` | TEXT | NOT NULL DEFAULT `'proposal'`. Values: `'proposal'` (the 8-10 page Proposal Document) or `'simple'` (lighter Simple Quote — skips Requirements page in PDF + reworked Thank You paragraph). Enforced by `quote_buddy_quotes_mode_check` CHECK constraint. Added by `supabase-migration-quote-mode.sql` (2026-05-19). |
| `created_by`, `created_at`, `updated_at` | mixed | |

Trigger `quote_stats_trg` (AFTER INSERT/UPDATE/DELETE) calls `recompute_opp_quote_stats(p_opp_id)` to maintain the denormalised quote stats on `opportunities`.

---

### Email Sync

#### `email_messages`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `message_id`, `conversation_id`, `internet_message_id`, `in_reply_to` | TEXT | Microsoft Graph + RFC 2822 IDs |
| `direction` | TEXT | NOT NULL CHECK in `('outbound', 'inbound')` |
| `subject`, `from_email`, `from_name` | TEXT | |
| `to_emails`, `cc_emails`, `bcc_emails` | TEXT[] | |
| `body_html`, `body_text` | TEXT | |
| `lead_id`, `opportunity_id` | UUID | FK SET NULL |
| `sender_user_email` | TEXT | The BDM whose mailbox this came from / went out of |
| `sent_at`, `received_at`, `created_at`, `updated_at` | TIMESTAMPTZ | |

Cross-mailbox dedup: unique partial index on `internet_message_id` where non-null.

#### `user_graph_tokens`

| Column | Type | Notes |
|---|---|---|
| `email` | TEXT | PRIMARY KEY |
| `name` | TEXT | |
| `refresh_token_encrypted` | TEXT | NOT NULL. Format: `<iv_b64>:<authTag_b64>:<ciphertext_b64>` (AES-256-GCM) |
| `access_token_encrypted` | TEXT | |
| `access_token_expires_at` | TIMESTAMPTZ | |
| `scopes` | TEXT[] | |
| `tenant_id` | TEXT | |
| `last_refreshed_at`, `last_sync_at` | TIMESTAMPTZ | |
| `last_sync_error` | TEXT | |
| `last_sync_messages_imported` | INTEGER | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

Encryption key: `EMAIL_TOKEN_KEY` env var (32-byte hex). Rotating it requires a token re-encryption migration — don't rotate casually.

#### `email_sync_log`

`(id, run_at, user_email, conversations_polled, messages_imported, error, duration_ms)`. One row per scheduled poller run.

#### `email_subscriptions`

Tracks active Microsoft Graph webhook subscriptions for the `@Buddy/Auto-Log` folder per BDM.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `user_email` | TEXT | NOT NULL |
| `subscription_id` | TEXT | UNIQUE (UUID issued by Microsoft) |
| `resource` | TEXT | e.g. `/me/mailFolders/{id}/messages` |
| `client_state` | TEXT | Random opaque string verified on each notification |
| `notification_url` | TEXT | The `graph-webhook.js` Netlify endpoint URL |
| `expires_at` | TIMESTAMPTZ | NOT NULL |
| `folder_id`, `folder_name` | TEXT | |
| `created_at`, `renewed_at` | TIMESTAMPTZ | |

Subscriptions expire every ~3 days; the email-sync poller renews any row with `expires_at` within 24h.

#### `email_review_queue`

Messages dragged into the `@Buddy/Auto-Log` folder that didn't auto-match a Lead/Opp.

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `user_email` | TEXT | NOT NULL |
| `message_id`, `internet_message_id`, `conversation_id` | TEXT | |
| `subject`, `body_preview`, `from_email`, `from_name` | TEXT | |
| `to_emails`, `cc_emails` | TEXT[] | |
| `received_at` | TIMESTAMPTZ | |
| `suggested_lead_id`, `suggested_opportunity_id` | UUID | FK SET NULL |
| `status` | TEXT | `pending` / `matched` / `dismissed` |
| `matched_to_lead_id`, `matched_to_opportunity_id`, `matched_at`, `matched_by_email` | mixed | |
| `created_at` | TIMESTAMPTZ | |

---

### Calendar Sync

#### `meeting_events`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `event_id` | TEXT | Microsoft Graph `/me/events/{id}` |
| `ical_uid` | TEXT | Stable cross-calendar key (UNIQUE partial index) |
| `series_master_id` | TEXT | For occurrences of recurring events |
| `subject`, `body_html`, `body_text`, `location`, `online_meeting_url` | TEXT | |
| `organizer_email`, `organizer_name` | TEXT | |
| `attendees` | JSONB | `[{email, name, type, status}, …]` |
| `start_at`, `end_at` | TIMESTAMPTZ | |
| `is_all_day` | BOOLEAN | |
| `lead_id`, `opportunity_id` | UUID | FK SET NULL |
| `sender_user_email` | TEXT | The BDM whose calendar this came from |
| `outcome` | TEXT | NULL or `completed` / `no_show` / `cancelled` / `rescheduled` (CHECK) |
| `outcome_at`, `outcome_by_email`, `outcome_notes` | mixed | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

Indexes: `uq_meeting_events_ical_uid` (UNIQUE partial), `idx_meeting_events_lead`, `idx_meeting_events_opp`, `idx_meeting_events_start` (DESC), `idx_meeting_events_pending_outcome` (partial WHERE outcome IS NULL).

> Recurring meetings — every occurrence currently renders independently (one row per occurrence). Acceptable for read-only v1, but a real refactor when write-back lands. See the handover.

#### `meeting_review_queue`

Same shape as `email_review_queue` but for ambiguous calendar matches (2+ attendee matches).

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `user_email` | TEXT | NOT NULL |
| `event_id` | TEXT | NOT NULL — Graph event id |
| `ical_uid` | TEXT | UNIQUE partial |
| `subject`, `body_preview`, `location` | TEXT | |
| `organizer_email`, `organizer_name` | TEXT | |
| `attendees` | JSONB | |
| `start_at`, `end_at` | TIMESTAMPTZ | |
| `suggested_lead_id`, `suggested_opportunity_id` | UUID | |
| `status` | TEXT | `pending` / `matched` / `dismissed` |
| `matched_to_lead_id`, `matched_to_opportunity_id`, `matched_at`, `matched_by_email` | mixed | |
| `created_at` | TIMESTAMPTZ | |

---

### Reports

#### `saved_reports`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `name`, `description` | TEXT | |
| `entity` | TEXT | NOT NULL CHECK in `('leads', 'opportunities', 'opportunity_stage_history', 'opportunity_activities', 'lead_activities', 'meeting_events')` |
| `spec` | JSONB | NOT NULL — the full POST body the Report Builder sends |
| `owner_email`, `owner_name` | TEXT | |
| `is_shared` | BOOLEAN | If true, visible to all users with `report_builder` access |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

The CHECK constraint has been opened twice — by `-reports-2-entity-expand.sql` and again by the calendar Phase 3 migration.

---

### Sales Home

#### `salesperson_targets`

Per-BDM monthly goals consumed by the Sales Home dashboard.

| Column | Type | Notes |
|---|---|---|
| `email` | TEXT | NOT NULL, part of PK |
| `month` | DATE | First day of the month at 00:00 NZ time, part of PK |
| `lead_target` | INTEGER | DEFAULT 40 |
| `mrr_target` | NUMERIC(10,2) | DEFAULT 4000 |
| `qualified_mrr_target` | NUMERIC(10,2) | DEFAULT 4000 |
| `notes` | TEXT | |
| `created_at`, `updated_at` | TIMESTAMPTZ | trigger maintains `updated_at` |

If no row exists for a `(BDM, month)` pair, `sales-home.js` falls back to the schema defaults — so the table can be empty on day one.

---

### Skill Queue

#### `skill_queue_skills`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `name` | TEXT | UNIQUE NOT NULL |
| `description` | TEXT | |
| `skill_path` | TEXT | e.g. `.claude/skills/sales-buddy/` |
| `payload_schema` | JSONB | Schema for the task payload |
| `enabled` | BOOLEAN | DEFAULT true |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

Seeded skills: `sales_research`, `lead_enrichment`, `quote_generation`, `salesforce_order`, `audience_buddy`.

#### `skill_queue_tasks`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `title` | TEXT | NOT NULL |
| `skill_name` | TEXT | NOT NULL, FK → `skill_queue_skills(name)` |
| `status` | TEXT | NOT NULL DEFAULT `'pending'`, CHECK in `('pending', 'in_progress', 'complete', 'failed', 'cancelled')` |
| `priority` | INTEGER | NOT NULL DEFAULT 3, CHECK 1–5 |
| `payload` | JSONB | |
| `submitted_by` | TEXT | (legacy column — `submitted_by_email` may also exist; functions reference both) |
| `source` | TEXT | DEFAULT `'dashboard'` |
| `result` | JSONB | |
| `error_message` | TEXT | |
| `created_at`, `started_at`, `completed_at`, `updated_at` | TIMESTAMPTZ | |

Partial index `idx_sq_tasks_pending` ON `(priority ASC, created_at ASC)` WHERE `status = 'pending'`.

---

### Access Control

#### `feature_flags`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `key` | TEXT | UNIQUE — feature identifier (e.g. `opportunity_buddy`, `report_builder`) |
| `label`, `description` | TEXT | |
| `enabled` | BOOLEAN | Global kill switch |
| `min_permission` | TEXT | `user` / `manager` / `admin` / `super_admin` |
| `departments` | JSONB | Array of department strings (empty = all). super_admin bypasses |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

#### `user_permissions`

`(id, email UNIQUE lowercase, permission, created_at, updated_at)`.

---

### Pricebook

#### `pricebook`

| Column | Type | Notes |
|---|---|---|
| `id` | UUID | PK |
| `category` | TEXT | `monthly` / `oneoff` / `hardware` |
| `name` | TEXT | NOT NULL |
| `price` | NUMERIC | >= 0 |
| `is_active` | BOOLEAN | soft delete |
| `sort_order` | INTEGER | |
| `created_at`, `updated_at` | TIMESTAMPTZ | |

UNIQUE `(category, name)` — `pricebook_category_name_unique`.

---

### Finance (legacy)

`mrr_months`, `mrr_entries`, `mrr_audit` — same shape as the original Buddy. Used by the legacy MRR module on this CRM. See [docs/database-schema.md history in git](#) if you need column-level detail; it has not changed in this fork.

---

## RPC Functions

### `opportunity_pipeline_summary()`
Returns `(stage, opp_count, total_amount, weighted_amount)` for non-closed opps, ordered through the canonical stage list.

### `opportunity_forecast_summary(p_owner_email TEXT DEFAULT NULL)`
Same shape as above but grouped by `forecast_category`. Filters by `owner_email` if a non-null arg is supplied.

### `recompute_opp_quote_stats(p_opp_id UUID)`
Recomputes `opportunities.quote_count`, `first_quote_sent_at`, `last_quote_sent_at` from current `quote_buddy_quotes` rows for the given opp. Called by the `quote_stats_trg` trigger.

### `skill_queue_claim_task(p_task_id UUID)`
Atomic task claim. Sets `status='in_progress'` only if the row is still `pending`. Returns `{claimed: true, task: {...}}` or `{claimed: false, reason: 'Task not found or no longer pending'}`.

### Trigger functions
- `trg_opp_quote_stats()` — fires `recompute_opp_quote_stats` on quote insert/update/delete (handles opp moves)
- `trg_salesperson_targets_updated_at()` — touches `updated_at`
- `skill_queue_set_updated_at()` — same, on both skill-queue tables

---

## Row Level Security & Realtime

Neither is in active use in the CRM fork. RLS is disabled on all CRM tables — access control is enforced in application code via `checkFeatureAccess()` and per-function ownership checks.

The shared Supabase project enables RLS + Realtime on three `project_buddy_*` tables for the original Buddy app, but those tables are not touched by this CRM.

---

## Storage Buckets

The CRM fork uses one Supabase Storage bucket, **`quotes`** (shared with the original Buddy app):

- **E-sign flow** (`quote-signing.js`) — `unsigned/<quote_id>.pdf` + `signed/<quote_id>.pdf`, keyed off `quote_buddy_quotes`.
- **Manual signed-contract upload** (`contract-upload.js`) — `manual/<opportunity_id>.pdf`, keyed off `opportunities` (see the `signed_contract_*` columns above). The browser uploads straight to Storage via a short-lived **signed upload URL** minted server-side, bypassing Netlify's ~6 MB function-body cap — the PDF bytes never pass through the function.
- `quote-generate.js` returns generated PDFs directly to the browser (or uploads only when the original Buddy schema is in play).

PDFs are served as 1-hour signed URLs (`createSignedUrl(path, 3600)`); there is no public-read access.

---

## Standard migration template (post 2026-10-30)

From 30 October 2026, new tables in the `public` schema need explicit GRANTs to be reachable via `supabase-js` (Supabase Data API policy change). Existing tables are unaffected — they keep their current grants forever. The template below is the copy-paste pattern for any new `supabase-migration-*.sql` that creates a NEW table. Tables created **before** the cutoff (e.g. anything in this repo as of 2026-05-14) inherit the old default grants and don't need to be backfilled.

```sql
-- Idempotent guard so the migration is safe to re-run.
CREATE TABLE IF NOT EXISTS public.your_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- columns here
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Required from 30 Oct 2026. Safe to apply now — these grants match the
-- pre-cutoff defaults, so re-running has no effect on behaviour.
GRANT SELECT                          ON public.your_table TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE  ON public.your_table TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE  ON public.your_table TO service_role;

-- RLS is currently DISABLED across the CRM schema (auth is enforced in
-- application code via checkFeatureAccess() + per-function ownership
-- checks — see netlify/functions/supabase-client.js). Leave RLS off on
-- new tables unless that posture changes for the whole project.
-- Enabling RLS without a corresponding policy would lock out service_role.
-- ALTER TABLE public.your_table ENABLE ROW LEVEL SECURITY;

-- CREATE POLICY ...;  -- only if RLS is enabled above
```

Symptom if a new table is created after 30 Oct 2026 without GRANTs: every supabase-js call to that table returns PostgREST error `42501`. The error body includes the exact `GRANT` statement to fix it — paste it into the SQL Editor and the next call works.

For adding a **column** to an existing table (ALTER TABLE), no GRANT is needed — the column inherits the table's existing grants.

---

## Migrations to Apply (Idempotent)

If you set up a new Supabase environment, apply these in any order via the SQL Editor — every file is `IF NOT EXISTS`-safe:

- `supabase-migration-opportunities.sql`
- `supabase-migration-opportunity-line-items.sql`
- `supabase-migration-opportunity-contact-role.sql`
- `supabase-migration-quote-versioning.sql`
- `supabase-migration-saved-reports.sql`
- `supabase-migration-report-builder-flag.sql`
- `supabase-migration-reports-2-entity-expand.sql`
- `supabase-migration-email-sync-2-1.sql`
- `supabase-migration-email-sync-2-2.sql` (token storage — encrypted refresh tokens)
- `supabase-migration-email-sync-3.sql` (Layer 2 — folder + subscriptions)
- `supabase-migration-calendar-sync-phase-3.sql` (meeting_events + meeting_review_queue)
- `supabase-migration-meeting-outcomes.sql`
- `supabase-migration-salesperson-targets.sql`
- `supabase-migration-skill-queue.sql`
- `supabase-migration-opportunity-contract-signed-date.sql` (canonical won-date + Sales TV tiles)
- `supabase-migration-opportunity-manual-contract.sql` (manual signed-contract upload — `signed_contract_*` cols + sign-method CHECK)

Pre-existing tables (`leads`, `lead_activities`, `salespeople`, `pricebook`, `feature_flags`, `user_permissions`, `quote_buddy_quotes`, `mrr_*`, plus the `lead_sources` / `lead_regions` / `lead_products` / `lead_lost_reasons` reference tables) were created in the Supabase Dashboard before the CRM fork and have no migration file in this repo.