docs/handoff/02_database_schema.md

Helpdesk.ai — Project Handoff Sheet: 02. Database Schema & Supabase Configuration

This sheet provides a complete overview of the Supabase PostgreSQL database schema, including the logical tables, structural constraints, RLS security policies, trigger functions, and critical migration details.

---

🗄️ 1. Core Tables & Field Definitions

🏢 companies

Represents the tenants on the multi-tenant platform.

• id (UUID, Primary Key, Default: gen_random_uuid())
• name (TEXT, Not Null) — Name of the company.
• domain (TEXT, Unique) — E.g., riteshpvtltd.com (used for automated user routing).
• status (TEXT) — active, suspended, pending_verification.
• created_at (TIMESTAMPTZ, Default: now())

👤 profiles

Integrates with auth.users to implement Role-Based Access Control (RBAC).

• id (UUID, Primary Key, References auth.users.id ON DELETE CASCADE)
• full_name (TEXT) — Display name of the user.
• company_id (UUID, References companies.id ON DELETE SET NULL)
• company (TEXT) — Company name string fallback.
• role (TEXT, Default: 'user') — ENUM check: 'user', 'admin', 'master_admin'.
• status (TEXT, Default: 'active') — 'active', 'pending_approval', 'rejected'.
• updated_at (TIMESTAMPTZ)
• created_at (TIMESTAMPTZ, Default: now())

🎫 tickets

The central ticket store with embedded machine learning extraction columns and RAG vector indexes.

• id (UUID, Primary Key, Default: gen_random_uuid())
• title (TEXT, Not Null)
• description (TEXT, Not Null)
• status (TEXT, Default: 'open') — 'open', 'in_progress', 'resolved', 'closed'.
• priority (TEXT, Default: 'medium') — 'low', 'medium', 'high', 'urgent'.
• category (TEXT) — E.g., 'IT Support', 'HR', 'Billing'.
• sub_category (TEXT) — E.g., 'Network Issue', 'Payroll', 'Refund'.
• created_by (UUID, References profiles.id)
• company_id (UUID, References companies.id ON DELETE CASCADE)
• assigned_to (UUID, References profiles.id)
• ai_confidence (FLOAT) — Confidence rating of the automatic classification.
• ai_metadata (JSONB) — Raw extraction results (NER fields, duplicate scoring log).
• sla_deadline (TIMESTAMPTZ) — Calculated timestamp based on ticket priority.
• sla_status (TEXT, Default: 'active') — 'active', 'warning', 'breached', 'resolved'.
• sla_breached (BOOLEAN, Default: false)
• parent_id (UUID, References tickets.id ON DELETE SET NULL) — Parent ticket pointer for duplicates.
• vector_embedding (vector(384)) — pgvector cosine representation of ticket description (used for duplicate search).
• created_at (TIMESTAMPTZ, Default: now())

💬 ticket_messages

Stores the dialog threads for active tickets.

• id (UUID, Primary Key, Default: gen_random_uuid())
• ticket_id (UUID, References tickets.id ON DELETE CASCADE)
• sender_id (UUID, References profiles.id ON DELETE CASCADE)
• message (TEXT, Not Null)
• is_system (BOOLEAN, Default: false) — Identifies system prompts or AI audit responses.
• attachments (TEXT[]) — File storage links.
• created_at (TIMESTAMPTZ, Default: now())

🛠️ system_settings

Configures tenant-specific SLA tolerances and AI trigger boundaries.

• company_id (UUID, Primary Key, References companies.id ON DELETE CASCADE)
• ai_confidence_threshold (FLOAT, Default: 0.80) — Minimum score to accept automated actions.
• duplicate_sensitivity (FLOAT, Default: 0.85) — Cosine similarity cut-off.
• enable_auto_resolve (BOOLEAN, Default: false)
• auto_close_enabled (BOOLEAN, Default: true)
• auto_close_days (INTEGER, Default: 7)
• email_notifications (BOOLEAN, Default: true)
• admin_alerts (BOOLEAN, Default: true)
• digest_frequency (TEXT, Default: 'daily') — 'daily', 'weekly'.
• updated_at (TIMESTAMPTZ, Default: now())

---

🔒 2. Row Level Security (RLS) Design

Supabase RLS secures the multi-tenant data layer. Below are the key security definitions:

Profiles Table

• Read access: Members can view other profiles sharing their company_id.

  CREATE POLICY "Members see company profiles" ON profiles
  FOR SELECT USING (company_id = (SELECT company_id FROM profiles WHERE id = auth.uid()));
  

• Write access: Users can update their own profile metadata.

  CREATE POLICY "Users update own profile" ON profiles
  FOR UPDATE USING (id = auth.uid());
  

Tickets Table

• Standard Users: Can read and write only their own records.

  CREATE POLICY "Users read own tickets" ON tickets
  FOR SELECT USING (created_by = auth.uid());
  
  CREATE POLICY "Users create own tickets" ON tickets
  FOR INSERT WITH CHECK (created_by = auth.uid());
  

• Admins: Can read, update, and manage all tickets belonging to their tenant.

  CREATE POLICY "Admins manage company tickets" ON tickets
  FOR ALL USING (
      company_id = (SELECT company_id FROM profiles WHERE id = auth.uid() AND role IN ('admin', 'master_admin'))
  );
  

System Settings Table

• Restricts select and modifications strictly to organization administrators.

  CREATE POLICY "Company members can manage own settings" ON system_settings
  FOR ALL
  USING (company_id IN (SELECT company_id FROM profiles WHERE id = auth.uid()))
  WITH CHECK (company_id IN (SELECT company_id FROM profiles WHERE id = auth.uid()));
  

---

⚡ 3. SQL Trigger Actions

Profile Generation Trigger

An automatic database trigger maps new signups from Supabase Auth (auth.users) to the public profiles table to maintain RBAC coherence.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, company, role, status)
  VALUES (
    new.id,
    COALESCE(new.raw_user_meta_data->>'full_name', 'Employee'),
    COALESCE(new.raw_user_meta_data->>'company', 'Default Org'),
    COALESCE(new.raw_user_meta_data->>'role', 'user'),
    COALESCE(new.raw_user_meta_data->>'status', 'active')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

---

⚠️ 4. Pending Database Actions

The new system_settings table and configuration RLS structure must be run manually on the live database. The migration script is fully completed and stored locally in 20260531_add_company_settings.sql.

Execution Plan:

1. Open the Supabase Dashboard.
2. Navigate to your project (replace YOUR_PROJECT_REF with your Supabase project reference).
3. Open the SQL Editor tab.
4. Copy the complete SQL script located in the local migration file: supabase/migrations/20260531_add_company_settings.sql
5. Execute the script to create the table, set up its triggers, and authorize permissions.