Spaces:
Running
Running
File size: 2,081 Bytes
4939f56 | 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 | -- 009_page_captures.sql — techspec.md §3
-- One row per external source queried during a pipeline run, storing the
-- human-facing page URL plus extracted text sections and figure image URLs,
-- so the final synthesis can cite real pages instead of bare API endpoints.
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = 'page_captures'
) THEN
CREATE TABLE public.page_captures (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
job_id uuid NOT NULL,
user_id uuid REFERENCES public.profiles(id) ON DELETE CASCADE,
source text NOT NULL CHECK (source IN (
'ncbi', 'uniprot', 'alphafold', 'rcsb', 'interpro',
'reactome', 'wikipathways', 'string'
)),
page_url text NOT NULL,
title text,
text_sections jsonb NOT NULL DEFAULT '[]'::jsonb,
figure_urls jsonb NOT NULL DEFAULT '[]'::jsonb,
fetch_status text NOT NULL DEFAULT 'captured'
CHECK (fetch_status IN ('captured', 'failed', 'skipped')),
error_note text,
fetched_at timestamptz DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now()
);
END IF;
END $$;
ALTER TABLE public.page_captures ENABLE ROW LEVEL SECURITY;
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'page_captures' AND policyname = 'Users can view own page captures'
) THEN
CREATE POLICY "Users can view own page captures"
ON public.page_captures FOR SELECT
USING (auth.uid() = user_id);
END IF;
IF NOT EXISTS (
SELECT 1 FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'page_captures' AND policyname = 'Users can insert own page captures'
) THEN
CREATE POLICY "Users can insert own page captures"
ON public.page_captures FOR INSERT
WITH CHECK (auth.uid() = user_id);
END IF;
END $$;
CREATE INDEX IF NOT EXISTS idx_page_captures_job ON public.page_captures(job_id);
CREATE INDEX IF NOT EXISTS idx_page_captures_user ON public.page_captures(user_id);
|