didi-lot2-backend/backend/services/data-layer/didiDatabase/INDEX.md

# didiDatabase - Index

Documentatie completa pentru baza de date PostgreSQL a platformei DIDI. Baza principala DIDI ruleaza pe un **container LOCAL** (`didi-postgres`, PostgreSQL 17) pe masina de deployment, in reteaua Docker `didi-network`. Clusterul extern Patroni/HAProxy ramane configurat ca fallback HA, dar NU este folosit operational acum.

---

## PostgreSQL LOCAL (PRODUCTIE — activ)

| Parametru | Valoare |
|-----------|---------|
| Container | `didi-postgres` |
| Imagine | `postgres:17-alpine` |
| Host intern | `didi-postgres:5432` (Docker DNS pe `didi-network`) |
| Port host | `5432` expus pe `0.0.0.0:5432->5432` |
| Database | `DIDI` |
| User principal | `bos_interface` / `interface` |

### Baze de date pe instanta

Instanta `didi-postgres` contine o singura baza de business, `DIDI` (4 scheme + public, ~2012 sesiuni de analiza la data documentatiei).

| Baza / consumator | User | Folosita de | Note |
|-------------------|------|-------------|------|
| DIDI | bos_interface | agent-v3, didiFramework | schemele `bos_*` |
| DIDI (schema `public`) | bos_interface | Keycloak IAM | `KC_DB_URL=jdbc:postgresql://didi-postgres:5432/DIDI?currentSchema=public` |
| — | — | Kong API Gateway | Kong ruleaza **DBless** (config declarativ), fara baza proprie |

### Cine se conecteaza

| Serviciu | Host | Port | Database | User | Fisier config |
|----------|------|------|----------|------|---------------|
| agent-v3 | didi-postgres | 5432 | DIDI | bos_interface | agent-v3/src/shared/persistence/pg-pool.ts |
| didiFramework | didi-postgres | 5432 | DIDI | bos_interface | didiFramework/src/config/database.ts |
| Keycloak | didi-postgres | 5432 | DIDI (schema public) | bos_interface | production/.env (`KC_DB_URL`) |

Containerul local `didi-postgres` este unicul PostgreSQL de productie activ. Fostul container `staging-dataLayer-postgres` NU mai exista. Toate schemele `bos_*` + `public` sunt pe `didi-postgres`.

agent-v3 acceseaza baza prin `shared/persistence/pg-pool.ts` (`didi-postgres:5432`, DB `DIDI`, user `bos_interface`). Dupa migration 011 scrie si in `bos_analysis.moderation_queue` (prin `moderation/queue-manager.ts`) si citeste coloanele HIL noi de pe `analysis_session`.

didiFramework scrie in `bos_parammgmt.moderation_config`, `sensitive_topic`, `moderation_role` (introduse de migration 011).

---

## Baza de date DIDI -- Schema completa

4 scheme + public, ~50 tabele total.

---

### Schema: bos_analysis (7 tabele + 1 view)

Scrisa de agent-v3 (pg-adapter.ts, moderation/queue-manager.ts). Citita si de didiFramework (history.ts, sync-analysis.ts).

#### analysis_session

Tabelul central -- o inregistrare per analiza.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT PK | UUID sesiune |
| user_id | TEXT | ID utilizator |
| user_email | TEXT | Email utilizator |
| input_type | TEXT | text, url, image, audio, video |
| input_text | TEXT | Text de analizat |
| input_url | TEXT | URL analizat |
| input_media_url | TEXT | URL media MinIO |
| input_hash | TEXT | Hash input (deduplicare) |
| status | TEXT | running, completed, failed |
| components_run | TEXT[] | Componente rulate |
| components_skipped | TEXT[] | Componente sarite |
| risk_score | NUMERIC | Scor risc final (0-100) |
| risk_category | TEXT | Categorie risc |
| risk_level | TEXT | Nivel risc |
| confidence | NUMERIC | Incredere (0-100) |
| confidence_level | TEXT | Nivel incredere |
| started_at | TIMESTAMP | Start procesare |
| completed_at | TIMESTAMP | Sfarsit procesare |
| total_duration_ms | INTEGER | Durata totala ms |
| scenario_applied | TEXT | Scenariu ponderi aplicat |
| topic_applied | TEXT | Topic detectat |
| source_app | TEXT | web (default) |
| api_version | TEXT | v3 (default) |
| created_at | TIMESTAMP | Data creare |

Coloane HIL adaugate prin migration 011 (2026-05-01):

| Coloana | Tip | Scop |
|---------|-----|------|
| review_status | TEXT default 'none' (CHECK: none\|pending\|in_review\|resolved\|declined) | HIL state |
| human_corrected | BOOLEAN default false | true daca moderator a corectat |
| human_corrections | JSONB NULL | Diff-style corrections {verdict?, techniques?, ai_tampered?, claims?} |
| verified_by | TEXT NULL | keycloak_id moderator |
| verified_at | TIMESTAMPTZ NULL | When resolved |
| review_notes | TEXT NULL | Optional moderator notes |

Index partial: `idx_analysis_session_review_status WHERE review_status != 'none'` -- majoritatea sesiunilor raman 'none', sunt sarite la scan.

#### analysis_techniques

O inregistrare per sesiune -- rezultat componenta tehnici de manipulare.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT FK | Referinta sesiune |
| manipulation_score | NUMERIC | Scor manipulare (0-100) |
| total_severity | NUMERIC | Severitate totala |
| dimensions_affected | TEXT[] | Dimensiuni afectate |
| techniques_count | INTEGER | Numar tehnici detectate |
| techniques_detected | JSONB | Lista tehnici cu detalii |
| coupling_context | JSONB | Context cuplare inter-tehnici |
| llm_screening | TEXT | Model LLM screening |
| llm_deep | TEXT | Model LLM deep analysis |
| screening_duration_ms | INTEGER | Durata screening |
| deep_analysis_duration_ms | INTEGER | Durata analiza profunda |
| total_duration_ms | INTEGER | Durata totala |
| fallbacks_screening | INTEGER | Fallback-uri screening |
| fallbacks_deep | INTEGER | Fallback-uri deep |

#### analysis_ai_tampered

O inregistrare per sesiune -- detectie continut AI/manipulat.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT FK | Referinta sesiune |
| ai_probability | NUMERIC | Probabilitate AI (0-100) |
| verdict | TEXT | Verdict AI detection |
| risk_score | NUMERIC | Scor risc AI |
| categories_affected | TEXT[] | Categorii afectate |
| indicators_count | INTEGER | Numar indicatori |
| disclosure_detected | BOOLEAN | Disclosure detectat |
| disclosure_explicit | BOOLEAN | Disclosure explicit |
| disclosure_text | TEXT | Text disclosure |
| indicators_detected | JSONB | Lista indicatori |
| coupling_context | JSONB | Context cuplare |
| llm_screening | TEXT | Model screening |
| llm_deep | TEXT | Model deep |
| screening_duration_ms | INTEGER | Durata screening |
| deep_analysis_duration_ms | INTEGER | Durata deep |
| total_duration_ms | INTEGER | Durata totala |
| fallbacks_screening | INTEGER | Fallback-uri screening |
| fallbacks_deep | INTEGER | Fallback-uri deep |
| content_type | TEXT | text, image, audio, video |
| image_analysis | JSONB | Rezultat analiza imagine |

#### analysis_claims

O inregistrare per sesiune -- verificare afirmatii.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT FK | Referinta sesiune |
| total_claims | INTEGER | Total afirmatii |
| verified_true | INTEGER | Verificate adevarate |
| verified_false | INTEGER | Verificate false |
| unverified | INTEGER | Neverificate |
| opinions | INTEGER | Opinii |
| credibility_score | NUMERIC | Scor credibilitate |
| interpretation | TEXT | Interpretare |
| claims_by_status | JSONB | Claims grupate pe status |
| claims_by_type | JSONB | Claims grupate pe tip |
| claims_verified | JSONB | Detalii verificare |
| llm_extraction | TEXT | Model extragere |
| llm_verification | TEXT | Model verificare |
| extraction_duration_ms | INTEGER | Durata extragere |
| verification_duration_ms | INTEGER | Durata verificare |
| total_duration_ms | INTEGER | Durata totala |
| web_searches_made | INTEGER | Cautari web efectuate |

#### analysis_domain

O inregistrare per sesiune -- analiza domeniu/sursa.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT FK | Referinta sesiune |
| domain | TEXT | Domeniu analizat |
| verdict | TEXT | Verdict domeniu |
| trust_score | NUMERIC | Scor incredere |
| risk_level | TEXT | Nivel risc |
| age_days | INTEGER | Varsta domeniu (zile) |
| age_category | TEXT | Categorie varsta |
| domain_created_at | TIMESTAMP | Data creare domeniu |
| is_blacklisted | BOOLEAN | Pe lista neagra |
| reputation_score | NUMERIC | Scor reputatie |
| has_ssl | BOOLEAN | Are SSL |
| ssl_valid | BOOLEAN | SSL valid |
| ssl_issuer | TEXT | Emitent SSL |
| registrar | TEXT | Registrar domeniu |
| organization | TEXT | Organizatie |
| country | TEXT | Tara |
| red_flags | TEXT[] | Semnale alarma |
| warnings | TEXT[] | Avertismente |
| duration_ms | INTEGER | Durata analiza |

#### analysis_verdict

O inregistrare per sesiune -- verdictul final agregat.

| Coloana | Tip | Scop |
|---------|-----|------|
| session_id | TEXT FK | Referinta sesiune |
| risk_score | NUMERIC | Scor risc final |
| risk_category | TEXT | Categorie risc |
| risk_category_color | TEXT | Culoare categorie |
| risk_level | TEXT | Nivel risc |
| risk_level_color | TEXT | Culoare nivel |
| severity | TEXT | Severitate |
| recommended_action | TEXT | Actiune recomandata |
| confidence | NUMERIC | Incredere |
| confidence_level | TEXT | Nivel incredere |
| score_manipulation | NUMERIC | Scor componenta manipulare |
| score_claims | NUMERIC | Scor componenta claims |
| score_ai | NUMERIC | Scor componenta AI |
| score_source | NUMERIC | Scor componenta sursa |
| score_context | NUMERIC | Scor context |
| applied_weights | JSONB | Ponderi aplicate |
| override_applied | BOOLEAN | Override aplicat |
| override_type | TEXT | Tip override |
| override_reason | TEXT | Motiv override |
| override_adjustment | NUMERIC | Ajustare override |
| context_summary | JSONB | Sumar context |
| components_used | TEXT[] | Componente folosite |
| weights_source | TEXT | Sursa ponderi |
| duration_ms | INTEGER | Durata calcul |
| explanation_ro | TEXT | Explicatie romana (migration 001) |
| explanation_en | TEXT | Explicatie engleza (migration 001) |
| virality_score | NUMERIC | Scor viralitate (0-100) |
| virality_level | TEXT | Nivel viralitate |
| virality_factors | JSONB | Factori viralitate |

#### moderation_queue (adaugat prin migration 011)

Stare workflow HIL (Human-in-the-Loop). Un rand per sesiune marcata de triage pentru review uman.

| Coloana | Tip | Scop |
|---------|-----|------|
| queue_id | BIGSERIAL PK | Auto-increment |
| session_id | UUID FK -> analysis_session(session_id) ON DELETE CASCADE | Referinta sesiune |
| priority | INTEGER (1-5) | 1=highest (user_flagged), 3=low_confidence, 4=sensitive_topic |
| enqueue_reason | TEXT | flagged \| low_confidence \| sensitive_topic \| mixed |
| enqueue_meta | JSONB | Triage metadata (risk_score, confidence, topic detected) |
| status | TEXT | pending \| in_review \| resolved \| declined \| auto_closed |
| assigned_to | TEXT | keycloak_id moderator |
| assigned_at | TIMESTAMPTZ | When claimed |
| resolved_at | TIMESTAMPTZ | When closed |
| resolved_by | TEXT | keycloak_id |
| resolution_action | TEXT | approved \| corrected \| rejected |
| time_in_queue_ms | INTEGER | enqueue -> start review |
| time_in_review_ms | INTEGER | start review -> resolved |
| created_at | TIMESTAMPTZ | Default now() |

Indecsi: `idx_moderation_queue_status_priority` (partial WHERE status IN ('pending','in_review')), `idx_moderation_queue_session`, `idx_moderation_queue_assigned`.

#### v_analysis_full (VIEW)

JOIN pe toate 6 tabelele de analiza (session + techniques + ai_tampered + claims + domain + verdict). Definit in migration 001. Selecteaza doar coloane sumar (nu JSONB-uri grele): session metadata, verdict scores, techniques summary, ai probability, claims summary, domain summary + explanation_ro/en.

Migration 011 NU modifica view-ul: coloanele HIL noi de pe `analysis_session` (review_status, human_corrected etc.) sunt acoperite automat de `SELECT s.*`.

---

### Schema: bos_parammgmt (~40 tabele)

Scrisa si citita exclusiv de didiFramework. Contine toti parametrii de configurare ai platformei. Sincronizata in Redis prin POST /api/sync-redis.

Search path setat in database.ts: `SET search_path TO bos_parammgmt, public`.

#### Tabel de baza

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| parameter | Tabel parinte versionare (parameter_id, parameter_type, valid_from/to) | intern (FK din toate celelalte) |

#### Tehnici de manipulare (ierarhie 4 nivele)

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| dimension | Dimensiuni top-level (code, name, weight) | /api/dimensions |
| subdimension | Sub-dimensiuni (FK dimension) | /api/subdimensions |
| technique | Tehnici individuale (FK subdimension, severity, confidence, detectability) | /api/techniques |
| technique_indicator | Indicatori detectie per tehnica (name, description, max_intensity 1-3) | /api/indicators |
| technique_validation_rule | Reguli validare per tehnica | /api/validation-rules |

#### Evaluare sursa

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| platform | Platforme social media (code, name, score) | /api/platforms |
| platform_modifier | Modificatori platforma (condition, score) | /api/platform-modifiers |
| source_credibility | Factori credibilitate sursa | /api/source-credibility |
| source_type | Tipuri sursa (base_score) | intern |
| source_assessment | Evaluare sursa | intern |
| domain_age_score | Scor varsta domeniu (range-uri, impact) | /api/domain-age-scores |
| domain_risk_level | Nivele risc domeniu (range-uri, interpretare) | /api/domain-risk-levels |
| domain_red_flag | Red flags domeniu (condition, severity, action) | /api/domain-red-flags |
| author_classification | Clasificari autor (code, name, score) | /api/author-classifications |
| author_credibility | Credibilitate autor (impact) | /api/author-credibility |

#### Claims

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| claim | Statusuri claim (TRUE, FALSE, UNVERIFIED, OPINION) | /api/claims/status |
| claim_type | Tipuri claim (factual, statistic, cauzal, etc.) | /api/claims/types |
| confidence | Nivele incredere (level, color, action, range) | /api/claims/confidence |
| interpretation | Interpretare scor credibilitate (range-uri) | /api/claims/interpretation |

#### Verdicte si scoruri

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| verdict_category | Categorii verdict (code, range, color) | /api/verdicts/categories |
| risk_mapping | Mapping risc (level, range, color) | /api/verdicts/risk |
| severity_assessment | Evaluare severitate (category, range, action) | /api/verdicts/severity |

#### Ponderi

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| component_weight | Ponderi componente (manipulation, claims, source, ai, context) | /api/weights/components |
| weight_scenario | Scenarii ponderi (per topic: health, politics, etc.) | /api/weights/scenarios |
| multiplier | Multiplicatori (topic, temporal, reach) | /api/weights/multipliers |

#### Provideri LLM

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| llm_provider | Configurare provideri (base_url, auth_type, rate_limit) | /api/providers/configs |
| llm_model | Modele LLM (context_window, cost, capabilities) | /api/providers/models |
| component_provider_assignment | Assignment componenta -> model (legacy, pre-migration-002) | /api/providers/assignments |
| provider_api_key | Chei API per provider (criptate, usage tracking) | /api/providers/keys |

#### Configurare unificata componente (adaugat prin migration 002, extinsa cu tier prin 006)

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| component_stage_assignment | Assignment model pe etapa + **tier** (free/premium) cu fallback chain. Unique: `(component_code, stage_code, tier, fallback_order)`. | /api/providers/assignments (suporta `?tier=X` filter) |
| component_prompt | Prompturi LLM per componenta/etapa (system_prompt, user_template) | /api/providers/prompts |
| component_config | Config JSONB catch-all per componenta (scoring, patterns, vision models) | intern (sync-redis) |

**Component codes prezente dupa migrations 006-009**:
- `techniques` (stages: techniques_screening, techniques_deep)
- `ai-tampered` (stages: ai_tampered_screening, ai_tampered_deep)
- `claims` (stages: claims_extraction, claims_verification)
- `source-assessment` (stages: source_assessment_extraction, source_assessment_evaluation)
- `vision` (stage: image_analysis — OCR + AI detection + video frames, Etapa 4)
- `verdict` (stage: verdict_review — LLM verdict reviewer care ajusteaza scorul final + explicatii RO/EN, Etapa 5)

Fiecare componenta/stage are **2 tiers** (`free` + `premium`), fiecare cu propriul fallback chain (primary + 2-3 fallbacks). Ex: `techniques_screening` are 4 randuri `tier='free'` + 4 randuri `tier='premium'`.

Tier-ul final folosit la runtime se deriveaza din `planType` al userului (returnat de check-credits):
- `plan_type` 1-3 (Freemium/Starter/Basic) → `tier='free'`
- `plan_type` 4-6 (Pro/Business/Enterprise) → `tier='premium'`

#### Chei API extensie browser

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| extension_api_key | Chei API extensie browser (key, user_id, usage_count) | /api/extension-keys |

#### Profiluri verdict per input type (adaugat 2026-03-21)

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| input_type_profile | 6 profiluri verdict (text, image, audio, video, url) cu ponderi per componenta, reguli INCONCLUSIVE, disclosure multipliers | /api/input-profiles |
| profile_override_config | Override-uri per profil (8 tipuri × 6 profiluri = 48 randuri) | /api/input-profiles/:code/overrides |

Coloane noi in tabele existente:
- `claim_type.unverified_weight` NUMERIC(3,2) — ponderea UV per tip claim (0.25-0.50)
- `claim.credibility_weight` NUMERIC(3,2) — ponderea credibilitate per status claim (0.00-1.00)

#### HIL Moderation config (adaugat prin migration 011, 2026-05-01)

| Tabel | Scop | Rute CRUD |
|-------|------|-----------|
| moderation_config | Single-row settings (CHECK config_id=1): triage thresholds (confidence_low, risk_grey_min/max, queue_relax_at, queue_strict_at) + brain client config (brain_enabled, brain_url, lookup/write timeouts, brain_confidence_min_silver, brain_semantic_threshold, brain_per_component JSONB). 14 fields total. Sincronizat in Redis ca `didi:config:moderation:v1:settings`. | /api/moderation-config |
| sensitive_topic | Topics care declanseaza HIL review (seed: elections, health, war, covid, climate). topic_code regex `[a-z0-9_]+` UNIQUE; soft delete via is_active. Sincronizat in Redis ca `didi:config:moderation:v1:sensitive_topics`. | /api/sensitive-topics |
| moderation_role | Mapping Keycloak role -> HIL permissions (seed: moderator, senior_moderator). Toggles: can_resolve, can_escalate, can_force_gold_brain, is_active. role_code este PK (immutable). Sincronizat in Redis ca `didi:config:moderation:v1:roles`. | /api/moderation-roles |

---

### Schema: bos_sysadmin (5 tabele)

Scrisa si citita de didiFramework (auth.ts, admin.ts, subscriptions.ts). Management utilizatori si abonamente.

| Tabel | Scop | Rute |
|-------|------|------|
| internet_user | Utilizator platforma (internet_user_id, person_id FK, credits_remained, credits_spent) | /api/auth/me (auto-creare), /api/admin/users |
| user_credential | Credentiale (email, keycloak_id, enrollment_type, subscription_status) | /api/auth/me, /api/admin/users |
| subscription | Abonament activ (internet_user_id FK, plan FK, status, activation_date) | /api/subscriptions |
| subscription_plan | Planuri abonament (plan_name, plan_type, price, credits, limite storage/media, costuri per tip) | /api/admin/plans |
| ai_credit_usage | Log consum credite (session_id, user_id, credits_used, input_type) | /api/auth/deduct-credits |

---

### Schema: bos_subscriber (4 tabele)

Scrisa de didiFramework la inregistrare utilizator. Date personale.

| Tabel | Scop |
|-------|------|
| person | Entitate persoana (person_id, person_type, status) |
| address | Adresa (address_id, address_type) |
| persoana_fizica | Persoana fizica romaneasca (nume, prenume, FK person, FK address) |
| contact | Contact (person_id FK, contact_type_id, contact_info) |

---

### Schema: public

| Tabel | Scop |
|-------|------|
| waitlist | Lista de asteptare pre-lansare (vezi sectiunea container local) |

---

## Migratii aplicate

| Fisier | Ce face | Aplicata de |
|--------|---------|-------------|
| didiFramework/sql/migrations/001_add_explanation_columns.sql | Adauga explanation_ro, explanation_en la analysis_verdict + creeaza view v_analysis_full | didiFramework la pornire |
| didiFramework/sql/migrations/002_add_component_pilot_config.sql | Adauga tabele component_stage_assignment, component_prompt, component_config | didiFramework la pornire |
| didiFramework/sql/migrations/006_add_tier_column.sql | `component_stage_assignment.tier varchar(20) DEFAULT 'free'` + unique constraint pe (component_code, stage_code, tier, fallback_order) | Manual |
| didiFramework/sql/migrations/007_seed_premium_assignments.sql | Seed 32 rows `tier='premium'` pentru 8 stages LLM (techniques/ai-tampered/claims/source-assessment) | Manual |
| didiFramework/sql/migrations/008_seed_vision_assignments.sql | Seed 7 rows pentru component `vision` stage `image_analysis` (3 free + 4 premium) | Manual |
| didiFramework/sql/migrations/009_seed_verdict_assignments.sql | Seed 8 rows pentru component `verdict` stage `verdict_review` (4 free + 4 premium) | Manual |
| didiFramework/sql/migrations/011_add_moderation.sql | HIL Moderation foundation: 6 coloane pe `analysis_session`, tabela `moderation_queue`, 3 tabele config in bos_parammgmt (moderation_config, sensitive_topic, moderation_role) + seeds. Companion `011_rollback.sql`. session_id este UUID, FK foloseste UUID. | Manual |

Migratiile ulterioare (012 topic_volatility, 013 user_audit_log, 014 atomic_path_prefix, 015 social_post, 016 input_profile_versions, 017 model_catalog_attributes) sunt incluse integral in seed-ul canonic `DIDI_full_export_2026-07-02.sql`. Un restore curat al seed-ului produce schema completa la zi (fara a mai rula migratiile manual). Cateva dintre ele sunt descrise mai jos in "Schema additions".

---

## Container `staging-dataLayer-postgres` (ISTORIC — inexistent)

> Nota istorica: un container `staging-dataLayer-postgres` (postgres:15-alpine, database `misinformation_db`) a servit candva doar tabela `public.waitlist` + ~22 tabele legacy goale din vechiul orchestrator Python. **Acest container NU mai exista.** Baza de business (inclusiv `public.waitlist`, daca este folosita) este acum in database-ul `DIDI` de pe containerul `didi-postgres`. Orice referinta la `staging-dataLayer-postgres`, `misinformation_db` sau la path-ul arhiva `didiDatabase-legacy/` este stale si nu mai reflecta realitatea.

---

## Diagrama conexiuni

```
    +-----------------------------------+
    |  didi-postgres:5432               |
    |  Container LOCAL (postgres:17)     |
    |  didi-network                      |
    +-----------------------------------+
    |  Database: DIDI                    |
    +-----------------------------------+
    |  bos_analysis   (agent-v3)         |
    |  bos_parammgmt  (didiFramework)    |
    |  bos_sysadmin   (didiFramework)    |
    |  bos_subscriber (didiFramework)    |
    |  public         (Keycloak schema)  |
    +-----------------------------------+
          ^            ^            ^
          |            |            |
      agent-v3   didiFramework   Keycloak
                                (schema public)

    Kong ruleaza DBless (fara baza proprie).
```

---

## Fisiere in directorul didiDatabase

```
DIDI_full_export_2026-07-02.sql  -- SEED CANONIC (23 MB): pg_dump complet DIDI
                                    (schema + date + migratiile 001-017). Restorabil
                                    cu --clean --if-exists --no-owner.
Dockerfile                       -- Build imagine postgres cu init (pastrat pentru rebuild container)
MIGRATION.md                     -- Note migrare PostgreSQL (atentie: contine si sectiuni stale despre cluster)
REBUILD.md                       -- Reteta rebuild baza pe alt host din seed-ul canonic
ha-cluster/                      -- Config optional HA (docker-compose + haproxy.cfg) pentru fallback cluster
.gitignore                       -- Exclude .env, data/
INDEX.md                         -- Aceasta documentatie
```

Nota seed: fisierul canonic actual este `DIDI_full_export_2026-07-02.sql`. Seed-ul vechi `DIDI_full_export_2026-03-22.sql` (fara migratiile 016/017) si pachetul demo (`DIDI_demo_seed_2026-07-02.sql` + `demo-seed/`) au fost arhivate **in afara repo-ului** (`/home/admin365/didi_seed_archive_2026-07-08/`) — livrarea foloseste DOAR full seed-ul curent.

---

## Ce NU face containerul local `didi-postgres`

- Nu are replicare (instanta singulara); HA se obtine doar comutand pe fallback-ul cluster din `ha-cluster/`
- Nu are backup automat integrat (backup manual din seed / pg_dump)
- Nu are SSL/TLS intern
- Este sursa unica de adevar pentru datele DIDI; Redis (`didi:config:*`, `didi:framework:*`) e cache derivat, regenerat cu `sync-redis`

---

## Schema additions (2026-05-04 → 2026-05-05)

### `bos_parammgmt.sensitive_topic` — extins (migration 012)

ALTER ADD: `volatility ('volatile'|'evolving'|'stable')`, `cache_ttl_hours integer (1-26280)`, `recency_window_days integer (1-365)`, `half_life_days numeric (>0)`. Seed: war/elections=volatile@24h/7d/3d, health/covid=evolving@168h/14d/14d, climate=stable@720h/180d/180d, fraud_test=defaults. Used by brain `topic_volatility.py` to override classifier TTL per topic.

### `bos_sysadmin.user_audit_log` — nou (migration 013)

```
audit_id           bigserial PK
internet_user_id   integer (NULL pentru keycloak-only useri)
target_email       text
target_keycloak_id text
actor_keycloak_id  text     -- extras din JWT (sub claim)
actor_email        text
action             text NOT NULL  -- user.{update,delete,sync,email_verified,subscription,roles,group,reset_password}
payload            jsonb DEFAULT '{}'    -- diff before/after sau parametri operațiune
request_ip         text
user_agent         text
created_at         timestamptz NOT NULL DEFAULT now()
```

4 indexuri: user (partial), actor, action+time, time. Powers tab "Audit Log" în UserManagement DIDI admin.

### Brain tables (alongside Atomic, prefix `brain_*`, public schema)

| Tabela | Scop | Cheie unique |
|---|---|---|
| `brain_analysis_atom` (existed) | Cache rezultate full-component LLM (techniques/ai_tampered/claims). +7 coloane noi: `volatility`, `topic_codes text[]`, `entity_bindings jsonb`, `ttl_hours_used`, `last_audited_at`, `audit_history jsonb` (last-50 cap), `consecutive_audit_passes` | `(content_hash, component, prompt_hash)` |
| `brain_verification_cache` (existed) | Cache verdict LLM per claim. Same +7 coloane | `(claim_hash, tier)` |
| `brain_fact_status` (NOU, 2026-05-04) | Current truth pentru triplete `(subject, predicate, object)`. Coloane: `current_truth bool|NULL`, `current_version_id`, `current_confidence`, `last_verified_at`, `last_evidence_urls jsonb`, `volatility`, `topic_codes`, `next_check_at`, `check_interval_hours`, `moderator_locked bool`, `moderator_user_id`, `moderator_notes` | `canonical_form_hash` |
| `brain_fact_version` (NOU) | Temporal versioning. `truth_value bool`, `confidence`, `valid_from`, `valid_to (NULL=current)`, `source_atom_ids text[]`, `evidence_urls jsonb`, `llm_reasoning`, `created_by ('auto'|'moderator'|'breaking_news_watcher'|'auditor'|'extractor')`, `moderator_user_id`, `notes` | bigserial; FK fact_id → fact_status ON DELETE CASCADE |
| `brain_audit_log` (NOU) | Cache mutation log: judge decisions, mass invalidations, gold promotions, fact truth changes. Coloane: `action`, `target_table`, `target_id`, `actor`, `payload jsonb` | bigserial |

GIN index-uri pe `topic_codes` (pentru topic-scoped invalidate). Partial index pe `cache_tier IN ('gold','silver') AND volatility != 'stable'` pentru auditor sweep. Schema migrează idempotent la fiecare brain `db.connect()`.

### DB live counts (2026-05-05, pe `didi-postgres`)

```
internet_users: 21        | brain_fact_status: 4
user_credentials: 20      | brain_fact_version: 1 (Putin → TRUE locked smoke-admin)
subscriptions: 20         | brain_audit_log: ~10 (mostly fact_truth_changed + reset_password)
subscription_plans: 20    | user_audit_log: live (logged on every PUT/DELETE/role/group)
```