Lago
61517879e1
- Create Kong declarative configuration for routing and authentication. - Implement initialization script to set up the database. - Add SQL migration for initializing roles, schemas, and seeding vehicle data. - Create leads and customers tables with appropriate policies and functions for CRM. - Seed admin user and configure storage bucket with RLS policies.
253 lines
9.6 KiB
PL/PgSQL
253 lines
9.6 KiB
PL/PgSQL
-- =============================================================================
|
|
-- MC Cars - CRM schema: leads + customers + realtime publication.
|
|
-- Runs from post-init AFTER auth/storage schemas exist.
|
|
-- Idempotent.
|
|
-- =============================================================================
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- LEADS: landing-page form submissions (anon may INSERT, only authenticated
|
|
-- may SELECT/UPDATE).
|
|
-- -----------------------------------------------------------------------------
|
|
create table if not exists public.leads (
|
|
id uuid primary key default gen_random_uuid(),
|
|
name text not null,
|
|
email text not null,
|
|
phone text not null default '',
|
|
vehicle_id uuid references public.vehicles(id) on delete set null,
|
|
vehicle_label text not null default '', -- denormalized (brand + model) at submit time
|
|
date_from date,
|
|
date_to date,
|
|
message text not null default '',
|
|
status text not null default 'new'
|
|
check (status in ('new','qualified','disqualified')),
|
|
is_active boolean not null default true, -- false once qualified/disqualified
|
|
admin_notes text not null default '',
|
|
source text not null default 'website',
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
qualified_at timestamptz,
|
|
qualified_by uuid references auth.users(id) on delete set null
|
|
);
|
|
|
|
create index if not exists leads_active_created_idx on public.leads (is_active, created_at desc);
|
|
create index if not exists leads_status_idx on public.leads (status);
|
|
create index if not exists leads_vehicle_idx on public.leads (vehicle_id);
|
|
|
|
drop trigger if exists leads_touch on public.leads;
|
|
create trigger leads_touch
|
|
before update on public.leads
|
|
for each row execute function public.tg_touch_updated_at();
|
|
|
|
alter table public.leads enable row level security;
|
|
|
|
drop policy if exists "leads_anon_insert" on public.leads;
|
|
drop policy if exists "leads_admin_select_all" on public.leads;
|
|
drop policy if exists "leads_admin_update" on public.leads;
|
|
drop policy if exists "leads_admin_delete" on public.leads;
|
|
|
|
-- Anonymous visitors may submit the booking form, but NOT read anything back.
|
|
create policy "leads_anon_insert"
|
|
on public.leads for insert to anon
|
|
with check (true);
|
|
|
|
create policy "leads_admin_select_all"
|
|
on public.leads for select to authenticated
|
|
using (true);
|
|
|
|
create policy "leads_admin_update"
|
|
on public.leads for update to authenticated
|
|
using (true) with check (true);
|
|
|
|
create policy "leads_admin_delete"
|
|
on public.leads for delete to authenticated
|
|
using (true);
|
|
|
|
grant insert on public.leads to anon;
|
|
grant select, insert, update, delete on public.leads to authenticated;
|
|
grant all on public.leads to service_role;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- CUSTOMERS: created ONLY by qualifying a lead from the admin panel.
|
|
-- Keeps a hard FK back to the originating lead for audit.
|
|
-- -----------------------------------------------------------------------------
|
|
create table if not exists public.customers (
|
|
id uuid primary key default gen_random_uuid(),
|
|
lead_id uuid not null references public.leads(id) on delete restrict,
|
|
name text not null,
|
|
email text not null,
|
|
phone text not null default '',
|
|
first_contacted_at timestamptz not null default now(),
|
|
notes text not null default '',
|
|
status text not null default 'active'
|
|
check (status in ('active','inactive')),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
created_by uuid references auth.users(id) on delete set null
|
|
);
|
|
|
|
create unique index if not exists customers_lead_unique on public.customers (lead_id);
|
|
create index if not exists customers_email_idx on public.customers (email);
|
|
|
|
drop trigger if exists customers_touch on public.customers;
|
|
create trigger customers_touch
|
|
before update on public.customers
|
|
for each row execute function public.tg_touch_updated_at();
|
|
|
|
alter table public.customers enable row level security;
|
|
|
|
drop policy if exists "customers_admin_select" on public.customers;
|
|
drop policy if exists "customers_admin_insert" on public.customers;
|
|
drop policy if exists "customers_admin_update" on public.customers;
|
|
drop policy if exists "customers_admin_delete" on public.customers;
|
|
|
|
create policy "customers_admin_select"
|
|
on public.customers for select to authenticated using (true);
|
|
|
|
create policy "customers_admin_insert"
|
|
on public.customers for insert to authenticated with check (true);
|
|
|
|
create policy "customers_admin_update"
|
|
on public.customers for update to authenticated using (true) with check (true);
|
|
|
|
create policy "customers_admin_delete"
|
|
on public.customers for delete to authenticated using (true);
|
|
|
|
grant select, insert, update, delete on public.customers to authenticated;
|
|
grant all on public.customers to service_role;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- qualify_lead(lead_id, notes)
|
|
-- Transactional: marks the lead qualified+inactive and creates the matching
|
|
-- customer row. Called via PostgREST RPC by the admin UI.
|
|
-- -----------------------------------------------------------------------------
|
|
create or replace function public.qualify_lead(p_lead_id uuid, p_notes text default '')
|
|
returns public.customers
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_lead public.leads;
|
|
v_customer public.customers;
|
|
v_user uuid := auth.uid();
|
|
begin
|
|
select * into v_lead from public.leads where id = p_lead_id for update;
|
|
if not found then
|
|
raise exception 'lead % not found', p_lead_id;
|
|
end if;
|
|
|
|
if v_lead.status = 'qualified' then
|
|
select * into v_customer from public.customers where lead_id = v_lead.id;
|
|
return v_customer;
|
|
end if;
|
|
|
|
update public.leads
|
|
set status = 'qualified',
|
|
is_active = false,
|
|
qualified_at = now(),
|
|
qualified_by = v_user,
|
|
admin_notes = coalesce(nullif(p_notes, ''), admin_notes)
|
|
where id = v_lead.id;
|
|
|
|
insert into public.customers (lead_id, name, email, phone, notes, created_by)
|
|
values (v_lead.id, v_lead.name, v_lead.email, v_lead.phone, coalesce(p_notes,''), v_user)
|
|
returning * into v_customer;
|
|
|
|
return v_customer;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.qualify_lead(uuid, text) from public;
|
|
grant execute on function public.qualify_lead(uuid, text) to authenticated;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- disqualify_lead(lead_id, notes)
|
|
-- -----------------------------------------------------------------------------
|
|
create or replace function public.disqualify_lead(p_lead_id uuid, p_notes text default '')
|
|
returns public.leads
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_lead public.leads;
|
|
begin
|
|
update public.leads
|
|
set status = 'disqualified',
|
|
is_active = false,
|
|
admin_notes = coalesce(nullif(p_notes, ''), admin_notes)
|
|
where id = p_lead_id
|
|
returning * into v_lead;
|
|
|
|
if not found then
|
|
raise exception 'lead % not found', p_lead_id;
|
|
end if;
|
|
|
|
return v_lead;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.disqualify_lead(uuid, text) from public;
|
|
grant execute on function public.disqualify_lead(uuid, text) to authenticated;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- reopen_lead(lead_id): moves a lead back to active/new (admin correction).
|
|
-- -----------------------------------------------------------------------------
|
|
create or replace function public.reopen_lead(p_lead_id uuid)
|
|
returns public.leads
|
|
language plpgsql
|
|
security invoker
|
|
as $$
|
|
declare
|
|
v_lead public.leads;
|
|
begin
|
|
update public.leads
|
|
set status = 'new',
|
|
is_active = true,
|
|
qualified_at = null,
|
|
qualified_by = null
|
|
where id = p_lead_id
|
|
returning * into v_lead;
|
|
|
|
-- If a customer was spawned from this lead, remove it.
|
|
delete from public.customers where lead_id = p_lead_id;
|
|
|
|
return v_lead;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.reopen_lead(uuid) from public;
|
|
grant execute on function public.reopen_lead(uuid) to authenticated;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Realtime: publish leads + customers so the admin UI sees live inserts/updates.
|
|
-- -----------------------------------------------------------------------------
|
|
do $$
|
|
declare t text;
|
|
begin
|
|
if not exists (select 1 from pg_publication where pubname = 'supabase_realtime') then
|
|
create publication supabase_realtime;
|
|
end if;
|
|
|
|
foreach t in array array['leads','customers','vehicles'] loop
|
|
if not exists (
|
|
select 1 from pg_publication_tables
|
|
where pubname='supabase_realtime' and schemaname='public' and tablename=t
|
|
) then
|
|
execute format('alter publication supabase_realtime add table public.%I', t);
|
|
end if;
|
|
end loop;
|
|
end
|
|
$$;
|
|
|
|
alter table public.leads replica identity full;
|
|
alter table public.customers replica identity full;
|
|
alter table public.vehicles replica identity full;
|
|
|
|
-- Tell PostgREST to reload its schema cache after new tables/functions appear.
|
|
notify pgrst, 'reload schema';
|
|
|