277 lines
15 KiB
SQL
277 lines
15 KiB
SQL
 
|
-- ============================================================================
|
||
|
|
-- Migration 001 — schéma initial de la veille législative
|
||
|
|
--
|
||
|
|
-- Sémantique conforme au §4 du prompt de mission. Les libellés d'énumération
|
||
|
|
-- sont ceux du corpus de recherche : ils ne sont pas traduits ni normalisés,
|
||
|
|
-- de façon à ce qu'une valeur en base soit toujours retrouvable dans un
|
||
|
|
-- fichier de `data/input/`.
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
PRAGMA journal_mode = WAL;
|
||
|
|
PRAGMA foreign_keys = ON;
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Table principale : un texte législatif ou une décision juridictionnelle
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE textes (
|
||
|
|
-- Identifiant lisible et stable, servant aussi d'URL : « loi-2026-491 ».
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
|
||
|
|
-- Numéro officiel une fois attribué (« 2026-491 »). NULL tant que le texte
|
||
|
|
-- n'est pas promulgué : c'est précisément ce qui distingue un texte adopté
|
||
|
|
-- d'un texte en vigueur.
|
||
|
|
numero_officiel TEXT,
|
||
|
|
|
||
|
|
type TEXT NOT NULL CHECK (type IN (
|
||
|
|
'loi', 'loi_organique', 'pjl', 'ppl', 'ordonnance',
|
||
|
|
'decision_cc', 'decret', 'accord_international')),
|
||
|
|
|
||
|
|
titre_court TEXT NOT NULL,
|
||
|
|
titre_officiel TEXT,
|
||
|
|
|
||
|
|
-- Règle d'or du rapport : le statut prime sur l'intitulé.
|
||
|
|
statut TEXT NOT NULL CHECK (statut IN (
|
||
|
|
'promulguee', 'adoptee_non_promulguee', 'saisie_cc',
|
||
|
|
'validee_cc', 'censuree_partiellement', 'navette',
|
||
|
|
'deposee_non_examinee', 'annoncee', 'rejetee')),
|
||
|
|
statut_date TEXT, -- date du dernier changement de statut (ISO 8601)
|
||
|
|
|
||
|
|
date_depot TEXT,
|
||
|
|
date_adoption TEXT,
|
||
|
|
date_promulgation TEXT,
|
||
|
|
date_entree_vigueur TEXT,
|
||
|
|
prochaine_echeance TEXT,
|
||
|
|
prochaine_echeance_label TEXT,
|
||
|
|
|
||
|
|
-- Tableaux JSON. SQLite les stocke en texte ; les fonctions json_*
|
||
|
|
-- permettent de filtrer sans table de jointure supplémentaire.
|
||
|
|
themes TEXT NOT NULL DEFAULT '[]',
|
||
|
|
|
||
|
|
-- Objet JSON : { "individus": {"sens": "negatif", "note": "…"}, … }
|
||
|
|
-- Sens admis : positif | negatif | mixte | neutre.
|
||
|
|
impacts TEXT NOT NULL DEFAULT '{}',
|
||
|
|
|
||
|
|
guadeloupe_pertinence TEXT CHECK (guadeloupe_pertinence IN ('forte', 'moyenne', 'faible')),
|
||
|
|
guadeloupe_note TEXT,
|
||
|
|
|
||
|
|
resume TEXT,
|
||
|
|
points_cles TEXT NOT NULL DEFAULT '[]',
|
||
|
|
|
||
|
|
confiance TEXT NOT NULL DEFAULT 'medium'
|
||
|
|
CHECK (confiance IN ('high', 'medium', 'low')),
|
||
|
|
|
||
|
|
-- Fichier de `data/input/` dont provient l'entrée, ou nom du collecteur.
|
||
|
|
source_seed TEXT,
|
||
|
|
|
||
|
|
-- Drapeau de revue humaine : toute classification automatique le lève.
|
||
|
|
a_verifier INTEGER NOT NULL DEFAULT 0 CHECK (a_verifier IN (0, 1)),
|
||
|
|
motif_verification TEXT,
|
||
|
|
|
||
|
|
derniere_verif TEXT,
|
||
|
|
cree_le TEXT NOT NULL DEFAULT (datetime('now')),
|
||
|
|
maj_le TEXT NOT NULL DEFAULT (datetime('now'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX idx_textes_numero ON textes (numero_officiel)
|
||
|
|
WHERE numero_officiel IS NOT NULL;
|
||
|
|
CREATE INDEX idx_textes_statut ON textes (statut);
|
||
|
|
CREATE INDEX idx_textes_type ON textes (type);
|
||
|
|
CREATE INDEX idx_textes_gpe ON textes (guadeloupe_pertinence);
|
||
|
|
CREATE INDEX idx_textes_promulgation ON textes (date_promulgation);
|
||
|
|
CREATE INDEX idx_textes_echeance ON textes (prochaine_echeance);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Sources : une citation vérifiable par fait important
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE sources (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
texte_id TEXT NOT NULL REFERENCES textes (id) ON DELETE CASCADE,
|
||
|
|
|
||
|
|
url TEXT NOT NULL,
|
||
|
|
titre TEXT,
|
||
|
|
editeur TEXT,
|
||
|
|
date_publication TEXT,
|
||
|
|
|
||
|
|
-- T1 = source primaire (Légifrance/JO, AN, Sénat, Conseil constitutionnel,
|
||
|
|
-- Conseil d'État, vie-publique, préfectures) ; T2 = presse et analyses.
|
||
|
|
tier TEXT NOT NULL DEFAULT 'T2' CHECK (tier IN ('T1', 'T2')),
|
||
|
|
|
||
|
|
extrait_verbatim TEXT,
|
||
|
|
contexte TEXT,
|
||
|
|
confiance TEXT CHECK (confiance IN ('high', 'medium', 'low')),
|
||
|
|
|
||
|
|
-- Marqueur d'origine dans le corpus : « dim07-24 », « phase5-3.1 », « ref-481 ».
|
||
|
|
marqueur TEXT,
|
||
|
|
fichier_origine TEXT,
|
||
|
|
|
||
|
|
cree_le TEXT NOT NULL DEFAULT (datetime('now'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_sources_texte ON sources (texte_id);
|
||
|
|
CREATE UNIQUE INDEX idx_sources_unicite ON sources (texte_id, url, COALESCE(marqueur, ''));
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Événements : la timeline parlementaire d'un texte
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE evenements (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
texte_id TEXT NOT NULL REFERENCES textes (id) ON DELETE CASCADE,
|
||
|
|
|
||
|
|
date_evenement TEXT NOT NULL,
|
||
|
|
type_etape TEXT NOT NULL CHECK (type_etape IN (
|
||
|
|
'depot', 'adoption_1re_lecture', 'adoption_definitive',
|
||
|
|
'commission_mixte_paritaire', 'transmission', 'saisine_cc',
|
||
|
|
'decision_cc', 'promulgation', 'publication_jo',
|
||
|
|
'entree_vigueur', 'rejet', 'annonce', 'autre')),
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
source_url TEXT,
|
||
|
|
|
||
|
|
-- Vrai lorsque l'événement est postérieur à la date d'arrêté des données :
|
||
|
|
-- il s'agit alors d'une échéance prévisionnelle, pas d'un fait acquis.
|
||
|
|
previsionnel INTEGER NOT NULL DEFAULT 0 CHECK (previsionnel IN (0, 1)),
|
||
|
|
|
||
|
|
cree_le TEXT NOT NULL DEFAULT (datetime('now'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_evenements_texte ON evenements (texte_id, date_evenement);
|
||
|
|
CREATE INDEX idx_evenements_date ON evenements (date_evenement);
|
||
|
|
CREATE UNIQUE INDEX idx_evenements_unicite
|
||
|
|
ON evenements (texte_id, date_evenement, type_etape, description);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Décisions du Conseil constitutionnel
|
||
|
|
--
|
||
|
|
-- `date_decision IS NULL` signifie « affaire en instance » : c'est ce qui
|
||
|
|
-- alimente la facette « saisi du Conseil constitutionnel » de l'interface.
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE decisions_cc (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
texte_id TEXT REFERENCES textes (id) ON DELETE SET NULL,
|
||
|
|
|
||
|
|
numero_affaire TEXT NOT NULL UNIQUE, -- « 2026-915 DC »
|
||
|
|
date_saisine TEXT,
|
||
|
|
date_decision TEXT,
|
||
|
|
resultat TEXT CHECK (resultat IN (
|
||
|
|
'conforme', 'conforme_avec_reserves', 'non_conformite_partielle',
|
||
|
|
'non_conformite_totale', 'en_instance')),
|
||
|
|
saisissants TEXT,
|
||
|
|
resume TEXT,
|
||
|
|
url TEXT,
|
||
|
|
|
||
|
|
date_decision_attendue TEXT, -- prévisionnel, tant que la décision n'est pas rendue
|
||
|
|
cree_le TEXT NOT NULL DEFAULT (datetime('now')),
|
||
|
|
maj_le TEXT NOT NULL DEFAULT (datetime('now'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_cc_texte ON decisions_cc (texte_id);
|
||
|
|
CREATE INDEX idx_cc_instance ON decisions_cc (date_decision);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Analyses transversales issues de `lois-2026_insight.md`
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE insights (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
numero INTEGER NOT NULL UNIQUE,
|
||
|
|
titre TEXT NOT NULL,
|
||
|
|
corps TEXT NOT NULL,
|
||
|
|
implications TEXT,
|
||
|
|
confiance TEXT NOT NULL DEFAULT 'medium',
|
||
|
|
derive_de TEXT NOT NULL DEFAULT '[]', -- JSON : ["dim02", "dim03", …]
|
||
|
|
fichier_origine TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Journal des exécutions du pipeline
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE TABLE veille_log (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
horodatage TEXT NOT NULL DEFAULT (datetime('now')),
|
||
|
|
mode TEXT NOT NULL CHECK (mode IN ('seed', 'update', 'dry-run')),
|
||
|
|
duree_s REAL,
|
||
|
|
ajouts INTEGER NOT NULL DEFAULT 0,
|
||
|
|
modifications INTEGER NOT NULL DEFAULT 0,
|
||
|
|
echecs INTEGER NOT NULL DEFAULT 0,
|
||
|
|
alertes TEXT NOT NULL DEFAULT '[]', -- JSON
|
||
|
|
rapport TEXT -- compte-rendu lisible
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_veille_log_date ON veille_log (horodatage DESC);
|
||
|
|
|
||
|
|
-- Détail des changements d'un run, pour l'encart « derniers changements ».
|
||
|
|
CREATE TABLE veille_changements (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
run_id INTEGER NOT NULL REFERENCES veille_log (id) ON DELETE CASCADE,
|
||
|
|
texte_id TEXT,
|
||
|
|
nature TEXT NOT NULL CHECK (nature IN ('ajout', 'statut', 'date', 'source', 'autre')),
|
||
|
|
champ TEXT,
|
||
|
|
ancienne_valeur TEXT,
|
||
|
|
nouvelle_valeur TEXT,
|
||
|
|
description TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_changements_run ON veille_changements (run_id);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Recherche plein texte
|
||
|
|
--
|
||
|
|
-- Table FTS5 à contenu externe : l'index ne duplique pas les données, il est
|
||
|
|
-- tenu à jour par les déclencheurs ci-dessous. `remove_diacritics 2` permet de
|
||
|
|
-- trouver « chlordécone » en tapant « chlordecone ».
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE VIRTUAL TABLE textes_fts USING fts5 (
|
||
|
|
titre_court,
|
||
|
|
titre_officiel,
|
||
|
|
resume,
|
||
|
|
points_cles,
|
||
|
|
guadeloupe_note,
|
||
|
|
numero_officiel,
|
||
|
|
content = 'textes',
|
||
|
|
content_rowid = 'rowid',
|
||
|
|
tokenize = "unicode61 remove_diacritics 2"
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TRIGGER textes_fts_ai AFTER INSERT ON textes BEGIN
|
||
|
|
INSERT INTO textes_fts (rowid, titre_court, titre_officiel, resume, points_cles,
|
||
|
|
guadeloupe_note, numero_officiel)
|
||
|
|
VALUES (new.rowid, new.titre_court, new.titre_officiel, new.resume, new.points_cles,
|
||
|
|
new.guadeloupe_note, new.numero_officiel);
|
||
|
|
END;
|
||
|
|
|
||
|
|
CREATE TRIGGER textes_fts_ad AFTER DELETE ON textes BEGIN
|
||
|
|
INSERT INTO textes_fts (textes_fts, rowid, titre_court, titre_officiel, resume,
|
||
|
|
points_cles, guadeloupe_note, numero_officiel)
|
||
|
|
VALUES ('delete', old.rowid, old.titre_court, old.titre_officiel, old.resume,
|
||
|
|
old.points_cles, old.guadeloupe_note, old.numero_officiel);
|
||
|
|
END;
|
||
|
|
|
||
|
|
CREATE TRIGGER textes_fts_au AFTER UPDATE ON textes BEGIN
|
||
|
|
INSERT INTO textes_fts (textes_fts, rowid, titre_court, titre_officiel, resume,
|
||
|
|
points_cles, guadeloupe_note, numero_officiel)
|
||
|
|
VALUES ('delete', old.rowid, old.titre_court, old.titre_officiel, old.resume,
|
||
|
|
old.points_cles, old.guadeloupe_note, old.numero_officiel);
|
||
|
|
INSERT INTO textes_fts (rowid, titre_court, titre_officiel, resume, points_cles,
|
||
|
|
guadeloupe_note, numero_officiel)
|
||
|
|
VALUES (new.rowid, new.titre_court, new.titre_officiel, new.resume, new.points_cles,
|
||
|
|
new.guadeloupe_note, new.numero_officiel);
|
||
|
|
END;
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- Vue de consultation : ajoute les informations dérivées dont l'interface a
|
||
|
|
-- besoin sans dupliquer d'état en base.
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
CREATE VIEW v_textes AS
|
||
|
|
SELECT
|
||
|
|
t.*,
|
||
|
|
(SELECT COUNT(*) FROM sources s WHERE s.texte_id = t.id) AS nb_sources,
|
||
|
|
(SELECT COUNT(*) FROM evenements e WHERE e.texte_id = t.id) AS nb_evenements,
|
||
|
|
-- Un texte est « devant le Conseil constitutionnel » soit par son statut,
|
||
|
|
-- soit parce qu'une affaire le concernant est en instance. Les deux voies
|
||
|
|
-- sont nécessaires : le §7 du prompt impose que la loi « Riposte » garde le
|
||
|
|
-- statut `adoptee_non_promulguee`, alors qu'elle doit apparaître dans la
|
||
|
|
-- facette « saisi du Conseil constitutionnel ».
|
||
|
|
CASE WHEN t.statut = 'saisie_cc'
|
||
|
|
OR EXISTS (SELECT 1 FROM decisions_cc d
|
||
|
|
WHERE d.texte_id = t.id AND d.date_decision IS NULL)
|
||
|
|
THEN 1 ELSE 0 END AS devant_cc
|
||
|
|
FROM textes t;
|