Database

The CRM uses PostgreSQL via Supabase. All tables are in the public schema. Every table carries a tenant_id column that is enforced at the application layer and by Row Level Security policies (see RLS).

---

ER Diagram (Core Entities)

erDiagram
    clients {
        uuid id PK
        uuid tenant_id FK
        uuid assigned_manager_id FK
        text name
        text email
        boolean portal_access
    }
    proposals {
        uuid id PK
        uuid tenant_id FK
        uuid client_id FK
        text title
        text status
        integer subtotal_cents
        timestamptz approved_at
    }
    contracts {
        uuid id PK
        uuid tenant_id FK
        uuid client_id FK
        text title
        text status
        text signature
        timestamptz signed_at
    }
    invoices {
        uuid id PK
        uuid tenant_id FK
        uuid client_id FK
        text status
        integer subtotal_cents
        date due_date
        timestamptz paid_at
        boolean is_recurring
    }
    projects {
        uuid id PK
        uuid tenant_id FK
        uuid client_id FK
        text name
        text status
        date start_date
        date end_date
    }
    tasks {
        uuid id PK
        uuid project_id FK
        uuid tenant_id FK
        text title
        text status
        uuid assigned_to FK
    }
    time_logs {
        uuid id PK
        uuid task_id FK
        uuid employee_id FK
        numeric hours
        text note
        date logged_date
    }
    clients ||--o{ proposals : "has"
    clients ||--o{ contracts : "has"
    clients ||--o{ invoices : "has"
    clients ||--o{ projects : "has"
    projects ||--o{ tasks : "has"
    tasks ||--o{ time_logs : "has"

---

Table Reference

tenants

Top-level isolation boundary. Every other table references tenant_id.

Column	Type	Nullable	Description
id	uuid	No	Primary key
name	text	No	Display name of the agency/company
slug	text	No	URL-safe identifier, unique across all tenants
created_at	timestamptz	No	Row creation timestamp (default now())

Indexes: PK on id, unique on slug.

---

users

All authenticated users across all roles. Linked to auth.users via the same UUID.

Column	Type	Nullable	Description
id	uuid	No	PK — matches auth.users.id
tenant_id	uuid	No	FK → tenants.id
email	text	No	Must match auth.users.email
full_name	text	No	Display name
role	text	No	One of: super_admin, admin, employee, client
avatar_url	text	Yes	Supabase Storage public URL for avatar image
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, index on tenant_id, index on email.

Notes: The role column drives authorization (see Authorization). super_admin users span tenants; all other roles are scoped to a single tenant_id.

---

leads

Inbound prospects not yet converted to clients.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
name	text	No	Lead name or company name
email	text	Yes	Contact email
phone	text	Yes	Contact phone
source	text	Yes	Acquisition channel (website, referral, social, etc.)
status	text	No	new, contacted, qualified, converted, lost (default new)
notes	text	Yes	Free-text notes
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, status).

---

clients

Converted customers with active relationships.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
name	text	No	Client/company display name
email	text	No	Primary billing/contact email
company	text	Yes	Legal company name if different from name
phone	text	Yes	Primary phone number
address	text	Yes	Mailing/billing address
assigned_manager_id	uuid	Yes	FK → users.id (the account manager responsible)
portal_access	boolean	No	Whether client can log in to the portal (default false)
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, index on tenant_id, index on assigned_manager_id.

FK relationships: - assigned_manager_id → users.id (nullable — clients may be unassigned)

---

contacts

Individual people associated with a client (multiple contacts per client).

Column	Type	Nullable	Description
id	uuid	No	Primary key
client_id	uuid	No	FK → clients.id
name	text	No	Contact full name
email	text	Yes	Contact email
phone	text	Yes	Contact phone
title	text	Yes	Job title (e.g., "CFO", "Marketing Manager")
is_primary	boolean	No	Whether this is the primary contact (default false)
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, index on client_id, partial index on (client_id, is_primary) WHERE is_primary = true.

Notes: Application logic ensures at most one is_primary = true per client_id. When a new contact is added as primary, previous primary contact is updated.

---

proposals

Scoped sales proposals sent to clients.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
title	text	No	Proposal title
status	text	No	draft, sent, approved, declined (default draft)
subtotal_cents	integer	No	Computed total from line items (denormalized for queries)
valid_until	date	Yes	Expiry date shown to client
sent_at	timestamptz	Yes	When the proposal was emailed to the client
approved_at	timestamptz	Yes	When the client approved
declined_at	timestamptz	Yes	When the client declined
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, status), index on client_id.

---

proposal_line_items

Individual line items belonging to a proposal.

Column	Type	Nullable	Description
id	uuid	No	Primary key
proposal_id	uuid	No	FK → proposals.id (cascade delete)
description	text	No	Line item description
quantity	numeric	No	Quantity (supports fractional hours)
unit_price_cents	integer	No	Price per unit in cents
created_at	timestamptz	No	Row creation timestamp

Notes: subtotal_cents on the parent proposals row must be recomputed whenever line items change (done in application layer via trigger or explicit update).

---

contracts

Legally binding agreements sent to clients for e-signature.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
title	text	No	Contract title
content	text	No	Full contract body in HTML
status	text	No	draft, sent, signed, declined (default draft)
sent_at	timestamptz	Yes	When contract was emailed to client
signed_at	timestamptz	Yes	When client e-signed
declined_at	timestamptz	Yes	When client declined
signature	text	Yes	Base64-encoded signature image (PNG) or typed name
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, status).

---

invoices

Billing invoices, supporting one-off and recurring schedules.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
status	text	No	draft, sent, paid, overdue, payment_failed (default draft)
subtotal_cents	integer	No	Computed total from line items
due_date	date	Yes	Payment due date
paid_at	timestamptz	Yes	When payment was confirmed (manual or Stripe)
stripe_payment_intent_id	text	Yes	Stripe PaymentIntent ID for online payments
is_recurring	boolean	No	Whether invoice auto-recurs (default false)
recurrence_interval	text	Yes	monthly, quarterly, annually (only set when is_recurring = true)
next_invoice_date	date	Yes	Date to generate the next recurring invoice
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, status), index on (is_recurring, next_invoice_date) for cron query.

---

invoice_line_items

Individual line items belonging to an invoice.

Column	Type	Nullable	Description
id	uuid	No	Primary key
invoice_id	uuid	No	FK → invoices.id (cascade delete)
description	text	No	Line item description
quantity	numeric	No	Quantity
unit_price_cents	integer	No	Price per unit in cents

---

projects

Active delivery projects linked to clients.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
name	text	No	Project name
status	text	No	active, on_hold, completed, cancelled (default active)
start_date	date	Yes	Planned start date
end_date	date	Yes	Planned end date
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, status).

---

tasks

Work items within a project, assignable to team members.

Column	Type	Nullable	Description
id	uuid	No	Primary key
project_id	uuid	No	FK → projects.id
tenant_id	uuid	No	FK → tenants.id (denormalized for RLS)
title	text	No	Task title
status	text	No	todo, in_progress, review, done (default todo)
assigned_to	uuid	Yes	FK → users.id (nullable — unassigned tasks)
due_date	date	Yes	Task due date
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, index on project_id, index on assigned_to.

---

time_logs

Hours logged by employees against specific tasks.

Column	Type	Nullable	Description
id	uuid	No	Primary key
task_id	uuid	No	FK → tasks.id
employee_id	uuid	No	FK → users.id (the employee who logged time)
hours	numeric	No	Hours worked (supports fractions, e.g. 1.5)
note	text	Yes	Description of work performed
logged_date	date	No	Calendar date the work was performed
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, index on task_id, index on employee_id, index on logged_date.

---

support_tickets

Client support requests managed through the portal and admin views.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
subject	text	No	Ticket subject line
status	text	No	open, in_progress, resolved, closed (default open)
created_by	uuid	No	FK → users.id
assigned_to	uuid	Yes	FK → users.id (admin handling the ticket)
created_at	timestamptz	No	Row creation timestamp

---

ticket_replies

Threaded replies on support tickets. Supports internal (admin-only) notes.

Column	Type	Nullable	Description
id	uuid	No	Primary key
ticket_id	uuid	No	FK → support_tickets.id
author_id	uuid	No	FK → users.id
body	text	No	Reply body (HTML or plain text)
is_internal	boolean	No	If true, hidden from client portal (default false)
created_at	timestamptz	No	Row creation timestamp

---

documents

Files uploaded to Supabase Storage and shared with clients.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
name	text	No	Human-readable file name
file_url	text	No	Supabase Storage path (not a signed URL — generate signed URLs at request time)
mime_type	text	No	MIME type (e.g., application/pdf)
size_bytes	integer	No	File size in bytes
uploaded_by	uuid	No	FK → users.id
created_at	timestamptz	No	Row creation timestamp

---

notifications

In-app notifications for admin and broadcast alerts.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
user_id	uuid	Yes	FK → users.id. If NULL, the notification is broadcast to all admins in the tenant.
type	text	No	Event type: new_lead, invoice_paid, contract_signed, contract_declined, proposal_approved, proposal_declined, support_reply
title	text	No	Short notification title
body	text	No	Notification body text
resource_type	text	Yes	Related entity type (lead, invoice, contract, etc.)
resource_id	uuid	Yes	UUID of the related entity
resource_label	text	Yes	Human-readable label for the resource
link	text	Yes	Deep link to the resource in the admin UI
read_at	timestamptz	Yes	When the user read the notification. NULL = unread.
created_at	timestamptz	No	Row creation timestamp

Indexes: PK on id, composite index on (tenant_id, user_id, read_at) for the notification bell query.

---

wiki_articles

Internal knowledge base articles managed by admins.

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
slug	text	No	URL slug, unique per tenant
title	text	No	Article title
category	text	No	Category label for grouping
icon	text	Yes	Emoji or icon name for the category
summary	text	Yes	One-sentence summary shown in article listings
content_html	text	No	Full article body in HTML
is_published	boolean	No	Whether the article is visible to users (default false)
sort_order	integer	No	Manual ordering within a category (default 0)
created_at	timestamptz	No	Row creation timestamp

---

deployments

Deployment records for projects, tracking environment and version.

Column	Type	Nullable	Description
id	uuid	No	Primary key
project_id	uuid	No	FK → projects.id
environment	text	No	prod, staging, or dev
url	text	Yes	Deployment URL
version	text	Yes	Version tag or git SHA
deployed_at	timestamptz	No	When the deployment occurred
notes	text	Yes	Release notes or change summary
created_at	timestamptz	No	Row creation timestamp

---

client_costs

Costs incurred on behalf of a client (vendor bills, subscriptions, etc.).

Column	Type	Nullable	Description
id	uuid	No	Primary key
tenant_id	uuid	No	FK → tenants.id
client_id	uuid	No	FK → clients.id
category	text	No	Cost category (hosting, software, advertising, etc.)
vendor	text	Yes	Vendor or supplier name
description	text	No	Description of the cost
amount_cents	integer	No	Cost amount in cents
currency	text	No	ISO 4217 currency code (default USD)
cost_date	date	No	Date the cost was incurred
notes	text	Yes	Additional notes
created_at	timestamptz	No	Row creation timestamp

---

Common Query Patterns

-- Unread notifications for a user (includes broadcasts)
SELECT * FROM notifications
WHERE tenant_id = $1
  AND (user_id = $2 OR user_id IS NULL)
  AND read_at IS NULL
ORDER BY created_at DESC;

-- Total hours logged per project for a date range
SELECT p.name, SUM(tl.hours) as total_hours
FROM time_logs tl
JOIN tasks t ON t.id = tl.task_id
JOIN projects p ON p.id = t.project_id
WHERE p.tenant_id = $1
  AND tl.logged_date BETWEEN $2 AND $3
GROUP BY p.id, p.name;

-- Overdue invoices for cron
SELECT * FROM invoices
WHERE status = 'sent'
  AND due_date < CURRENT_DATE;

---

Supabase

This page documents how Supabase is used in the SONAN DIGITAL CRM: client instantiation, Auth, Storage, Migrations, typed schema, and the common gotchas that have caused production bugs.

---

1. Client Types

There are two Supabase client factories. Using the wrong one is a frequent source of authorization bugs.

createClient() — User-scoped, SSR-safe

Use this in server components and API routes that operate on behalf of the signed-in user. It reads the session from the request's httpOnly cookies via @supabase/ssr, so it automatically applies the user's identity and Supabase RLS policies.

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'
import type { Database } from '@/types/supabase'

export async function createClient() {
  const cookieStore = await cookies()

  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() { return cookieStore.getAll() },
        setAll(cookiesToSet) {
          cookiesToSet.forEach(({ name, value, options }) =>
            cookieStore.set(name, value, options)
          )
        },
      },
    }
  )
}

Use when: fetching data in server components, in portal API routes, in any context where you want RLS to enforce tenant/user isolation.

createServiceClient() — Service role, bypasses RLS

Use this only in admin API routes and server-side operations that must bypass RLS (e.g., cross-tenant super admin actions, cron jobs). This client uses SUPABASE_SERVICE_ROLE_KEY which has full database access with no RLS restrictions.

// lib/supabase/service.ts
import { createClient as createSupabaseClient } from '@supabase/supabase-js'
import type { Database } from '@/types/supabase'

export function createServiceClient() {
  return createSupabaseClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
}

Use when: requireAdminWithTenant() admin routes, cron jobs, Stripe webhook handler, any operation that needs to write across tenant boundaries.

---

2. Auth

Supabase Auth provides email+password authentication with TOTP MFA. Sessions are stored in httpOnly cookies — not localStorage.

Checking the current user in a server component

// app/admin/dashboard/page.tsx
import { createClient } from '@/lib/supabase/server'
import { redirect } from 'next/navigation'

export default async function DashboardPage() {
  const supabase = await createClient()
  const { data: { user } } = await supabase.auth.getUser()

  if (!user) redirect('/auth/login')

  // Fetch data as this user — RLS applies automatically
  const { data: notifications } = await supabase
    .from('notifications')
    .select('*')
    .order('created_at', { ascending: false })
    .limit(10)

  return <DashboardClient notifications={notifications ?? []} />
}

Checking MFA status

MFA verification is checked in the auth middleware and in requireAdminWithTenant(). After password login, users are redirected to /auth/mfa if they have a TOTP factor enrolled.

const { data: factors } = await supabase.auth.mfa.listFactors()
const hasMfa = factors?.totp?.length > 0

if (hasMfa) {
  // Redirect to MFA challenge
  redirect('/auth/mfa')
}

---

3. Storage

File uploads use Supabase Storage with signed upload URLs. Files are never routed through the Next.js edge function.

Bucket names

Bucket	Access	Purpose
documents	Private	Client-facing documents (contracts, proposals, PDFs)
avatars	Public	User avatar images

Upload pattern (two-step)

Step 1: Generate a signed upload URL from an API route (server-side, requires auth):

// POST /api/admin/documents/upload-url
const supabase = createServiceClient()
const filePath = `${tenantId}/${clientId}/${Date.now()}_${fileName}`

const { data, error } = await supabase.storage
  .from('documents')
  .createSignedUploadUrl(filePath)

return NextResponse.json({ uploadUrl: data?.signedUrl, filePath })

Step 2: Upload directly from the browser using the signed URL (no auth headers needed):

// Client component
const { uploadUrl, filePath } = await fetch('/api/admin/documents/upload-url', {
  method: 'POST',
  body: JSON.stringify({ fileName, mimeType, clientId }),
}).then(r => r.json())

await fetch(uploadUrl, {
  method: 'PUT',
  body: file,
  headers: { 'Content-Type': mimeType },
})

// Then POST metadata to save the document record
await fetch('/api/admin/documents', {
  method: 'POST',
  body: JSON.stringify({ name: fileName, filePath, mimeType, sizeBytes: file.size, clientId }),
})

Generating signed read URLs

Files in private buckets are never publicly accessible. Always generate a signed URL at request time:

const { data } = await supabase.storage
  .from('documents')
  .createSignedUrl(document.file_url, 3600) // 1 hour expiry

return NextResponse.json({ url: data?.signedUrl })

File size validation

Validate file size before requesting an upload URL to avoid wasting the slot:

const MAX_SIZE_BYTES = 20 * 1024 * 1024 // 20 MB

if (file.size > MAX_SIZE_BYTES) {
  return NextResponse.json({ error: 'File exceeds 20 MB limit' }, { status: 400 })
}

Also validate MIME type server-side — do not trust the browser's file.type:

const ALLOWED_MIME_TYPES = [
  'application/pdf',
  'image/png',
  'image/jpeg',
  'application/msword',
  'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
]

if (!ALLOWED_MIME_TYPES.includes(mimeType)) {
  return NextResponse.json({ error: 'File type not allowed' }, { status: 400 })
}

---

4. RLS Integration

createClient() automatically applies RLS because the user's JWT is sent with every request. The RLS policies on each table enforce tenant_id isolation.

createServiceClient() bypasses RLS entirely — the service role key grants full table access. When using the service client, always scope queries manually by tenant_id:

// Good — manual tenant scoping when using service client
const { data } = await supabase
  .from('clients')
  .select('*')
  .eq('tenant_id', caller.tenantId)  // always filter by tenant

// Bad — service client with no tenant filter leaks all tenants' data
const { data } = await supabase.from('clients').select('*')

See RLS for the full policy inventory.

---

5. Migrations

Migrations live in supabase/migrations/. Each migration is a .sql file prefixed with a timestamp.

Local development

# Apply pending migrations to local Supabase
supabase db push

# Create a new migration file
supabase migration new add_client_costs_table

# Reset local DB and apply all migrations from scratch
supabase db reset

Production migrations

Migrations are applied to the production Supabase project via the Supabase CLI with the production project credentials. They are not automatically applied by the Vercel deployment.

# Apply to production (run from local machine with prod credentials)
supabase db push --db-url "{{ SUPABASE_PROD_DB_URL }}"

---

6. Typed Client

The Supabase client is typed using generated types from the database schema.

Generating types

supabase gen types typescript --project-id {{ SUPABASE_PROJECT_ID }} > types/supabase.ts

Run this after every migration that changes the schema. The generated types/supabase.ts is committed to the repository.

Using typed queries

import type { Database } from '@/types/supabase'

type Client = Database['public']['Tables']['clients']['Row']
type NewClient = Database['public']['Tables']['clients']['Insert']

// The supabase client is typed — .from() returns typed results
const { data }: { data: Client[] | null } = await supabase
  .from('clients')
  .select('*')
  .eq('tenant_id', tenantId)

---

7. Gotcha: Nested FK Joins Return Arrays

This is the single most common TypeScript error in the codebase.

When using Supabase's foreign key join syntax (the !hint notation or nested select), TypeScript infers nested relations as arrays, even for many-to-one relationships where you expect a single object.

// Query with nested FK joins
const { data } = await supabase
  .from('time_logs')
  .select(`
    id, hours, logged_date,
    tasks!inner(id, title,
      projects!inner(id, name,
        clients!inner(id, name)
      )
    ),
    users!inner(id, full_name)
  `)

// Supabase infers the type as:
// {
//   id: string
//   hours: number
//   tasks: Array<{
//     id: string
//     title: string
//     projects: Array<{
//       id: string
//       name: string
//       clients: Array<{ id: string; name: string }>
//     }>
//   }>
//   users: Array<{ id: string; full_name: string }>
// }

// CORRECT — always use [0] indexing
const taskTitle = data?.[0]?.tasks?.[0]?.title
const projectName = data?.[0]?.tasks?.[0]?.projects?.[0]?.name
const clientName = data?.[0]?.tasks?.[0]?.projects?.[0]?.clients?.[0]?.name
const employeeName = data?.[0]?.users?.[0]?.full_name

// WRONG — TypeScript error: Property 'title' does not exist on type 'Array<...>'
const taskTitle = data?.[0]?.tasks?.title

Define interfaces to match the array shape:

interface TimeLogWithRelations {
  id: string
  hours: number
  logged_date: string
  tasks: Array<{
    id: string
    title: string
    projects: Array<{
      id: string
      name: string
      clients: Array<{ id: string; name: string }>
    }>
  }>
  users: Array<{ id: string; full_name: string }>
}

---

8. Gotcha: Service Client Must Never Run Client-Side

The createServiceClient() function imports SUPABASE_SERVICE_ROLE_KEY from process.env. In Next.js, any environment variable without the NEXT_PUBLIC_ prefix is only available server-side.

If you accidentally import createServiceClient in a Client Component (marked 'use client'), Next.js will silently pass undefined for the service role key, causing all requests to fail with 401 errors — not a build error.

Prevention: Add a runtime guard:

// lib/supabase/service.ts
export function createServiceClient() {
  if (typeof window !== 'undefined') {
    throw new Error('createServiceClient must not be called from the browser')
  }
  // ...
}

---

Row Level Security (RLS)

Row Level Security is enabled on every table in the public schema. RLS is the last line of defense for data isolation — it prevents cross-tenant and cross-user data leaks even if the application layer has a bug.

---

1. Overview

Why RLS?

The CRM is a multi-tenant SaaS. Without RLS, a bug in a single API route (e.g., a missing .eq('tenant_id', tenantId) filter) could expose one tenant's data to another. With RLS, such a bug at the application layer returns an empty result set instead of leaking data.

Defense-in-depth

RLS works alongside, not instead of, application-layer filtering:

Layer	Mechanism	What it prevents
API route	requireAdminWithTenant() guard	Unauthenticated access to admin endpoints
Query filter	.eq('tenant_id', caller.tenantId)	Application-level cross-tenant query
RLS policy	tenant_id = get_active_tenant_id()	Database-level cross-tenant leak if app filter is missing
Supabase Storage	Bucket policies + signed URLs	Direct file access without auth

get_active_tenant_id() function

Most RLS policies call a helper function that extracts the tenant ID from the user's JWT claims:

CREATE OR REPLACE FUNCTION get_active_tenant_id()
RETURNS uuid
LANGUAGE sql STABLE
AS $$
  SELECT (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid
$$;

This is set in app_metadata during the auth flow and cannot be modified by the user's JWT.

---

2. Policy Pattern

Every table follows this baseline pattern:

-- Enable RLS
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;

-- Admins (and service role) can do everything within their tenant
CREATE POLICY "tenant_isolation" ON clients
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

Tables with additional role-based restrictions have supplementary policies layered on top.

---

3. Table-by-Table Policies

clients

-- All operations require tenant_id match
CREATE POLICY "clients_tenant_isolation" ON clients
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

No portal-user access — clients cannot query their own client record via the portal (they access proposals, invoices etc. which are scoped by client_id).

---

contacts

-- Access via parent client's tenant
CREATE POLICY "contacts_via_client" ON contacts
  FOR ALL
  USING (
    client_id IN (
      SELECT id FROM clients WHERE tenant_id = get_active_tenant_id()
    )
  );

---

proposals

-- Admin access: tenant_id match
CREATE POLICY "proposals_admin" ON proposals
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

-- Portal access: client can only read their own proposals
CREATE POLICY "proposals_portal_read" ON proposals
  FOR SELECT
  USING (
    client_id IN (
      SELECT id FROM clients
      WHERE id = get_portal_client_id()
        AND tenant_id = get_active_tenant_id()
    )
  );

---

invoices

-- Admin access: tenant_id match
CREATE POLICY "invoices_admin" ON invoices
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

-- Portal access: client can only read their own invoices
CREATE POLICY "invoices_portal_read" ON invoices
  FOR SELECT
  USING (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
  );

---

contracts

CREATE POLICY "contracts_admin" ON contracts
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

CREATE POLICY "contracts_portal_read" ON contracts
  FOR SELECT
  USING (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
  );

-- Portal users can update their own contract (sign/decline only)
CREATE POLICY "contracts_portal_update" ON contracts
  FOR UPDATE
  USING (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
    AND status = 'sent'
  )
  WITH CHECK (
    status IN ('signed', 'declined')
  );

---

support_tickets

CREATE POLICY "tickets_admin" ON support_tickets
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

-- Portal: client can read and insert their own tickets
CREATE POLICY "tickets_portal_read" ON support_tickets
  FOR SELECT
  USING (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
  );

CREATE POLICY "tickets_portal_insert" ON support_tickets
  FOR INSERT
  WITH CHECK (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
  );

---

ticket_replies

CREATE POLICY "replies_admin" ON ticket_replies
  FOR ALL
  USING (
    ticket_id IN (
      SELECT id FROM support_tickets WHERE tenant_id = get_active_tenant_id()
    )
  );

-- Portal: client can read non-internal replies on their tickets
CREATE POLICY "replies_portal_read" ON ticket_replies
  FOR SELECT
  USING (
    is_internal = false
    AND ticket_id IN (
      SELECT id FROM support_tickets
      WHERE client_id = get_portal_client_id()
        AND tenant_id = get_active_tenant_id()
    )
  );

-- Portal: client can insert replies on their own tickets
CREATE POLICY "replies_portal_insert" ON ticket_replies
  FOR INSERT
  WITH CHECK (
    is_internal = false
    AND ticket_id IN (
      SELECT id FROM support_tickets
      WHERE client_id = get_portal_client_id()
        AND tenant_id = get_active_tenant_id()
    )
  );

---

documents

CREATE POLICY "documents_admin" ON documents
  FOR ALL
  USING (tenant_id = get_active_tenant_id());

-- Portal: client can only read documents explicitly shared with them
CREATE POLICY "documents_portal_read" ON documents
  FOR SELECT
  USING (
    client_id = get_portal_client_id()
    AND tenant_id = get_active_tenant_id()
  );

Storage bucket policy (Supabase Storage RLS):

-- Private documents bucket: require signed URL path to match tenant/client
CREATE POLICY "documents_storage_read" ON storage.objects
  FOR SELECT
  USING (
    bucket_id = 'documents'
    AND (storage.foldername(name))[1] = get_active_tenant_id()::text
    AND (storage.foldername(name))[2] = get_portal_client_id()::text
  );

---

time_logs

-- Admin: full access within tenant
CREATE POLICY "timelogs_admin" ON time_logs
  FOR ALL
  USING (
    task_id IN (
      SELECT id FROM tasks WHERE tenant_id = get_active_tenant_id()
    )
  );

-- Employee: can only read and insert their own logs
CREATE POLICY "timelogs_employee_read" ON time_logs
  FOR SELECT
  USING (
    employee_id = auth.uid()
    AND task_id IN (
      SELECT id FROM tasks WHERE tenant_id = get_active_tenant_id()
    )
  );

CREATE POLICY "timelogs_employee_insert" ON time_logs
  FOR INSERT
  WITH CHECK (
    employee_id = auth.uid()
  );

---

notifications

-- Each user sees their own notifications plus broadcasts (user_id IS NULL)
CREATE POLICY "notifications_user_read" ON notifications
  FOR SELECT
  USING (
    tenant_id = get_active_tenant_id()
    AND (user_id = auth.uid() OR user_id IS NULL)
  );

CREATE POLICY "notifications_user_update" ON notifications
  FOR UPDATE
  USING (
    tenant_id = get_active_tenant_id()
    AND (user_id = auth.uid() OR user_id IS NULL)
  )
  WITH CHECK (
    -- Only allow marking as read — no other field changes via RLS
    read_at IS NOT NULL
  );

-- Only service role (admin API) can insert notifications
CREATE POLICY "notifications_service_insert" ON notifications
  FOR INSERT
  WITH CHECK (false);  -- blocked for authenticated users; service role bypasses RLS

---

4. Known RLS Incidents

CRIT-4 — Cross-tenant notification leak (Fixed)

Severity: Critical
Discovered: Internal audit, v0.8
Status: Fixed in v0.9

Description: The initial notifications RLS policy did not include a tenant_id check — it only filtered by user_id = auth.uid() OR user_id IS NULL. This meant that broadcast notifications (user_id IS NULL) from one tenant were visible to all users across all tenants.

Impact: Admin users on any tenant could see broadcast notifications (e.g., "New lead: Acme Corp") from other tenants.

Fix:

-- Before (vulnerable):
CREATE POLICY "notifications_read" ON notifications
  FOR SELECT
  USING (user_id = auth.uid() OR user_id IS NULL);

-- After (fixed):
CREATE POLICY "notifications_read" ON notifications
  FOR SELECT
  USING (
    tenant_id = get_active_tenant_id()
    AND (user_id = auth.uid() OR user_id IS NULL)
  );

---

CRIT-7 — Client document access via direct Storage URL (Fixed)

Severity: Critical
Discovered: Penetration test, v0.9
Status: Fixed in v1.0

Description: Documents were stored in a Storage bucket with public read access. Clients who obtained the file_url (visible in API responses) could construct a direct URL and download documents belonging to other clients, bypassing RLS on the documents table.

Fix: 1. Changed the documents bucket from public to private. 2. Removed file_url from portal API responses; replaced with a server-generated signed URL with 1-hour expiry. 3. Added Storage RLS policy scoping access to tenant_id/client_id path prefix. 4. Added client_id check to the Storage bucket policy.

---

H8 — Employee could read all time logs in tenant (Fixed)

Severity: High
Discovered: Internal code review, v0.9
Status: Fixed in v1.0

Description: The time_logs RLS policy for employees only checked tenant_id — it did not restrict employees to their own logs. An employee could call GET /api/admin/time-logs without a filter and retrieve all time logs in the tenant, including those of colleagues.

Fix: Added employee_id = auth.uid() to the employee SELECT policy (see policy above). Admin role retains full access.

---

5. Testing RLS Policies

Use the Supabase Dashboard SQL editor to test policies without bypassing them.

Test as a specific user

-- Impersonate a user session for policy testing
SET request.jwt.claims = '{"sub": "USER_UUID", "app_metadata": {"tenant_id": "TENANT_UUID", "role": "employee"}}';

-- Now run a query — it will be subject to RLS as this user
SELECT * FROM time_logs;
-- Should only return this user's logs

Verify a policy is blocking correctly

-- Test that cross-tenant access is blocked
SET request.jwt.claims = '{"sub": "USER_A_UUID", "app_metadata": {"tenant_id": "TENANT_A_UUID"}}';

SELECT COUNT(*) FROM clients WHERE tenant_id = 'TENANT_B_UUID';
-- Must return 0

List all policies on a table

SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'notifications';

Check RLS is enabled

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
-- rowsecurity must be TRUE for all tables