All docs

Supabase setup

The QCD AI Agent uses Supabase for its database (Postgres + pgvector), row-level security and (optionally) authentication for admin users.

QCD production project

The QCD production Supabase project has already been created:

  • Project name: AI Agent
  • Project ref: vajlilfofqsolrfvhsdh
  • Region: eu-west-1
  • API URL: https://vajlilfofqsolrfvhsdh.supabase.co
  • Public key: already included in .env.example

You still need to add the server-only SUPABASE_SERVICE_ROLE_KEY as an environment variable. Get it from Supabase Dashboard -> Project Settings -> API -> service_role. Never expose it in client code or Squarespace.

1. Create the project, if starting from scratch

1. Go to https://supabase.com and create a new project (London region is closest to QCD). 2. From Project settings -> API, copy: - Project URL -> NEXT_PUBLIC_SUPABASE_URL - anon public key -> NEXT_PUBLIC_SUPABASE_ANON_KEY - service_role key -> SUPABASE_SERVICE_ROLE_KEY (keep secret, server-side only)

2. Enable pgvector

In Database -> Extensions, enable the vector extension. Our schema also enables it idempotently in the migration.

3. Apply the schema

Open SQL editor and run, in order:

1. supabase/migrations/0001_init.sql 2. supabase/migrations/0002_rls.sql 3. supabase/migrations/0003_production_hardening.sql 4. supabase/migrations/0004_supabase_advisor_cleanup.sql 5. supabase/migrations/0005_seed_initial_kb_chunks.sql 6. supabase/migrations/0006_email_templates_and_lead_statuses.sql

This creates:

  • organisations, app_users, settings, email_templates
  • kb_articles, kb_chunks (with embedding vector(1536) column and the match_kb_chunks RPC)
  • conversations, messages, leads, events
  • Row-level security policies that scope everything to the user's organisation.

4. Seed the QCD content

Open SQL editor and paste the contents of supabase/seed/0001_qcd_seed.sql. Articles marked needs_review will not be served to customers until a human approves them in the admin Knowledge tab.

Alternatively run:


SUPABASE_URL=... SUPABASE_SERVICE_ROLE_KEY=... npm run seed:kb

(This requires the exec_sql RPC to be enabled. On Supabase Cloud, the SQL editor is the simplest path.)

5. Crawl the QCD website (optional)


SUPABASE_URL=... SUPABASE_SERVICE_ROLE_KEY=... npm run crawl

This fetches the main qcdauto.co.uk pages, chunks them, and stores them as needs_review. Approve them in /admin/knowledge before they are served to customers.

6. Embeddings

The retrieval layer is ready for pgvector embeddings out of the box. Add embeddings by:

1. Setting AI_EMBEDDING_MODEL and your provider key (OpenAI text-embedding-3-small is a good default). 2. Running the (separate) embed worker (not bundled - see roadmap) or computing embeddings inline when chunks are inserted.

Until embeddings are populated, retrieval falls back to a database-backed keyword scorer over published articles. The production seed also creates one initial kb_chunks row per published article so the bot has source-grounded context immediately.

7. Row-level security

All tables are scoped by organisation_id. The supplied policies assume:

  • auth.uid() matches a row in users.id, and that row has the same organisation_id as the data being read.
  • Anonymous traffic from the widget never touches Supabase directly - it goes through /api/chat and /api/leads, which use the service-role key on the server.

If you add a new table, mirror the policy pattern in 0002_rls.sql and the hardening pattern in 0003_production_hardening.sql.

Current production status

As of the latest setup:

  • 7 migrations available; 0001 through 0006 are applied to the QCD AI Agent project (ref vajlilfofqsolrfvhsdh).
  • 16 public tables with RLS enabled.
  • 27 QCD knowledge articles seeded.
  • 14 initial knowledge chunks seeded.
  • 14 editable email templates seeded.
  • Supabase security advisor reports no active lints.

Migration 0007 - garage operations

0007_garage_operations.sql adds the tables that power VRM lookup, pricing, booking requests and Motasoft handoff:

  • vehicles, vehicle_lookups, mot_history_snapshots
  • service_catalogue, pricing_rules, pricing_bands, pricing_benchmarks, admin_pricing_reviews
  • booking_requests, quote_requests
  • customer_consents, reminder_preferences
  • integration_settings, motasoft_handoff_logs

It is additive. Every table uses CREATE TABLE IF NOT EXISTS, enums are added without dropping existing types, and RLS policies follow the existing per-organisation pattern from 0002_rls.sql. It seeds a default integration_settings row and a small service_catalogue for organisation 00000000-0000-0000-0000-000000000001.

Apply it with the Supabase CLI or via the SQL editor in the dashboard. No existing data is touched.