spb/trouve-ka Public

1-- Trouve-KA — migration 001 : schéma initial2-- Author: Simon-Pierre Boucher3-- Contact: contact@spboucher.ai45BEGIN;67CREATE TABLE IF NOT EXISTS schema_migrations (8    version     INTEGER PRIMARY KEY,9    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()10);1112-- ---------------------------------------------------------------------------13-- Domaines connus du web québécois (et non québécois, pour mémoire)14-- ---------------------------------------------------------------------------15CREATE TABLE IF NOT EXISTS domains (16    id                  BIGSERIAL PRIMARY KEY,17    domain              TEXT NOT NULL UNIQUE,18    first_seen          TIMESTAMPTZ NOT NULL DEFAULT now(),19    last_crawled_at     TIMESTAMPTZ,20    robots_fetched_at   TIMESTAMPTZ,21    robots_body         TEXT,22    robots_status       TEXT,            -- ok | not_found | error | forbidden23    crawl_delay_ms      INTEGER,         -- délai imposé par robots.txt (Crawl-delay)24    quebec_score        REAL NOT NULL DEFAULT 0,25    authority_score     REAL NOT NULL DEFAULT 0,26    page_count          INTEGER NOT NULL DEFAULT 0,27    inlink_domains      INTEGER NOT NULL DEFAULT 0,28    outlink_domains     INTEGER NOT NULL DEFAULT 0,29    language_stats      JSONB NOT NULL DEFAULT '{}'::jsonb,30    content_change_rate REAL,31    blocked             BOOLEAN NOT NULL DEFAULT FALSE,32    is_seed             BOOLEAN NOT NULL DEFAULT FALSE33);34CREATE INDEX IF NOT EXISTS idx_domains_quebec ON domains (quebec_score DESC);3536-- ---------------------------------------------------------------------------37-- URLs canoniques connues38-- ---------------------------------------------------------------------------39CREATE TABLE IF NOT EXISTS urls (40    id              BIGSERIAL PRIMARY KEY,41    url             TEXT NOT NULL UNIQUE,42    canonical_url   TEXT,43    domain_id       BIGINT NOT NULL REFERENCES domains(id),44    first_seen      TIMESTAMPTZ NOT NULL DEFAULT now()45);46CREATE INDEX IF NOT EXISTS idx_urls_domain ON urls (domain_id);4748-- ---------------------------------------------------------------------------49-- Frontier : la file d'URLs à crawler, avec priorités et scheduling50-- ---------------------------------------------------------------------------51CREATE TABLE IF NOT EXISTS frontier_items (52    id              BIGSERIAL PRIMARY KEY,53    url_id          BIGINT NOT NULL UNIQUE REFERENCES urls(id),54    priority        REAL NOT NULL DEFAULT 0.5,55    depth           INTEGER NOT NULL DEFAULT 0,56    source_url_id   BIGINT REFERENCES urls(id),57    discovered_at   TIMESTAMPTZ NOT NULL DEFAULT now(),58    last_crawled_at TIMESTAMPTZ,59    next_crawl_at   TIMESTAMPTZ NOT NULL DEFAULT now(),60    status          TEXT NOT NULL DEFAULT 'pending',  -- pending|in_progress|done|failed|blocked61    retries         INTEGER NOT NULL DEFAULT 0,62    error_code      TEXT,63    locked_by       TEXT,64    locked_at       TIMESTAMPTZ65);66CREATE INDEX IF NOT EXISTS idx_frontier_ready67    ON frontier_items (next_crawl_at, priority DESC)68    WHERE status = 'pending';69CREATE INDEX IF NOT EXISTS idx_frontier_status ON frontier_items (status);7071-- ---------------------------------------------------------------------------72-- Historique des tentatives de crawl (l'échec est normal; il est traqué)73-- ---------------------------------------------------------------------------74CREATE TABLE IF NOT EXISTS crawl_attempts (75    id            BIGSERIAL PRIMARY KEY,76    url_id        BIGINT NOT NULL REFERENCES urls(id),77    fetched_at    TIMESTAMPTZ NOT NULL DEFAULT now(),78    status_code   INTEGER,79    error_code    TEXT,          -- dns|timeout|tls|http_4xx|http_5xx|robots_denied|parse_failed|80                                 -- unsupported_content|too_large|duplicate|spam|not_quebec|ssrf_blocked|ok81    outcome       TEXT NOT NULL, -- indexed|duplicate|error|not_quebec|robots_blocked|redirect|unchanged82    content_hash  TEXT,83    bytes         INTEGER,84    duration_ms   INTEGER,85    redirect_url  TEXT,86    title         TEXT,87    quebec_score  REAL88);89CREATE INDEX IF NOT EXISTS idx_attempts_time ON crawl_attempts (fetched_at DESC);90CREATE INDEX IF NOT EXISTS idx_attempts_url ON crawl_attempts (url_id);9192-- ---------------------------------------------------------------------------93-- Documents indexés (métadonnées; le contenu cherchable vit dans l'index)94-- ---------------------------------------------------------------------------95CREATE TABLE IF NOT EXISTS documents (96    id                  BIGSERIAL PRIMARY KEY,97    url_id              BIGINT NOT NULL UNIQUE REFERENCES urls(id),98    content_hash        TEXT NOT NULL,99    etag                TEXT,100    last_modified       TEXT,101    title               TEXT,102    description         TEXT,103    language            TEXT,104    page_quebec_score   REAL NOT NULL DEFAULT 0,105    published_at        TIMESTAMPTZ,106    last_changed_at     TIMESTAMPTZ,107    first_indexed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),108    last_indexed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),109    enrichment_stage    INTEGER NOT NULL DEFAULT 1110);111CREATE INDEX IF NOT EXISTS idx_documents_hash ON documents (content_hash);112113-- ---------------------------------------------------------------------------114-- Graphe de liens agrégé au niveau domaine (page→page viendra plus tard)115-- ---------------------------------------------------------------------------116CREATE TABLE IF NOT EXISTS domain_links (117    from_domain_id  BIGINT NOT NULL REFERENCES domains(id),118    to_domain_id    BIGINT NOT NULL REFERENCES domains(id),119    link_count      INTEGER NOT NULL DEFAULT 1,120    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),121    PRIMARY KEY (from_domain_id, to_domain_id)122);123CREATE INDEX IF NOT EXISTS idx_domain_links_to ON domain_links (to_domain_id);124125-- ---------------------------------------------------------------------------126-- Analytics de recherche agrégées et respectueuses de la vie privée127-- (les requêtes zéro-résultat sont de l'or : elles pilotent le crawl)128-- ---------------------------------------------------------------------------129CREATE TABLE IF NOT EXISTS search_queries (130    id            BIGSERIAL PRIMARY KEY,131    query         TEXT NOT NULL,132    language      TEXT,133    results_total INTEGER NOT NULL,134    took_ms       INTEGER NOT NULL,135    zero_result   BOOLEAN NOT NULL DEFAULT FALSE,136    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()137);138CREATE INDEX IF NOT EXISTS idx_queries_zero ON search_queries (created_at) WHERE zero_result;139140-- ---------------------------------------------------------------------------141-- Soumissions publiques d'URL (soumission ≠ inclusion)142-- ---------------------------------------------------------------------------143CREATE TABLE IF NOT EXISTS submissions (144    id           BIGSERIAL PRIMARY KEY,145    url          TEXT NOT NULL,146    submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),147    status       TEXT NOT NULL DEFAULT 'queued'  -- queued|accepted|rejected148);149150INSERT INTO schema_migrations (version) VALUES (1) ON CONFLICT DO NOTHING;151152COMMIT;153