Spaces:
Sleeping
Sleeping
File size: 4,086 Bytes
d3295cf | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | -- 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;
|