Spaces:
Runtime error
Runtime error
File size: 6,967 Bytes
769bf77 | 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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 | -- ─────────────────────────────────────────────────────────────────────────────
-- LabCard AI — Supabase Schema
-- Run this once in Supabase SQL Editor:
-- https://supabase.com → Your project → SQL Editor → New query
-- ─────────────────────────────────────────────────────────────────────────────
-- ── Extensions ────────────────────────────────────────────────────────────────
create extension if not exists "uuid-ossp";
create extension if not exists vector; -- for future RAG embeddings
-- ── Users (extends Supabase auth.users) ──────────────────────────────────────
create table if not exists public.profiles (
id uuid primary key references auth.users(id) on delete cascade,
name text,
age int,
gender text,
city text,
phone text,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
-- ── Reports ───────────────────────────────────────────────────────────────────
create table if not exists public.reports (
id uuid primary key default gen_random_uuid(),
user_id uuid references public.profiles(id) on delete cascade,
lab_name text,
report_date date,
health_score int check (health_score between 0 and 100),
biological_age int,
grade text,
summary_en text,
summary_hi text,
doctor_note text,
top_priority text,
has_critical boolean default false,
raw_text_hash text, -- SHA-256 of raw text — dedup without storing PII
created_at timestamptz default now()
);
create index if not exists idx_reports_user_date
on public.reports (user_id, created_at desc);
-- ── Biomarkers ────────────────────────────────────────────────────────────────
create table if not exists public.biomarkers (
id bigserial primary key,
report_id uuid references public.reports(id) on delete cascade,
name text not null,
std_name text,
value numeric,
unit text,
normal_low numeric,
normal_high numeric,
status text,
category text,
explanation_en text,
explanation_hi text,
advice text,
foods jsonb default '[]',
created_at timestamptz default now()
);
create index if not exists idx_biomarkers_report
on public.biomarkers (report_id);
create index if not exists idx_biomarkers_trend
on public.biomarkers (report_id, std_name, created_at);
-- ── Reference Ranges ─────────────────────────────────────────────────────────
create table if not exists public.reference_ranges (
id bigserial primary key,
biomarker text not null,
std_name text,
gender text default 'A',
age_min int default 0,
age_max int default 120,
normal_low numeric,
normal_high numeric,
critical_low numeric,
critical_high numeric,
unit text,
category text,
notes text,
updated_at timestamptz default now(),
unique (biomarker, gender)
);
-- ── Chat Messages ─────────────────────────────────────────────────────────────
create table if not exists public.chat_messages (
id bigserial primary key,
report_id uuid references public.reports(id) on delete cascade,
role text not null check (role in ('user','assistant')),
content text not null,
lang text default 'en',
created_at timestamptz default now()
);
create index if not exists idx_chat_report
on public.chat_messages (report_id, created_at);
-- ── Medical Knowledge (for future RAG) ───────────────────────────────────────
create table if not exists public.medical_knowledge (
id bigserial primary key,
content text not null,
embedding vector(1536),
metadata jsonb default '{}',
source text,
created_at timestamptz default now()
);
create index if not exists idx_medical_knowledge_embedding
on public.medical_knowledge
using hnsw (embedding vector_cosine_ops)
with (m = 16, ef_construction = 64);
-- Similarity search function
create or replace function match_medical_knowledge (
query_embedding vector(1536),
match_threshold float default 0.75,
match_count int default 5
)
returns table (id bigint, content text, metadata jsonb, similarity float)
language sql stable as $$
select id, content, metadata,
1 - (embedding <=> query_embedding) as similarity
from public.medical_knowledge
where 1 - (embedding <=> query_embedding) > match_threshold
order by embedding <=> query_embedding
limit match_count;
$$;
-- ── Row Level Security ────────────────────────────────────────────────────────
alter table public.profiles enable row level security;
alter table public.reports enable row level security;
alter table public.biomarkers enable row level security;
alter table public.chat_messages enable row level security;
-- Profiles: users can only see/edit their own profile
create policy "profiles_own" on public.profiles
for all using (auth.uid() = id);
-- Reports: users can only see their own reports
create policy "reports_own" on public.reports
for all using (auth.uid() = user_id);
-- Biomarkers: accessible through report ownership
create policy "biomarkers_own" on public.biomarkers
for all using (
report_id in (
select id from public.reports where user_id = auth.uid()
)
);
-- Chat: accessible through report ownership
create policy "chat_own" on public.chat_messages
for all using (
report_id in (
select id from public.reports where user_id = auth.uid()
)
);
-- Reference ranges: public read
create policy "ref_ranges_read" on public.reference_ranges
for select using (true);
|