# 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`.
    ```sql
    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.
    ```sql
    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.
    ```sql
    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.
    ```sql
    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.
    ```sql
    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.
```sql
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

> [!IMPORTANT]  
> 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](file:///c:/Projects/Software%20Projects/AI-Powered-Ticket-Creation-and-Categorization-from-User-Input/supabase/migrations/20260531_add_company_settings.sql).

### Execution Plan:
1. Open the [Supabase Dashboard](https://supabase.com).
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](file:///c:/Projects/Software%20Projects/AI-Powered-Ticket-Creation-and-Categorization-from-User-Input/supabase/migrations/20260531_add_company_settings.sql)
5. Execute the script to create the table, set up its triggers, and authorize permissions.