SQL_SCHEMA.sql

--
-- PaperSorter Database Schema
-- Generated by dump-sql-schema.py
--
-- This schema includes:
-- - pgvector extension for embeddings
-- - All tables, sequences, and constraints
-- - All indexes for performance
-- - Custom types (preferences_source enum)
--

-- Create extension if not exists (required for vector type)
CREATE EXTENSION IF NOT EXISTS vector;




SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


CREATE DATABASE papersorter WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';


\connect papersorter

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


CREATE SCHEMA papersorter;



CREATE TYPE papersorter.preferences_source AS ENUM (
    'feed-star',
    'interactive',
    'alert-feedback'
);



CREATE FUNCTION papersorter.propagate_feed_source_name_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$BEGIN
    IF NEW.name <> OLD.name THEN
        UPDATE papersorter.feeds
        SET origin = NEW.name
        WHERE origin = OLD.name;
    END IF;

    RETURN NEW;
END;
$$;


SET default_tablespace = '';

SET default_table_access_method = heap;


CREATE TABLE papersorter.articles (
    id bigint NOT NULL,
    external_id text,
    title text NOT NULL,
    content text,
    author text,
    origin text,
    link text,
    mediaurl text,
    tldr text,
    published timestamp with time zone NOT NULL,
    added timestamp with time zone DEFAULT now() NOT NULL
);



CREATE SEQUENCE papersorter.articles_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.articles_id_seq OWNED BY papersorter.articles.id;



CREATE TABLE papersorter.broadcasts (
    feed_id bigint NOT NULL,
    channel_id integer NOT NULL,
    broadcasted_time timestamp with time zone
);



CREATE TABLE papersorter.channels (
    id integer NOT NULL,
    name text,
    endpoint_url text,
    score_threshold double precision,
    model_id integer,
    is_active boolean DEFAULT true NOT NULL,
    broadcast_limit integer DEFAULT 20 NOT NULL,
    broadcast_hours text,
    include_abstracts boolean DEFAULT true NOT NULL,
    CONSTRAINT broadcast_limit_check CHECK (((broadcast_limit >= 1) AND (broadcast_limit <= 100)))
);



CREATE SEQUENCE papersorter.channels_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.channels_id_seq OWNED BY papersorter.channels.id;



CREATE TABLE papersorter.embeddings (
    feed_id bigint NOT NULL,
    embedding public.vector(1536)
);



CREATE TABLE papersorter.events (
    id integer NOT NULL,
    occurred timestamp with time zone DEFAULT now() NOT NULL,
    event_type text,
    external_id text,
    content text,
    feed_id bigint,
    user_id integer
);



CREATE SEQUENCE papersorter.events_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.events_id_seq OWNED BY papersorter.events.id;



CREATE TABLE papersorter.feed_sources (
    id integer NOT NULL,
    name text NOT NULL,
    source_type text NOT NULL,
    url text,
    added timestamp with time zone DEFAULT now() NOT NULL,
    last_updated timestamp with time zone,
    last_checked timestamp with time zone
);



CREATE SEQUENCE papersorter.feed_sources_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.feed_sources_id_seq OWNED BY papersorter.feed_sources.id;



CREATE TABLE papersorter.feeds (
    id bigint NOT NULL,
    external_id text,
    title text NOT NULL,
    content text,
    author text,
    origin text,
    journal text,
    link text,
    mediaurl text,
    tldr text,
    published timestamp with time zone NOT NULL,
    added timestamp with time zone DEFAULT now() NOT NULL
);



CREATE SEQUENCE papersorter.feeds_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.feeds_id_seq OWNED BY papersorter.feeds.id;



CREATE TABLE papersorter.labeling_sessions (
    id bigint NOT NULL,
    feed_id bigint NOT NULL,
    user_id bigint NOT NULL,
    score double precision,
    update_time timestamp with time zone
);



CREATE SEQUENCE papersorter.labeling_sessions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.labeling_sessions_id_seq OWNED BY papersorter.labeling_sessions.id;



CREATE TABLE papersorter.models (
    id integer NOT NULL,
    name text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    notes text,
    score_name varchar(50) DEFAULT 'Score' NOT NULL
);



CREATE SEQUENCE papersorter.models_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.models_id_seq OWNED BY papersorter.models.id;



CREATE TABLE papersorter.predicted_preferences (
    feed_id bigint NOT NULL,
    model_id integer NOT NULL,
    score double precision NOT NULL
);



CREATE TABLE papersorter.preferences (
    id bigint NOT NULL,
    feed_id integer NOT NULL,
    user_id bigint NOT NULL,
    "time" timestamp with time zone,
    score double precision,
    source papersorter.preferences_source NOT NULL
);



CREATE SEQUENCE papersorter.preferences_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.preferences_id_seq OWNED BY papersorter.preferences.id;



CREATE TABLE papersorter.saved_searches (
    id bigint NOT NULL,
    short_name text NOT NULL,
    user_id integer,
    added timestamp with time zone DEFAULT now() NOT NULL,
    query text NOT NULL,
    last_access timestamp with time zone,
    assisted_query text
);



CREATE SEQUENCE papersorter.saved_searches_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.saved_searches_id_seq OWNED BY papersorter.saved_searches.id;



CREATE TABLE papersorter.users (
    id bigint NOT NULL,
    username text NOT NULL,
    password text NOT NULL,
    created timestamp with time zone,
    lastlogin timestamp with time zone,
    is_admin boolean DEFAULT false NOT NULL,
    theme varchar(10) DEFAULT 'light',
    timezone text DEFAULT 'Asia/Seoul'::text,
    date_format text DEFAULT 'MMM D, YYYY'::text,
    bookmark bigint,
    feedlist_minscore integer DEFAULT 25,
    primary_channel_id integer
);



CREATE SEQUENCE papersorter.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE papersorter.users_id_seq OWNED BY papersorter.users.id;



ALTER TABLE ONLY papersorter.articles ALTER COLUMN id SET DEFAULT nextval('papersorter.articles_id_seq'::regclass);



ALTER TABLE ONLY papersorter.channels ALTER COLUMN id SET DEFAULT nextval('papersorter.channels_id_seq'::regclass);



ALTER TABLE ONLY papersorter.events ALTER COLUMN id SET DEFAULT nextval('papersorter.events_id_seq'::regclass);



ALTER TABLE ONLY papersorter.feed_sources ALTER COLUMN id SET DEFAULT nextval('papersorter.feed_sources_id_seq'::regclass);



ALTER TABLE ONLY papersorter.feeds ALTER COLUMN id SET DEFAULT nextval('papersorter.feeds_id_seq'::regclass);



ALTER TABLE ONLY papersorter.labeling_sessions ALTER COLUMN id SET DEFAULT nextval('papersorter.labeling_sessions_id_seq'::regclass);



ALTER TABLE ONLY papersorter.models ALTER COLUMN id SET DEFAULT nextval('papersorter.models_id_seq'::regclass);



ALTER TABLE ONLY papersorter.preferences ALTER COLUMN id SET DEFAULT nextval('papersorter.preferences_id_seq'::regclass);



ALTER TABLE ONLY papersorter.saved_searches ALTER COLUMN id SET DEFAULT nextval('papersorter.saved_searches_id_seq'::regclass);



ALTER TABLE ONLY papersorter.users ALTER COLUMN id SET DEFAULT nextval('papersorter.users_id_seq'::regclass);



ALTER TABLE ONLY papersorter.articles
    ADD CONSTRAINT articles_external_id_unique UNIQUE (external_id);



ALTER TABLE ONLY papersorter.broadcasts
    ADD CONSTRAINT broadcast_logs_pkey PRIMARY KEY (feed_id, channel_id);



ALTER TABLE ONLY papersorter.channels
    ADD CONSTRAINT channels_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.embeddings
    ADD CONSTRAINT embeddings_tmp_pkey PRIMARY KEY (feed_id);



ALTER TABLE ONLY papersorter.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.feed_sources
    ADD CONSTRAINT feed_sources_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.feeds
    ADD CONSTRAINT feeds_external_id_unique UNIQUE (external_id);



ALTER TABLE ONLY papersorter.feeds
    ADD CONSTRAINT idx_16500_primary PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.labeling_sessions
    ADD CONSTRAINT idx_16506_primary PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.preferences
    ADD CONSTRAINT idx_16512_primary PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.models
    ADD CONSTRAINT idx_models_id_unique UNIQUE (id);



ALTER TABLE ONLY papersorter.models
    ADD CONSTRAINT models_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.articles
    ADD CONSTRAINT pk_articles_primary PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.predicted_preferences
    ADD CONSTRAINT predicted_preferences_pkey PRIMARY KEY (feed_id, model_id);



ALTER TABLE ONLY papersorter.saved_searches
    ADD CONSTRAINT saved_searches_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.saved_searches
    ADD CONSTRAINT saved_searches_short_name_key UNIQUE (short_name);



ALTER TABLE ONLY papersorter.saved_searches
    ADD CONSTRAINT uqidx_saved_searches_query UNIQUE (query);



ALTER TABLE ONLY papersorter.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);



ALTER TABLE ONLY papersorter.users
    ADD CONSTRAINT check_theme_valid CHECK (theme IN ('light', 'dark', 'auto'));



ALTER TABLE ONLY papersorter.users
    ADD CONSTRAINT users_username_key UNIQUE (username);



CREATE INDEX embeddings_tmp_embedding_idx1 ON papersorter.embeddings USING hnsw (embedding public.vector_cosine_ops);



CREATE INDEX idx_16500_idx_feeds_id ON papersorter.feeds USING btree (external_id);



CREATE INDEX idx_16512_idx_ratings_feedid ON papersorter.preferences USING btree (feed_id);



CREATE INDEX idx_articles_added ON papersorter.articles USING btree (added);



CREATE INDEX idx_articles_link ON papersorter.articles USING btree (link);



CREATE INDEX idx_articles_mediaurl ON papersorter.articles USING btree (mediaurl);



CREATE INDEX idx_articles_published ON papersorter.articles USING btree (published);



CREATE INDEX idx_articles_title ON papersorter.articles USING btree (title);



CREATE INDEX idx_broadcast_logs_time ON papersorter.broadcasts USING btree (broadcasted_time);



CREATE INDEX idx_events_feed ON papersorter.events USING btree (feed_id);



CREATE INDEX idx_events_occurred ON papersorter.events USING btree (occurred);



CREATE INDEX idx_events_type ON papersorter.events USING btree (event_type);



CREATE INDEX idx_feed_sources_last_checked ON papersorter.feed_sources USING btree (last_checked);



CREATE INDEX idx_feeds_added ON papersorter.feeds USING btree (added);



CREATE INDEX idx_feeds_link ON papersorter.feeds USING btree (link);



CREATE INDEX idx_feeds_mediaurl ON papersorter.feeds USING btree (mediaurl);



CREATE INDEX idx_feeds_published ON papersorter.feeds USING btree (published);



CREATE INDEX idx_feeds_title ON papersorter.feeds USING btree (title);



CREATE INDEX idx_labeling_session ON papersorter.labeling_sessions USING btree (user_id);



CREATE INDEX idx_labeling_sessions_score ON papersorter.labeling_sessions USING btree (user_id, score);



CREATE INDEX idx_predpref_model_score_feed ON papersorter.predicted_preferences USING btree (model_id, score DESC, feed_id);



CREATE INDEX idx_preferences_score ON papersorter.preferences USING btree (score);



CREATE INDEX idx_preferences_time ON papersorter.preferences USING btree ("time");



CREATE INDEX idx_user_id ON papersorter.preferences USING btree (user_id);



CREATE INDEX idx_users_primary_channel ON papersorter.users USING btree (primary_channel_id);



CREATE INDEX pk_articles_primary_articles_id ON papersorter.articles USING btree (external_id);



CREATE TRIGGER trg_feed_source_name_update AFTER UPDATE ON papersorter.feed_sources FOR EACH ROW EXECUTE FUNCTION papersorter.propagate_feed_source_name_update();



ALTER TABLE ONLY papersorter.embeddings
    ADD CONSTRAINT embeddings_tmp_feed_id_fkey FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.broadcasts
    ADD CONSTRAINT fk_broadcast_logs_channel FOREIGN KEY (channel_id) REFERENCES papersorter.channels(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.broadcasts
    ADD CONSTRAINT fk_broadcast_logs_feed FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.channels
    ADD CONSTRAINT fk_channels_model FOREIGN KEY (model_id) REFERENCES papersorter.models(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.events
    ADD CONSTRAINT fk_events_feed FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.events
    ADD CONSTRAINT fk_events_user FOREIGN KEY (user_id) REFERENCES papersorter.users(id) ON UPDATE CASCADE;



ALTER TABLE ONLY papersorter.labeling_sessions
    ADD CONSTRAINT fk_labeling_sessions_feed_id FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON UPDATE CASCADE ON DELETE RESTRICT;



ALTER TABLE ONLY papersorter.labeling_sessions
    ADD CONSTRAINT fk_labeling_sessions_user_id FOREIGN KEY (user_id) REFERENCES papersorter.users(id) ON UPDATE CASCADE ON DELETE RESTRICT;



ALTER TABLE ONLY papersorter.predicted_preferences
    ADD CONSTRAINT fk_pred_pref_feed FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.predicted_preferences
    ADD CONSTRAINT fk_pred_pref_model FOREIGN KEY (model_id) REFERENCES papersorter.models(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.preferences
    ADD CONSTRAINT fk_preferences_user_id FOREIGN KEY (user_id) REFERENCES papersorter.users(id) ON UPDATE CASCADE ON DELETE RESTRICT;



ALTER TABLE ONLY papersorter.preferences
    ADD CONSTRAINT fk_ratings_feedid FOREIGN KEY (feed_id) REFERENCES papersorter.feeds(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.saved_searches
    ADD CONSTRAINT fk_saved_searches_user FOREIGN KEY (user_id) REFERENCES papersorter.users(id) ON UPDATE CASCADE ON DELETE CASCADE;



ALTER TABLE ONLY papersorter.users
    ADD CONSTRAINT users_primary_channel_fk FOREIGN KEY (primary_channel_id) REFERENCES papersorter.channels(id) ON DELETE SET NULL;