schema.dump.sql

--
-- PostgreSQL database dump
--

\restrict YFnCz7TvKbe0oFa47mMcwOmUUL3gs2ZXoAPCackpDepqGes2teSiIz1Sx4MgGzq

-- Dumped from database version 17.6 (Debian 17.6-1.pgdg13+1)
-- Dumped by pg_dump version 17.6

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;

--
-- Name: event_type; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.event_type AS ENUM (
    'investment',
    'distribution',
    'repayment',
    'conversion',
    'info'
);


ALTER TYPE public.event_type OWNER TO postgres;

--
-- Name: instrument_kind; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.instrument_kind AS ENUM (
    'equity',
    'note',
    'safe',
    'loan',
    'other'
);


ALTER TYPE public.instrument_kind OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: companies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.companies (
    company_pk bigint NOT NULL,
    company_id text NOT NULL,
    legal_name text NOT NULL,
    description text,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.companies OWNER TO postgres;

--
-- Name: companies_company_pk_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.companies_company_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.companies_company_pk_seq OWNER TO postgres;

--
-- Name: companies_company_pk_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.companies_company_pk_seq OWNED BY public.companies.company_pk;


--
-- Name: company_aliases; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.company_aliases (
    company_pk bigint,
    alias text NOT NULL
);


ALTER TABLE public.company_aliases OWNER TO postgres;

--
-- Name: documents; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.documents (
    doc_id text NOT NULL,
    company_pk bigint,
    title text,
    kind text,
    blob_url text NOT NULL,
    sha256 text NOT NULL,
    pages integer,
    embeddings_id text,
    uploaded_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.documents OWNER TO postgres;

--
-- Name: dossier_history; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.dossier_history (
    history_id bigint NOT NULL,
    company_pk bigint,
    version integer NOT NULL,
    snapshot jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.dossier_history OWNER TO postgres;

--
-- Name: dossier_history_history_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.dossier_history_history_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.dossier_history_history_id_seq OWNER TO postgres;

--
-- Name: dossier_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.dossier_history_history_id_seq OWNED BY public.dossier_history.history_id;


--
-- Name: dossier_version; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.dossier_version (
    company_pk bigint NOT NULL,
    version integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.dossier_version OWNER TO postgres;

--
-- Name: events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.events (
    event_id bigint NOT NULL,
    company_pk bigint,
    instrument_id bigint,
    event_date date NOT NULL,
    type public.event_type NOT NULL,
    amount numeric(15,2),
    subtype text,
    note text,
    party_in text,
    party_out text,
    created_at timestamp with time zone DEFAULT now(),
    ext_event_id text
);


ALTER TABLE public.events OWNER TO postgres;

--
-- Name: events_event_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.events_event_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.events_event_id_seq OWNER TO postgres;

--
-- Name: events_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.events_event_id_seq OWNED BY public.events.event_id;


--
-- Name: instruments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.instruments (
    instrument_id bigint NOT NULL,
    company_pk bigint,
    kind public.instrument_kind NOT NULL,
    name text,
    terms_json jsonb
);


ALTER TABLE public.instruments OWNER TO postgres;

--
-- Name: instruments_instrument_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.instruments_instrument_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.instruments_instrument_id_seq OWNER TO postgres;

--
-- Name: instruments_instrument_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.instruments_instrument_id_seq OWNED BY public.instruments.instrument_id;


--
-- Name: updates; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.updates (
    update_id uuid NOT NULL,
    company_pk bigint,
    date date NOT NULL,
    source_type text NOT NULL,
    source_ref text,
    update_type text NOT NULL,
    headline text NOT NULL,
    body_text text,
    structured_data jsonb,
    created_at timestamp with time zone DEFAULT now(),
    ext_update_id text
);


ALTER TABLE public.updates OWNER TO postgres;

--
-- Name: v_financials; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW public.v_financials AS
 SELECT u.company_pk,
    c.company_id,
    COALESCE((((u.structured_data -> 'financials'::text) ->> 'revenue_usd'::text))::numeric, (((u.structured_data -> 'q2_financials'::text) ->> 'bookings_usd'::text))::numeric) AS revenue_usd,
    COALESCE((((u.structured_data -> 'financials'::text) ->> 'gross_margin_pct'::text))::numeric, (((u.structured_data -> 'q2_financials'::text) ->> 'gross_margin_q2_pct'::text))::numeric) AS gross_margin_pct,
    COALESCE((((u.structured_data -> 'financials'::text) ->> 'ebitda_usd'::text))::numeric, (((u.structured_data -> 'q2_financials'::text) ->> 'ebitda_usd'::text))::numeric) AS ebitda_usd,
    u.date AS asof_date
   FROM (public.updates u
     JOIN public.companies c ON ((c.company_pk = u.company_pk)))
  WHERE ((u.structured_data ? 'financials'::text) OR (u.structured_data ? 'q2_financials'::text));


ALTER VIEW public.v_financials OWNER TO postgres;

--
-- Name: companies company_pk; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.companies ALTER COLUMN company_pk SET DEFAULT nextval('public.companies_company_pk_seq'::regclass);


--
-- Name: dossier_history history_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.dossier_history ALTER COLUMN history_id SET DEFAULT nextval('public.dossier_history_history_id_seq'::regclass);


--
-- Name: events event_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events ALTER COLUMN event_id SET DEFAULT nextval('public.events_event_id_seq'::regclass);


--
-- Name: instruments instrument_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.instruments ALTER COLUMN instrument_id SET DEFAULT nextval('public.instruments_instrument_id_seq'::regclass);


--
-- Name: companies companies_company_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.companies
    ADD CONSTRAINT companies_company_id_key UNIQUE (company_id);


--
-- Name: companies companies_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.companies
    ADD CONSTRAINT companies_pkey PRIMARY KEY (company_pk);


--
-- Name: company_aliases company_aliases_company_pk_alias_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.company_aliases
    ADD CONSTRAINT company_aliases_company_pk_alias_key UNIQUE (company_pk, alias);


--
-- Name: documents documents_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_pkey PRIMARY KEY (doc_id);


--
-- Name: dossier_history dossier_history_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.dossier_history
    ADD CONSTRAINT dossier_history_pkey PRIMARY KEY (history_id);


--
-- Name: dossier_version dossier_version_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.dossier_version
    ADD CONSTRAINT dossier_version_pkey PRIMARY KEY (company_pk);


--
-- Name: events events_ext_event_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_ext_event_id_key UNIQUE (ext_event_id);


--
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (event_id);


--
-- Name: instruments instruments_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.instruments
    ADD CONSTRAINT instruments_pkey PRIMARY KEY (instrument_id);


--
-- Name: updates updates_ext_update_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.updates
    ADD CONSTRAINT updates_ext_update_id_key UNIQUE (ext_update_id);


--
-- Name: updates updates_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.updates
    ADD CONSTRAINT updates_pkey PRIMARY KEY (update_id);


--
-- Name: idx_events_ext_event_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_events_ext_event_id ON public.events USING btree (ext_event_id);


--
-- Name: idx_updates_ext_update_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_updates_ext_update_id ON public.updates USING btree (ext_update_id);


--
-- Name: idx_updates_structured_data; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_updates_structured_data ON public.updates USING gin (structured_data);


--
-- Name: company_aliases company_aliases_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.company_aliases
    ADD CONSTRAINT company_aliases_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: documents documents_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: dossier_history dossier_history_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.dossier_history
    ADD CONSTRAINT dossier_history_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: dossier_version dossier_version_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.dossier_version
    ADD CONSTRAINT dossier_version_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: events events_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: events events_instrument_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_instrument_id_fkey FOREIGN KEY (instrument_id) REFERENCES public.instruments(instrument_id);


--
-- Name: instruments instruments_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.instruments
    ADD CONSTRAINT instruments_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- Name: updates updates_company_pk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.updates
    ADD CONSTRAINT updates_company_pk_fkey FOREIGN KEY (company_pk) REFERENCES public.companies(company_pk) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--

\unrestrict YFnCz7TvKbe0oFa47mMcwOmUUL3gs2ZXoAPCackpDepqGes2teSiIz1Sx4MgGzq