bootstrap.sql

-- Job Application Tracker — initial schema.
--
-- Run ONCE in the Supabase SQL editor (Dashboard -> SQL Editor -> New query).
-- Hand-written to mirror src/db/schema.ts, because drizzle-kit needs Node and
-- Node is not installed yet. Once it is, `npm run db:push` diffs this against
-- the schema file; a clean no-op result confirms the two agree.
--
-- Safe to re-run: every statement is guarded.

-- ---------------------------------------------------------------- companies
create table if not exists public.companies (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null references auth.users (id) on delete cascade,
  name        text not null,
  location    text,
  lat         double precision,
  lng         double precision,
  website     text,
  notes       text,
  created_at  timestamptz not null default now(),
  constraint companies_user_name_unique unique (user_id, name)
);
create index if not exists companies_user_idx on public.companies (user_id);

-- ----------------------------------------------------------------- contacts
create table if not exists public.contacts (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null references auth.users (id) on delete cascade,
  name        text not null,
  kind        text default 'recruiter',
  agency      text,
  company_id  uuid references public.companies (id) on delete set null,
  email       text,
  phone       text,
  notes       text,
  created_at  timestamptz not null default now()
);
create index if not exists contacts_user_idx on public.contacts (user_id);

-- ------------------------------------------------------------- applications
create table if not exists public.applications (
  id                uuid primary key default gen_random_uuid(),
  user_id           uuid not null references auth.users (id) on delete cascade,
  company_id        uuid not null references public.companies (id) on delete restrict,
  role              text not null,
  description       text,
  job_url           text,
  application_type  text,
  recruiter_id      uuid references public.contacts (id) on delete set null,
  platform_found    text,
  platform_applied  text,
  -- The office for this posting. Non-remote roles are required to carry one;
  -- that rule lives in the application layer so it can explain itself, and so
  -- rows predating it are not rejected. See supabase/004_application_location.sql.
  location          text,
  location_place_id text,
  location_lat      double precision,
  location_lng      double precision,
  remote_type       text,
  salary_min        integer,
  salary_max        integer,
  salary_currency   text default 'CAD',
  cover_letter      text,
  status            text not null default 'wishlist',
  outcome           text,
  rejection_reason  text,
  applied_at        timestamptz,
  first_response_at timestamptz,
  closed_at         timestamptz,
  created_at        timestamptz not null default now(),
  updated_at        timestamptz not null default now()
);
create index if not exists applications_user_idx on public.applications (user_id);
create index if not exists applications_user_status_idx on public.applications (user_id, status);
create index if not exists applications_user_applied_idx on public.applications (user_id, applied_at);
create index if not exists applications_company_idx on public.applications (company_id);
-- Partial: the dashboard map reads only the rows that have coordinates.
create index if not exists applications_user_located_idx
  on public.applications (user_id, location_lat)
  where location_lat is not null;

-- ----------------------------------------------------------------- meetings
create table if not exists public.meetings (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid not null references auth.users (id) on delete cascade,
  application_id  uuid not null references public.applications (id) on delete cascade,
  scheduled_at    timestamptz,
  purpose         text,
  participants    text[],
  challenge       text,
  outcome         text default 'pending',
  notes           text,
  created_at      timestamptz not null default now()
);
create index if not exists meetings_user_idx on public.meetings (user_id);
create index if not exists meetings_application_idx on public.meetings (application_id);

-- ----------------------------------------------------------- status_history
create table if not exists public.status_history (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid not null references auth.users (id) on delete cascade,
  application_id  uuid not null references public.applications (id) on delete cascade,
  status          text not null,
  changed_at      timestamptz not null default now()
);
create index if not exists status_history_user_idx on public.status_history (user_id);
create index if not exists status_history_application_idx on public.status_history (application_id);

-- -------------------------------------------------------------------- notes
create table if not exists public.notes (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid not null references auth.users (id) on delete cascade,
  application_id  uuid not null references public.applications (id) on delete cascade,
  content         text not null,
  created_at      timestamptz not null default now()
);
create index if not exists notes_application_idx on public.notes (application_id);

-- ---------------------------------------------------------------- documents
create table if not exists public.documents (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid not null references auth.users (id) on delete cascade,
  application_id  uuid not null references public.applications (id) on delete cascade,
  file_name       text not null,
  file_url        text not null,
  type            text,
  uploaded_at     timestamptz not null default now()
);
create index if not exists documents_application_idx on public.documents (application_id);

-- ------------------------------------------------------- updated_at trigger
-- defaults only fire on insert; without this, updated_at never moves.
create or replace function public.touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists applications_touch_updated_at on public.applications;
create trigger applications_touch_updated_at
  before update on public.applications
  for each row execute function public.touch_updated_at();

-- ----------------------------------------------------------- Row Level Security
-- Without this, the publishable key would let any authenticated user read every
-- other user's rows. Each table carries user_id directly, so the check is one
-- indexed comparison rather than a join back through applications.
do $$
declare t text;
begin
  foreach t in array array[
    'companies', 'contacts', 'applications',
    'meetings', 'status_history', 'notes', 'documents'
  ]
  loop
    execute format('alter table public.%I enable row level security', t);
    execute format('drop policy if exists owner_all on public.%I', t);
    execute format(
      'create policy owner_all on public.%I for all to authenticated
         using (user_id = (select auth.uid()))
         with check (user_id = (select auth.uid()))', t);
  end loop;
end $$;

-- ----------------------------------------------------------------- profiles
create table if not exists public.profiles (
  id          uuid primary key references auth.users (id) on delete cascade,
  email       text,
  full_name   text,
  avatar_url  text,
  created_at  timestamptz not null default now()
);

alter table public.profiles enable row level security;
drop policy if exists self_all on public.profiles;
create policy self_all on public.profiles for all to authenticated
  using (id = (select auth.uid()))
  with check (id = (select auth.uid()));

-- Provisioning. Runs inside the same transaction that creates the auth user, so
-- a signed-in user always has a profile — there is no window where the app sees
-- a session without one.
--
-- security definer: the caller during signup is the auth system, which has no
-- rights on public.profiles. search_path is pinned empty and every name below is
-- schema-qualified, so a definer function cannot be hijacked by a caller-set
-- search_path.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    -- Google returns full_name; name is the fallback for other providers.
    coalesce(new.raw_user_meta_data ->> 'full_name', new.raw_user_meta_data ->> 'name'),
    coalesce(new.raw_user_meta_data ->> 'avatar_url', new.raw_user_meta_data ->> 'picture')
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- Backfill anyone who signed up before this migration.
insert into public.profiles (id, email, full_name, avatar_url)
select u.id,
       u.email,
       coalesce(u.raw_user_meta_data ->> 'full_name', u.raw_user_meta_data ->> 'name'),
       coalesce(u.raw_user_meta_data ->> 'avatar_url', u.raw_user_meta_data ->> 'picture')
from auth.users u
on conflict (id) do nothing;

-- ---------------------------------------------------------------- dashboard
create or replace function public.dashboard_stats(p_tz text default 'UTC')
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
with apps as (
  -- Wishlist entries were never sent, so they are excluded from every rate.
  select a.*,
         -- first_response_at is the explicit signal; status is the fallback for
         -- rows created directly past 'applied'. A rejection is a response.
         (a.first_response_at is not null
          or a.status in ('phone_screen', 'interview', 'offer', 'rejected')) as responded
  from public.applications a
  where a.status <> 'wishlist'
),
totals as (
  select count(*)::int                                                   as applied,
         count(*) filter (where responded)::int                          as responded,
         count(*) filter (where status in ('applied', 'phone_screen',
                                           'interview', 'offer'))::int   as active,
         count(*) filter (where status = 'offer'
                          or outcome in ('accepted', 'declined'))::int   as offers
  from apps
),
weeks as (
  select gs::date as week_start
  from generate_series(
         date_trunc('week', now() at time zone p_tz) - interval '11 weeks',
         date_trunc('week', now() at time zone p_tz),
         interval '1 week') as gs
),
weekly as (
  select w.week_start,
         count(a.id)::int as applications
  from weeks w
  left join apps a
    on date_trunc('week', a.applied_at at time zone p_tz)::date = w.week_start
  group by w.week_start
),
by_platform as (
  -- Where the application was submitted, falling back to where it was found:
  -- the old system's /analytic/website grouped the same way.
  select coalesce(nullif(platform_applied, ''), nullif(platform_found, ''), 'Unknown') as platform,
         count(*)::int                         as applied,
         count(*) filter (where responded)::int as responded
  from apps
  group by 1
),
by_type as (
  select coalesce(application_type, 'unspecified') as application_type,
         count(*)::int                              as applied,
         count(*) filter (where responded)::int     as responded,
         count(*) filter (
           where status in ('interview', 'offer')
              or exists (select 1 from public.meetings m where m.application_id = apps.id)
         )::int                                     as interviewed
  from apps
  group by 1
),
rounds as (
  select count(*)::int                             as applications_with_rounds,
         coalesce(round(avg(n)::numeric, 1), 0)    as avg_rounds
  from (select application_id, count(*) as n
        from public.meetings
        group by application_id) per_app
)
select jsonb_build_object(
  'totals',      (select to_jsonb(t) from totals t),
  'weekly',      coalesce((select jsonb_agg(to_jsonb(w) order by w.week_start) from weekly w), '[]'::jsonb),
  'by_platform', coalesce((select jsonb_agg(to_jsonb(p) order by p.applied desc, p.platform) from by_platform p), '[]'::jsonb),
  'by_type',     coalesce((select jsonb_agg(to_jsonb(b) order by b.application_type) from by_type b), '[]'::jsonb),
  'rounds',      (select to_jsonb(r) from rounds r)
);
$$;

-- Supabase grants EXECUTE on new public functions to anon by default. RLS would
-- return an empty dashboard to anon anyway; revoking makes that explicit.
revoke all on function public.dashboard_stats(text) from public, anon;
grant execute on function public.dashboard_stats(text) to authenticated;