Spaces:
Sleeping
Sleeping
| -- CURAS Research Database schema | |
| -- Run once in the Supabase SQL Editor (Dashboard -> SQL Editor -> New query -> Run). | |
| -- The application talks to Supabase only with the service_role key from the server. | |
| -- Row Level Security is enabled with no policies, so the public anon key can neither | |
| -- read nor write these tables. | |
| create extension if not exists pgcrypto; | |
| create table if not exists public.listings ( | |
| id uuid primary key default gen_random_uuid(), | |
| status text not null default 'published' | |
| check (status in ('published', 'retired')), | |
| faculty_name text not null, | |
| faculty_profile_url text, | |
| researchworks_url text, | |
| project_title text, | |
| keywords text, | |
| description text, | |
| methods text, | |
| opportunity_type text, | |
| term_availability text, | |
| commitment text, | |
| standing text, | |
| prerequisites text, | |
| areas_of_study text, | |
| mentorship text, | |
| previous_student_work text, | |
| faculty_note text, | |
| application_deadline text, | |
| application_requirements text, | |
| updated_on date not null default current_date, | |
| source text not null default 'form' | |
| check (source in ('excel_import', 'form', 'admin')), | |
| submission_id uuid, -- the submission that created this listing, if any | |
| retired_at timestamptz, | |
| created_at timestamptz not null default now(), | |
| updated_at timestamptz not null default now() | |
| ); | |
| create table if not exists public.submissions ( | |
| id uuid primary key default gen_random_uuid(), | |
| kind text not null check (kind in ('new', 'update')), | |
| listing_id uuid references public.listings(id) on delete set null, | |
| submitter_name text not null, | |
| submitter_netid text not null, | |
| submitter_email text, | |
| faculty_name text not null, | |
| faculty_profile_url text, | |
| researchworks_url text, | |
| project_title text, | |
| keywords text, | |
| description text, | |
| methods text, | |
| opportunity_type text, | |
| term_availability text, | |
| commitment text, | |
| standing text, | |
| prerequisites text, | |
| areas_of_study text, | |
| mentorship text, | |
| previous_student_work text, | |
| faculty_note text, | |
| application_deadline text, | |
| application_requirements text, | |
| status text not null default 'pending' | |
| check (status in ('pending', 'approved', 'rejected')), | |
| admin_note text, | |
| reviewed_at timestamptz, | |
| published_at timestamptz, -- set once the approved listing has been written | |
| created_at timestamptz not null default now() | |
| ); | |
| create index if not exists listings_status_idx on public.listings (status); | |
| create unique index if not exists listings_submission_id_idx on public.listings (submission_id) | |
| where submission_id is not null; | |
| create index if not exists submissions_status_idx on public.submissions (status, created_at desc); | |
| -- Keep updated_at current on every change. | |
| create or replace function public.set_updated_at() returns trigger | |
| language plpgsql as $$ | |
| begin | |
| new.updated_at = now(); | |
| return new; | |
| end $$; | |
| drop trigger if exists listings_set_updated_at on public.listings; | |
| create trigger listings_set_updated_at | |
| before update on public.listings | |
| for each row execute function public.set_updated_at(); | |
| -- Lock the tables down. The server uses the service_role key, which bypasses RLS. | |
| alter table public.listings enable row level security; | |
| alter table public.submissions enable row level security; | |
| revoke all on table public.listings from anon, authenticated; | |
| revoke all on table public.submissions from anon, authenticated; | |