Schema Delta v5.15 -> v1.0

Documento di sintesi delle modifiche applicate al file akira_schema.sql (v5.14/v5.15) per produrre akira_schema_v1.sql (v1.0 freeze pre-development).

Riferimento docx: Akira_System_Architettura_v5_16.docx (in produzione).

A. Estensioni PostgreSQL

Estensione	Stato	Motivazione
`btree_gist`	NUOVA	Richiesta per gli `EXCLUDE` constraint temporali su `tariff_rates`, `destination_prefixes`, `offers`.

Tutte le altre estensioni (timescaledb, pg_trgm, pgcrypto, citext, btree_gin) restano invariate.

B. Tabelle nuove (26)

B.1 Rating / Rebill (cap. 15)

Tabella	Scopo
`rebill_tasks`	Coda task rebill con dry_run -> applied -> rolled_back.
`cdr_rating_history`	Audit trail rebill: delta (cost, revenue) per CDR + link task.

B.2 Quality engine (cap. 27)

Tabella	Scopo
`quality_policies`	Policy QoS con scope opzionale originator/destination/terminator.
`quality_policy_thresholds`	Soglie multi-metrica (asr/acd/pdd/fas, comparator, window).
`quality_policy_notifications`	Canali (email/telegram/webhook) per la policy.
`quality_alerts`	Alert generati (status open/resolved/muted).

B.3 Agents / Commissioni (cap. 30)

Tabella	Scopo
`agents`	Anagrafica agent/reseller con fee_model (per_minute / percent_profit).
`agent_fee_rules`	Regole fee versionate (valid_from/to), opzionalmente per destination.
`agent_fee_cdr_attribution`	Attribuzione fee per CDR con snapshot regola (replay safety).

B.4 Mail ingestion + rate templates (cap. 16.11 / 49.2)

Tabella	Scopo
`mailboxes`	Caselle IMAP centralizzate (role: rates/tickets/billing, auth: basic/oauth2).
`rate_templates`	Template parsing rate sheet (classic / multiprefixes).
`template_mail_associations`	Bind template <-> mailbox con match regex + priority.
`template_versions`	Storia versioni mapping_config (rollback safety).
`mail_processing_log`	Log per-mail con result + link a future_tariff.
`imap_inbound_configs`	Config inbound ticket (1:1 mailbox, cap. 49.2.4).

B.5 Currencies (cap. 17)

Tabella	Scopo
`currencies`	Anagrafica valute ISO 4217.
`currency_exchange_rates`	Tassi versionati (source: ECB / manual / api_fallback).

B.6 Routing / Access (cap. 18.7 / 19.5 / 20.7)

Tabella	Scopo
`failover_policies`	Politiche failover (rerouteable/final SIP causes, PDD threshold, max attempts).
`access_policies`	Ex "Route" rinominato: allow/block/surcharge su destination_group.
`destination_groups`	Gruppi destinazioni (tag_based / manual).
`destination_group_members`	Membership esplicita destination -> group.
`access_policy_authorized_originators`	N:M originator <-> access_policy.
`blocked_calls`	Hypertable Timescale (chunk 1d, retention 90gg) audit trail blocchi.
`source_destination_routing_rules`	Regole cross-cutting source/destination (cap. 19.5).

B.7 Finance ausiliaria (cap. 37.4)

Tabella	Scopo
`expense_categories`	Gerarchia categorie spese (self-FK). Linked da `expenses.category_id`.

B.8 Sicurezza / RBAC / MFA (cap. 5.2 / 8.2)

Tabella	Scopo
`secrets_vault`	Store cifrato centralizzato (ref opaca, AES-256-GCM).
`recovery_codes`	MFA backup codes (hash Argon2id).
`role_permissions`	RBAC: mapping role -> permission_key.
`user_extra_permissions`	Override per-utente (grant o deny espliciti).

B.9 Settings KV (cap. 39)

Tabella	Scopo
`settings_kv_history`	Audit trail variazioni `settings_kv` (popolato via trigger).

Inoltre settings_kv ora ha PK composta (key, environment) (vedi sez. G).

C. EXCLUDE constraint temporali (cap. 13 / 14 / 27)

Tutti basati su btree_gist + tstzrange(valid_from, COALESCE(valid_to,'infinity'), '[)'):

Tabella	Constraint	Semantica
`tariff_rates`	`excl_tariff_rates_temporal`	No overlap per `(tariff_id, destination_id)`.
`destination_prefixes`	`excl_destination_prefixes_temporal`	No overlap per `prefix`.
`offers`	`excl_offers_temporal`	No overlap per `tariff_id` (solo `is_active=TRUE`).

Eliminano la classe di bug "due rate sovrapposte sulla stessa destinazione".

D. Indici nuovi (12)

CDR hot paths

Indice	Definizione
`idx_cdr_orig_dest_ts`	`(originator_id, destination_id, started_at DESC)`
`idx_cdr_term_dest_ts`	`(terminator_id, destination_id, started_at DESC)`
`idx_cdr_src_ts`	`(src, started_at DESC) WHERE src IS NOT NULL`
`idx_cdr_live`	`(started_at DESC) WHERE ended_at IS NULL`
`idx_cdr_term_disp_ts`	`(terminator_id, disposition, started_at DESC)`
`idx_cdr_pdd`	`(pdd_ms) WHERE pdd_ms IS NOT NULL` (sez. J)

Finance / Operations

Indice	Definizione
`idx_cust_inv_collection`	`(status, due_at) WHERE status IN ('sent','overdue')`
`idx_supp_inv_pending`	`(status, issued_at DESC) WHERE status IN ('draft_received','validated','rate_check')`
`idx_tariff_rates_dest`	`(destination_id, valid_from DESC)`
`idx_balance_type_ts`	`(type, ts DESC)`
`idx_fraud_value`	`(value) WHERE active = TRUE`
`idx_audit_actor`	`(actor, ts DESC) WHERE actor IS NOT NULL`
`idx_tg_log_chat_ts`	`(chat_id, ts DESC)`

E. Fix audit / CHECK / trigger

E.1 Audit trigger array (sez. 11)

Aggiunte tabelle al DO block:

tariff_rates (mancante in v5.15)
nuove tabelle critiche: access_policies, destination_groups, failover_policies, quality_policies, agents, agent_fee_rules, mailboxes, rate_templates, rebill_tasks, currencies, currency_exchange_rates.

E.2 `updated_at` trigger array

Aggiunti: offers, tariff_rates, e164_country_codes, quality_profiles, agents, oltre alle nuove tabelle con campo updated_at (vedi sez. B).

E.3 CHECK currency uppercase (`~ '^[A-Z]{3}$'`)

Applicato su: tariffs, customer_invoices, supplier_invoices, payments, bank_connections, bank_transactions, balance_movements, expenses, cdr, currencies.

E.4 `devices` CHECK

Aggiunto: CHECK (associated_originator_id IS NOT NULL OR associated_terminator_id IS NOT NULL). Un device deve essere associato ad almeno una entità logica.

E.5 `routing_rules` UNIQUE

UNIQUE (routing_plan_id, originator_id, destination_id) per impedire duplicati.

E.6 `cdr.currency` NOT NULL

Cambiato da DEFAULT 'EUR' a NOT NULL DEFAULT 'EUR' + check uppercase.

E.7 Trigger `settings_kv_history`

Nuovo trigger fn_settings_kv_history su INSERT/UPDATE/DELETE -> popola settings_kv_history.

F. Ruoli

Ruolo	Stato	Permessi
`akira_app`	invariato	RW su tutto
`akira_readonly`	invariato	SELECT su tutto
`akira_kamailio`	esteso (v1.0)	aggiunto SELECT su `failover_policies`, `access_policies`, `access_policy_authorized_originators`, `destination_groups`, `destination_group_members`, `source_destination_routing_rules`
`akira_agentcore`	NUOVO	SELECT read-only su `cdr`, continuous aggregates, `destinations`, `destination_prefixes`, `companies`, `originators`, `terminators`, `fraud_blocklist`, `quality_alerts`, `quality_policies`, `mv_report_pivot_monthly`. NO write.

G. Settings KV con environment scoping

Prima (v5.15):

CREATE TABLE settings_kv (key VARCHAR(128) PRIMARY KEY, value JSONB, ...);

Ora (v1.0):

CREATE TABLE settings_kv (
    key         VARCHAR(128) NOT NULL,
    environment VARCHAR(16)  NOT NULL DEFAULT 'all',     -- 'all','dev','staging','prod'
    value       JSONB NOT NULL,
    ...
    PRIMARY KEY (key, environment)
);

Aggiunto settings_kv_history per audit trail (popolata da trigger fn_settings_kv_history).

H. Nightly CDR integrity check

Nuova materialized view mv_cdr_integrity_check:

conta CDR orfani per originator_id, terminator_id, destination_id
WITH NO DATA iniziale
index su (dim, orphan_cdrs DESC)
istruzione operativa nei commenti: REFRESH nightly + alert se SUM(orphan_cdrs) > 0

Necessaria perche le FK CDR sono soft (no enforcement DB per ragioni di performance hypertable).

I. SIP credentials storage (cap. 12.5)

Operazione	Dettaglio
RIMOSSO	`devices.sip_password_encrypted` (non presente in v5.15, ma DROP IF EXISTS conservativo nel docx). Nel file v1 la colonna semplicemente non esiste.
AGGIUNTO	`devices.sip_ha1_encrypted BYTEA` — store di `MD5(username:realm:password)`.
AGGIUNTO	`devices.sip_realm VARCHAR(128) DEFAULT 'akira.asheep.it'`.

Il flag operativo: utilizzare secrets_vault.ref come container long-term; sip_ha1_encrypted e cache locale per il challenge digest senza round-trip al vault.

J. PDD nel CDR (anticipato a MVP)

Aggiunta colonna cdr.pdd_ms INT (Post-Dial Delay in millisecondi).
Aggiunto indice parziale idx_cdr_pdd ON cdr (pdd_ms) WHERE pdd_ms IS NOT NULL.

Anticipata da fase 2 a MVP perche le quality policies (cap. 27) e failover_policies (cap. 18.7) dipendono dalla disponibilita di PDD per metric/threshold check.

Riepilogo numerico

Voce	v5.15	v1.0	Delta
Tabelle (`CREATE TABLE`)	~40	~66	+26
Hypertable Timescale	1	2	+1 (`blocked_calls`)
Indici (`CREATE INDEX`)	~55	~95	+~40 (12 esplicitamente richiesti + indici delle 26 nuove tabelle)
EXCLUDE constraint	0	3	+3
ENUM types	25	39	+14
Ruoli	3	4	+1 (`akira_agentcore`)
Materialized views	4	5	+1 (`mv_cdr_integrity_check`)
Trigger functions	4	5	+1 (`fn_settings_kv_history`)

Note di deployment

Lo schema e ancora idempotente: BEGIN; ... COMMIT;, CREATE IF NOT EXISTS, DO $$ EXCEPTION WHEN duplicate_object ovunque.
EXCLUDE constraints falliscono se ci sono dati esistenti sovrapposti — eseguire prima query di verifica:
```
SELECT tariff_id, destination_id, COUNT(*)
FROM tariff_rates
GROUP BY 1,2 HAVING COUNT(*) > 1;
```
Migrazione settings_kv: se la tabella esiste con vecchia PK su key, serve ALTER TABLE settings_kv DROP CONSTRAINT settings_kv_pkey, ADD PRIMARY KEY (key, environment) manuale prima del nuovo CREATE IF NOT EXISTS (che e no-op).
PDD backfill: i CDR storici avranno pdd_ms = NULL finche Kamailio non viene aggiornato per popolarlo (richiede patch dialog + B2B-UA).
mv_cdr_integrity_check: refresh notturno via cron + Telegram alert se orfani > 0.

A. Estensioni PostgreSQL​

B. Tabelle nuove (26)​

B.1 Rating / Rebill (cap. 15)​

B.2 Quality engine (cap. 27)​

B.3 Agents / Commissioni (cap. 30)​

B.4 Mail ingestion + rate templates (cap. 16.11 / 49.2)​

B.5 Currencies (cap. 17)​

B.6 Routing / Access (cap. 18.7 / 19.5 / 20.7)​

B.7 Finance ausiliaria (cap. 37.4)​

B.8 Sicurezza / RBAC / MFA (cap. 5.2 / 8.2)​

B.9 Settings KV (cap. 39)​

C. EXCLUDE constraint temporali (cap. 13 / 14 / 27)​

D. Indici nuovi (12)​

CDR hot paths​

Finance / Operations​

E. Fix audit / CHECK / trigger​

E.1 Audit trigger array (sez. 11)​

E.2 updated_at trigger array​

E.3 CHECK currency uppercase (~ '^[A-Z]{3}$')​

E.4 devices CHECK​

E.5 routing_rules UNIQUE​

E.6 cdr.currency NOT NULL​

E.7 Trigger settings_kv_history​

F. Ruoli​

G. Settings KV con environment scoping​

H. Nightly CDR integrity check​

I. SIP credentials storage (cap. 12.5)​

J. PDD nel CDR (anticipato a MVP)​

Riepilogo numerico​

Note di deployment​