Przejdź do treści

02 — Data Model

Insurance Intelligence System — PostgreSQL Schema


Zasady projektowania bazy

  1. Surowe dane nigdy nie są nadpisywane — raw_signals to append-only
  2. Klasyfikacja jest oddzielna od danych — można reklasyfikować bez utraty oryginału
  3. Każdy rekord ma źródło i timestamp — pełna audytowalność
  4. JSONB dla danych surowych — elastyczność bez zmiany schematu przy nowych źródłach
  5. Segmenty jako enum — spójność i możliwość filtrowania
  6. Point-in-time versioning dla metryk finansowych — as_of_date (okresu którego dotyczy dana) jest oddzielone od captured_at (kiedy system się o niej dowiedział). KNF i TU czasem korygują dane wstecznie — raport odtworzony za 3 miesiące musi pokazywać te same liczby, które były znane w momencie jego wygenerowania, chyba że świadomie żądamy najnowszej korekty.
  7. Każda wartość wyliczona/szacowana ma log kalkulacji — jeśli agent (Extractor lub Analyst) liczy coś, czego nie ma wprost w źródle (np. market share, trend delta), zapisuje logikę do calculation_log (patrz niżej). Bez tego wartość nie może trafić do dashboardu jako "gotowa do zacytowania klientowi".

Schemat bazy danych

Tabela: raw_signals

Surowe dane ze wszystkich źródeł. Append-only — nigdy nie modyfikowana.

CREATE TABLE raw_signals (
    id              SERIAL PRIMARY KEY,
    source_type     VARCHAR(50) NOT NULL,
    -- 'youtube' | 'knf' | 'piu' | 'nbp' | 'podcast' | 'media'
    source_id       VARCHAR(200),
    -- np. video_id dla YouTube, URL dla stron
    collected_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at    DATE,
    title           TEXT,
    raw_data        JSONB NOT NULL,
    -- pełne dane z API / scraper w oryginalnej formie
    description_urls TEXT[],
    -- URL-e wyciągnięte z opisów (linki do raportów itp.)
    checksum        VARCHAR(64) UNIQUE
    -- SHA256 z source_id — zapobiega duplikatom
);

CREATE INDEX idx_raw_signals_source ON raw_signals(source_type, collected_at);
CREATE INDEX idx_raw_signals_published ON raw_signals(published_at);

Tabela: extracted_signals

Wyniki Agenta 2 (Extractor) — znormalizowana, przetłumaczona na polski parafraza treści z source_documents, gotowa do klasyfikacji. Dodana 2026-09-22 przy implementacji Agenta 2+3.

CREATE TABLE extracted_signals (
    id                  SERIAL PRIMARY KEY,
    source_document_id  INTEGER NOT NULL REFERENCES source_documents(id),
    extracted_text       TEXT NOT NULL,
    -- parafraza PL, nigdy cytat 1:1 (zasada prawna, patrz 03_AGENTS.md)
    companies_mentioned   TEXT[],
    roles_mentioned       TEXT[],
    topics                 TEXT[],
    sentiment               VARCHAR(20),
    extraction_method       VARCHAR(30) NOT NULL DEFAULT 'claude_code_cli',
    confidence_score         SMALLINT CHECK (confidence_score BETWEEN 0 AND 100),
    -- pewność MODELU co do własnej oceny — NIE trafność treści (to jest w classified_signals.relevance_score)
    primary_topic            signal_topic_category,
    secondary_topic          signal_topic_category,
    -- dodane 2026-09-23, ENUM zdefiniowany w classified_signals wyżej — patrz tam wyjaśnienie
    -- różnicy segment vs primary_topic. Zduplikowane na obu tabelach tak jak companies_mentioned/
    -- roles_mentioned/topics — Extractor i Classifier w tym miejscu działają jako jedno wywołanie Claude
    extracted_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_extracted_signals_doc ON extracted_signals(source_document_id);
CREATE INDEX idx_extracted_signals_companies ON extracted_signals USING GIN(companies_mentioned);
CREATE INDEX idx_extracted_primary_topic ON extracted_signals(primary_topic);
CREATE INDEX idx_extracted_secondary_topic ON extracted_signals(secondary_topic);

Kolumna source_documents.extraction_status (pending | done | rejected_prefilter | failed) śledzi postęp narracyjnej ekstrakcji — dodana 2026-09-22.

Kolumna source_documents.financial_extraction_status (pending | done | rejected_prefilter | failed) śledzi postęp ekstrakcji liczb finansowych przez ir_financial_extraction_agent.py — niezależna od extraction_status. Dodana 2026-09-23. Dotyczy tylko dokumentów source_type='tu_ir' których tytuł wskazuje na dane finansowe (pre-filtr regex).

Tabela: classified_signals

Wyniki klasyfikacji przez Agenta 3. Jeden rekord na raw_signal (dane z Agent 1a YouTube) LUB na extracted_signal (dane z Agent 1b/2 Web Scout+Extractor) — kolumna extracted_signal_id dodana 2026-09-22, raw_signal_id pozostaje dla zgodności ze starszymi danymi. Co najmniej jedno z dwóch musi być ustawione (CHECK constraint).

CREATE TYPE signal_segment AS ENUM (
    'ceo_cfo_tu',
    'financials_tu',
    'regulators',
    'conferences',
    'bancassurance',
    'intermediaries',
    'expert_analysis',
    'eu_groups',
    'noise',
    'unclassified'
);

CREATE TYPE classification_method AS ENUM (
    'rules',        -- sklasyfikowany przez reguły
    'claude_api',   -- sklasyfikowany przez Claude API
    'manual'        -- poprawiony ręcznie przez właściciela projektu
);

-- dodane 2026-09-23 przy implementacji dashboardu
CREATE TYPE signal_topic_category AS ENUM (
    'finanse_wyniki',        -- liczby finansowe na poziomie wyniku TU (GWP, COR, wynik netto, rentowność, rating)
    'produkty_oferta',       -- konkretny produkt — nowy, zmieniony, wycofany, zmiana taryfy dla klienta
    'sprzedaz_dystrybucja',  -- kanał sprzedaży, sieć agencyjna, bancassurance, partnerstwo, wolumen polis
    'likwidacja_obsluga',    -- obsługa klienta po sprzedaży — likwidacja szkód, czas obsługi, skargi, NPS
    'technologia_digital',   -- system, technologia, dostawca IT, AI, chmura, automatyzacja, cyberbezpieczeństwo
    'strategia_ma',          -- M&A, przejęcie podmiotu, nowy rynek geograficzny, joint venture, deklaracja strategiczna
    'regulacje_compliance',  -- decyzja KNF, kara, postępowanie, nowy przepis, wymóg compliance/ESG prawny
    'marka_komunikacja',     -- kampania reklamowa, sponsoring, PR, rebranding, nagroda, wizerunek
    'organizacja_kadry',     -- zmiana personalna w zarządzie/RN, odejścia, zatrudnienia/zwolnienia na dużą skalę
    'inne'                   -- rynek jako całość, trendy makro, branża bez przypisania do konkretnego TU
);

CREATE TABLE classified_signals (
    id                      SERIAL PRIMARY KEY,
    raw_signal_id           INTEGER NOT NULL REFERENCES raw_signals(id),
    segment                 signal_segment NOT NULL,
    relevance_score         SMALLINT CHECK (relevance_score BETWEEN 0 AND 100),
    is_noise                BOOLEAN NOT NULL DEFAULT FALSE,
    classification_method   classification_method NOT NULL,
    classification_reason   TEXT,
    -- krótkie uzasadnienie (szczeg. dla claude_api i manual)
    companies_mentioned     TEXT[],
    -- ['PZU', 'Warta'] — TU wymienione w tytule/opisie
    roles_mentioned         TEXT[],
    -- ['prezes', 'dyrektor sprzedaży'] — stanowiska
    topics                  TEXT[],
    -- ['wyniki finansowe', 'strategia', 'AI'] — wolne tagi, nieustrukturyzowane
    primary_topic            signal_topic_category,
    secondary_topic          signal_topic_category,
    -- dodane 2026-09-23 — patrz niżej różnica względem `segment`
    classified_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    manually_reviewed_at    TIMESTAMPTZ,
    manually_reviewed_by    VARCHAR(100)
);

CREATE INDEX idx_classified_segment ON classified_signals(segment, relevance_score DESC);
CREATE INDEX idx_classified_noise ON classified_signals(is_noise);
CREATE INDEX idx_classified_companies ON classified_signals USING GIN(companies_mentioned);
CREATE INDEX idx_classified_primary_topic ON classified_signals(primary_topic);
CREATE INDEX idx_classified_secondary_topic ON classified_signals(secondary_topic);

segment vs primary_topic — dwa niezależne wymiary klasyfikacji (od 2026-09-23): segment odpowiada na "kto mówi / jaki typ źródła to jest" (zarząd TU, regulator, konferencja, bancassurance...) — to wymiar źródła/aktora. primary_topic/secondary_topic odpowiada na "o czym biznesowo jest treść" (finanse, produkty, technologia, kadry...) — to wymiar tematyczny, niezależny od tego kto mówi. Przykład: wywiad prezesa TU (segment=ceo_cfo_tu) może dotyczyć zarówno wyników finansowych (primary_topic=finanse_wyniki) jak i nowej strategii M&A (primary_topic=strategia_ma) — te dwie osie się nie pokrywają i obie są potrzebne do filtrowania w dashboardzie (np. "wszystkie sygnały o technologii, niezależnie od tego kto o nich mówił"). secondary_topic jest opcjonalny i ustawiany tylko gdy treść równie silnie dotyczy dwóch tematów (patrz pełna instrukcja klasyfikacji w 03_AGENTS.md).

Tabela: insights

Wyniki analizy przez Agenta 3. Jeden insight może łączyć wiele sygnałów.

CREATE TABLE insights (
    id              SERIAL PRIMARY KEY,
    signal_ids      INTEGER[] NOT NULL,
    -- powiązane classified_signals.id
    insight_type    VARCHAR(50),
    -- 'trend' | 'anomaly' | 'competitor_move' | 'regulatory_change'
    title           TEXT NOT NULL,
    summary         TEXT NOT NULL,
    companies       TEXT[],
    topics          TEXT[],
    importance      SMALLINT CHECK (importance BETWEEN 1 AND 5),
    -- 5 = krytyczny, 1 = informacyjny
    period_start    DATE,
    period_end      DATE,
    generated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    report_id       INTEGER
    -- opcjonalne powiązanie z raportem
);

Tabela: known_sources

Źródła które system już zidentyfikował jako wartościowe (kanały YouTube, strony itp.)

CREATE TABLE known_sources (
    id              SERIAL PRIMARY KEY,
    source_type     VARCHAR(50) NOT NULL,
    source_id       VARCHAR(200) NOT NULL UNIQUE,
    -- np. channel_id dla YouTube
    name            TEXT NOT NULL,
    description     TEXT,
    quality_score   SMALLINT DEFAULT 50,
    -- 0-100, rośnie z każdym wartościowym sygnałem
    segment         signal_segment,
    -- dominujący segment tego źródła
    is_monitored    BOOLEAN DEFAULT TRUE,
    first_seen_at   TIMESTAMPTZ DEFAULT NOW(),
    last_signal_at  TIMESTAMPTZ,
    signal_count    INTEGER DEFAULT 0
);

Tabela: reports

Wygenerowane raporty tygodniowe / miesięczne / na żądanie.

CREATE TABLE reports (
    id              SERIAL PRIMARY KEY,
    report_type     VARCHAR(50),
    -- 'weekly' | 'monthly' | 'adhoc' | 'benchmark'
    title           TEXT NOT NULL,
    period_start    DATE,
    period_end      DATE,
    content_html    TEXT,
    content_json    JSONB,
    generated_at    TIMESTAMPTZ DEFAULT NOW(),
    generated_by    VARCHAR(50) DEFAULT 'agent_reporter'
);

Tabela: w0_market_aggregate (dodana 2026-09-23)

Dane ZAGREGOWANE CAŁEGO RYNKU (nie per towarzystwo) z części B/C/D biuletynu kwartalnego KNF — część A (jedyna per-towarzystwo) zasila w1_market_position/ w2_financials, ale B/C/D mają wyłącznie sumy/wskaźniki rynkowe dla przekroju "Ogółem"/"Dział I" (życie)/"Dział II" (majątkowe), bez podziału na TU. Wcześniej te dane nie miały gdzie trafić — właściciel projektu zwrócił uwagę że są ważne do monitorowania ogólnej sytuacji rynkowej, niezależnie od benchmarku per-TU.

CREATE TABLE w0_market_aggregate (
    id                        SERIAL PRIMARY KEY,
    year                      SMALLINT NOT NULL,
    quarter                   SMALLINT CHECK (quarter BETWEEN 1 AND 4),
    dzial                     VARCHAR(20) NOT NULL DEFAULT 'ogolem',
    -- 'ogolem' | 'dzial_I' (życie) | 'dzial_II' (majątkowe)
    source_type               VARCHAR(20) NOT NULL DEFAULT 'knf',
    source_url                TEXT,
    collected_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_assets_pln          NUMERIC,
    total_gwp_pln             NUMERIC,
    total_gwp_growth_yoy_pct  NUMERIC,
    market_loss_ratio_pct     NUMERIC,
    market_expense_ratio_pct  NUMERIC,
    market_combined_ratio_pct NUMERIC,
    technical_result_pln      NUMERIC,
    net_profit_pln            NUMERIC,
    total_agents_exclusive_count INTEGER,
    total_ofwca_count         INTEGER,
    key_metrics               JSONB,
    -- catch-all — części B/C/D mają dziesiątki tabel, nie próbujemy z góry
    -- przewidzieć każdej sztywną kolumną
    confidence_score          SMALLINT CHECK (confidence_score BETWEEN 0 AND 100) DEFAULT 70,
    notes                     TEXT,
    UNIQUE(year, quarter, dzial)
);

CREATE INDEX idx_w0_market_year_quarter ON w0_market_aggregate(year, quarter);

UNIQUE(year, quarter, dzial) bez source_type w kluczu — dane z części B, C, D tego samego kwartału scalają się w JEDEN wiersz per (rok, kwartał, dział) przez UPSERT z COALESCE(EXCLUDED.col, w0_market_aggregate.col) (ten sam wzorzec co w1-w4) — część B dopisuje aktywa, część C dopisuje rentowność, część D dopisuje składkę/dystrybucję do tego samego wiersza, nie tworzą duplikatów.

Ekstrakcja: extractor/xlsx_extraction_agent.py --part B/C/D (ten sam agent co w1-w4, ta sama logika UPSERT — uogólniona przez TABLE_KEYS żeby obsłużyć klucz naturalny bez tu_name).

Tabela: tu_canonical_names + widok v_signals_by_tu (dodane 2026-09-24)

Normalizacja nazw towarzystw ubezpieczeniowych — rozwiązuje problem gdzie ta sama firma pojawia się pod wieloma różnymi nazwami w companies_mentioned (np. "VIG" i "Vienna Insurance Group" jako osobne encje — dashboard nie może poprawnie agregować).

CREATE TABLE tu_canonical_names (
    raw_name       VARCHAR(200) NOT NULL PRIMARY KEY,
    canonical_name VARCHAR(100) NOT NULL,  -- nazwa do wyświetlenia
    tu_key         VARCHAR(50)  NOT NULL,  -- klucz do grupowania (snake_case)
    is_pl_entity   BOOLEAN DEFAULT FALSE   -- czy wzmianka dotyczy polskiej spółki
);

is_pl_entity opisuje konkretną wzmiankę (raw_name), nie całą grupę — "Compensa" = TRUE (polska spółka VIG), "Vienna Insurance Group" = FALSE (poziom grupy). Dashboard grupuje po tu_key żeby scalić obie.

Widok do odpytywania przez dashboard — zastępuje bezpośrednie odpytanie classified_signals:

CREATE VIEW v_signals_by_tu AS
SELECT cs.id, cs.segment, cs.relevance_score, cs.is_noise,
       cs.topics, cs.primary_topic, cs.classified_at, cs.classification_reason,
       COALESCE(tcn.canonical_name, co) AS tu_canonical,
       COALESCE(tcn.tu_key, lower(regexp_replace(co, '\s+', '_', 'g'))) AS tu_key,
       COALESCE(tcn.is_pl_entity, FALSE) AS is_pl_entity
FROM classified_signals cs
CROSS JOIN LATERAL unnest(cs.companies_mentioned) AS co
LEFT JOIN tu_canonical_names tcn ON tcn.raw_name = co
WHERE cs.is_noise = FALSE;

Wynik (2026-09-24, top 5 po tu_key): PZU 387, VIG 362, Generali 144, NN Group 36, Erste 35. Tabela zawiera ~46 wpisów raw_name obejmujących główne grupy i polskie spółki córki. Do rozszerzania w miarę potrzeb dashboardu.

Konwencja: point-in-time versioning dla tabel metryk (w1–w7)

Tabele metryk finansowych i rynkowych opisane w 06_INTELLIGENCE_FRAMEWORK.md (w1_market_position, w2_financials, ...) jeszcze nie mają tu finalnego DDL — powstanie przy implementacji Extractora. Każda z nich musi mieć ten sam zestaw kolumn wersjonujących:

period_year         INT NOT NULL,
period_quarter      INT,               -- NULL jeśli metryka roczna
as_of_date          DATE NOT NULL,     -- data której dotyczy metryka (np. koniec Q2 2025)
captured_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- kiedy Extractor to zebrał
is_restated         BOOLEAN NOT NULL DEFAULT FALSE,
supersedes_id       INT REFERENCES <ta_sama_tabela>(id) -- jeśli koryguje wcześniejszy wpis

Backfill: dane finansowe (KNF, sprawozdania TU) zasilane 5 lat wstecz dla top 10 TU — patrz 03_AGENTS.md tryb --backfill Agenta 1.

Tabela: calculation_log

Log logiki kalkulacji dla każdej wartości, która nie jest kopią 1:1 ze źródła (szacunki, trendy, delty, wartości wyliczone z sumy innych sygnałów). Referencjonowana z dashboardu przy każdej wyliczonej metryce lub insight — mechanizm "zobacz źródło" dla wartości pochodnych.

CREATE TABLE calculation_log (
    id                  SERIAL PRIMARY KEY,
    agent_name          VARCHAR(50) NOT NULL,
    -- 'extractor' | 'analyst'
    output_table        VARCHAR(50) NOT NULL,
    -- gdzie trafił wynik, np. 'w1_market_position', 'insights'
    output_record_id    INTEGER NOT NULL,
    output_metric       VARCHAR(100),
    input_signal_ids    INTEGER[],
    -- z jakich raw_signals / extracted_signals policzono
    input_values        JSONB,
    formula_description TEXT NOT NULL,
    -- np. "market_share = TU_GWP / SUM(all_TU_GWP) za Q2 2025"
    reasoning           TEXT,
    -- parafraza uzasadnienia, jeśli wartość jest szacunkiem, nie twardą liczbą
    confidence_score    SMALLINT CHECK (confidence_score BETWEEN 0 AND 100),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_calc_log_output ON calculation_log(output_table, output_record_id);

Tabela: dashboard_queries (Faza 1 — telemetria Agenta 5)

Log zapytań do dashboardu — podstawa metryk adopcji (WAU, queries/user, adoption rate). Część instrumentacji Agenta 5 Reporter, nie osobny agent — ten sam trigger (żądanie z dashboardu) i te same zasoby (PostgreSQL).

Wdrażane fazowo, żeby nie projektować z góry pól, których jeszcze nie wiadomo jak używać:

-- FAZA 1 (przy budowie dashboardu MVP) — wystarcza do metryk adopcji
CREATE TABLE dashboard_queries (
    id              SERIAL PRIMARY KEY,
    user_id         VARCHAR(100) NOT NULL,
    session_id      UUID NOT NULL,
    asked_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    query_text      TEXT NOT NULL,
    query_type      VARCHAR(50),
    -- 'benchmark' | 'company_profile' | 'trend' | 'ad_hoc' | 'report_generation'
    tu_names_mentioned TEXT[],
    layer_accessed  VARCHAR(20)[],
    -- ['w2_financials', 'w6_technology'] — które warstwy odpytane
    context         VARCHAR(50)
    -- 'client_prep' | 'internal_research' | 'report_writing' | 'exploration'
);

CREATE INDEX idx_queries_user_time ON dashboard_queries(user_id, asked_at);
CREATE INDEX idx_queries_context ON dashboard_queries(context);
CREATE INDEX idx_queries_tu ON dashboard_queries USING GIN(tu_names_mentioned);

-- FAZA 2 (po 2-4 tyg. realnego użycia) — ALTER TABLE, metryki zaufania
-- ALTER TABLE dashboard_queries ADD COLUMN feedback_rating SMALLINT;
-- ALTER TABLE dashboard_queries ADD COLUMN feedback_text TEXT;
-- ALTER TABLE dashboard_queries ADD COLUMN flagged_as_incorrect BOOLEAN DEFAULT FALSE;
-- ALTER TABLE dashboard_queries ADD COLUMN would_cite_to_client BOOLEAN;
-- ALTER TABLE dashboard_queries ADD COLUMN used_in_deliverable BOOLEAN DEFAULT FALSE;

Tabela: discovery_query_log

Fundament pod samodoskonalenie zapytań Discovery (Agent 1b) — log wydajności każdego uruchomienia zapytania. Bez tego nie da się ocenić, które z 20+ zapytań faktycznie znajdują wartościowe źródła, a które marnują budżet API.

CREATE TABLE discovery_query_log (
    id                           SERIAL PRIMARY KEY,
    query_text                   TEXT NOT NULL,
    query_lang                   VARCHAR(5),
    run_at                       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    results_returned             INTEGER,
    results_passed_relevance     INTEGER,
    new_domains_found            INTEGER,
    domains_registered_permanent INTEGER
    -- najsilniejszy sygnał wartości zapytania: ile domen przeszło test
    -- powtarzalności (≥2 z ostatnich 10 wpisów RSS relevantne) i trafiło
    -- na stałe do sources_discovered.yaml
);

CREATE INDEX idx_discovery_log_query ON discovery_query_log(query_text, run_at);

Użycie (Faza 2 roadmapy, po ~miesiącu danych): zapytanie z domains_registered_permanent = 0 po 10+ uruchomieniach jest kandydatem do usunięcia z listy w sources.yaml — nie znajduje niczego wartościowego, tylko zużywa budżet.

Faza 3 (po ~miesiącu użycia) — usage_context_events: osobna tabela do trackingu zdarzeń typu "meeting_prep_started" / "deliverable_exported" bez ręcznego wypełniania formularzy, plus mikro-ankieta na koniec sesji ("ta sesja zajęła Ci ~X min, bez narzędzia zajęłaby: <15min / 15-30min / 30-60min / >1h") do policzenia time-saved. Schemat projektujemy dopiero gdy wiadomo, jak konsultanci faktycznie pracują z dashboardem — nie teraz.


Przepływ danych

YouTube API / KNF / PIU / RSS / podcasty
    └─► raw_signals (source_type, raw_data={...})
            └─► classified_signals (segment, relevance_score, companies[])
                    └─► insights (trend/anomaly/move)
                        │       └─► calculation_log (jeśli insight zawiera wartość wyliczoną)
                            └─► reports (weekly/monthly/adhoc)

known_sources ◄── aktualizowany gdy nowy kanał pojawia się wielokrotnie
dashboard_queries ◄── logowane przy każdym zapytaniu konsultanta do dashboardu

Konwencje

Element Konwencja
Nazwy tabel snake_case, liczba mnoga
Nazwy kolumn snake_case
Timestampy zawsze TIMESTAMPTZ (UTC)
Daty publikacji DATE (bez czasu)
Dane surowe JSONB (nie TEXT)
Enumy zdefiniowane jako PostgreSQL TYPE
Indeksy na każdej kolumnie używanej w WHERE/JOIN