Schema Database¶

Documentazione dello schema database PostgreSQL del sistema SGV GE.CO.

Architettura Database¶

Il sistema utilizza 3 schema distribuiti su 2 database PostgreSQL:

Database	Schema	Scopo	Tabelle
`c_a662`	`own_c_a662`	Dati operativi del reparto	234
`c_a662`	`dwh_c_a662`	Data Warehouse / reporting	81
`generale`	`own_generale`	Configurazione globale multi-tenant	10

Multi-tenancy

Ogni reparto/comune ha un proprio database c_XXXX con lo stesso schema own_c_XXXX. Il database generale e condiviso tra tutti i reparti e contiene la configurazione centralizzata.

Convenzioni di Naming¶

Prefissi tabelle (schema `own_c_a662`)¶

Prefisso	Significato	Tabelle	Descrizione
`vv_`	Verbale/Dati operativi	111	Tabelle principali con dati transazionali: verbali, notifiche, pagamenti, ricorsi, anagrafiche
`vf_`	Valori fissi/Lookup	88	Tabelle di riferimento: comuni, nazioni, province, violazioni, tipologie
`vp_`	Parametri	31	Configurazione applicativa: parametri init, template, contatori, scheduler
`vs_`	Settings	2	Impostazioni sistema
`da_`	Database changelog	2	Metadati Liquibase per migrazioni

Prefissi tabelle (schema `dwh_c_a662`)¶

Prefisso	Significato	Tabelle	Descrizione
`dwh_d_`	Dimensioni	21	Tabelle dimensionali (tempo, agente, strada, violazione, ecc.)
`dwh_f_`	Fatti	7	Tabelle dei fatti (verbale, pagamento, postalizzazione, ricorso)
`dwh_z_`	Supporto	2	Tabelle di supporto ETL
`sa_`	Staging Area	51	Tabelle di staging per alimentazione DWH

Convenzioni colonne¶

id — Chiave primaria (integer, sequenza PostgreSQL)
fk_* — Foreign key (es: fk_vv_verbale riferisce a vv_verbale.id)
f_* — Flag/stato (character 1 o integer 0/1)
d_* — Data (timestamp, es: d_verbale, d_insert, d_annullo)
cod — Codice naturale usato come PK nelle tabelle lookup (vf_*)
descr — Descrizione testuale
d_insert — Data inserimento record (presente in quasi tutte le tabelle)
fk_vv_operatore_ins — Operatore che ha inserito il record (audit trail)

Schema Operativo (`own_c_a662`)¶

Tabelle Core — Ciclo di Vita Verbale¶

erDiagram
    vv_verbale ||--o{ vv_infrazione : "contiene"
    vv_verbale ||--o{ vv_verba_veicolo : "coinvolge"
    vv_verba_veicolo }o--|| vv_veicolo : "riferisce"
    vv_verbale ||--o{ vv_figura : "soggetti"
    vv_figura }o--|| vv_anagrafica : "persona"
    vv_figura }o--|| vv_residenza : "indirizzo"
    vv_figura ||--o{ vv_notifica : "notificata a"
    vv_infrazione ||--o{ vv_infra_notif : "collegamento"
    vv_infra_notif }o--|| vv_notifica : "notifica"
    vv_infrazione ||--o{ vv_infra_pag_cauz : "collegamento"
    vv_infra_pag_cauz }o--|| vv_pagamento : "pagamento"
    vv_infrazione ||--o{ vv_infra_rico : "collegamento"
    vv_infra_rico }o--|| vv_ricorso : "ricorso"
    vv_verbale }o--o| vv_preavviso : "da preavviso"
    vv_infrazione }o--|| vf_violazione : "articolo"

`vv_verbale` — Verbale (390K righe)¶

Tabella centrale del sistema. Ogni record rappresenta un verbale di contestazione.

Colonna	Tipo	Note
`id`	integer PK	Sequenza `VV_VERBALE_ID_SEQ`
`rg_codice_verbale`	varchar(20)	Codice univoco verbale (registro generale)
`nr_verbale`	varchar(15)	Numero progressivo verbale
`d_verbale`	timestamp	Data emissione verbale
`d_violazione`	timestamp	Data/ora della violazione
`fk_vf_comune`	integer FK	Comune dove e avvenuta la violazione
`fk_vv_strada`	integer FK	Strada/luogo della violazione
`fk_vf_tipo_verbale`	char(2) FK	Tipo verbale
`fk_vf_reparto_acc`	char(8) FK	Reparto accertatore
`fk_vv_operatore_v1..v4`	integer FK	Agenti verbalizzanti (1-4)
`f_stato`	char(1)	Stato verbale
`f_stato_pag`	char(1)	Stato pagamento
`f_stato_notifica`	char(1)	Stato notifica
`f_stato_ricorso`	char(1)	Stato ricorso
`f_stato_ruolo`	char(1)	Stato iscrizione a ruolo
`f_annul`	integer	Flag annullamento
`f_definizione`	integer	Flag definizione
`cod_avviso_principale`	char(18)	Codice avviso PagoPA principale
`cod_avviso_secondario`	char(18)	Codice avviso PagoPA secondario
`note`	varchar(2000)	Note libere
`d_insert`	timestamp	Data inserimento
`fk_vv_operatore_ins`	integer FK	Operatore inserimento

`vv_infrazione` — Infrazione (390K righe)¶

Singola violazione contestata all'interno di un verbale. Un verbale puo contenere piu infrazioni.

Colonna	Tipo	Note
`id`	integer PK
`fk_vv_verbale`	integer FK	Verbale di appartenenza
`fk_vf_violazione`	integer FK	Articolo di legge violato
`fk_vv_verba_veicolo`	integer FK	Veicolo coinvolto
`importo_scontato`	numeric	Importo con sconto 30% (5gg)
`importo_ridotto`	numeric	Importo ridotto (60gg)
`importo_pieno`	numeric	Importo intero
`importo_ruolo`	numeric	Importo per iscrizione a ruolo
`punti`	integer	Punti patente da decurtare
`fk_vf_autorita`	char FK	Autorita competente

`vv_figura` — Soggetto del Verbale (654K righe)¶

Collega un soggetto (anagrafica) a un verbale con un ruolo specifico (trasgressore, obbligato in solido, proprietario).

Colonna	Tipo	Note
`id`	integer PK
`fk_vv_verbale`	integer FK	Verbale
`fk_vv_verba_veicolo`	integer FK	Veicolo del verbale
`fk_vf_tipo_figura`	char FK	Tipo: trasgressore, obbligato, proprietario
`fk_vf_tipo_obbligato`	char FK	Tipo obbligato in solido
`fk_vv_residenza`	integer FK	Indirizzo di residenza
`cogn_den`	varchar	Cognome/Denominazione
`nome`	varchar	Nome
`cod_fiscale_p_iva`	varchar(16)	Codice fiscale o P.IVA

`vv_anagrafica` — Anagrafica Soggetti (152K righe)¶

Registry centralizzato delle persone fisiche e giuridiche.

Colonna	Tipo	Note
`id`	integer PK
`f_pers_gf`	char(1)	G=Giuridica, F=Fisica
`cognome_denominazione`	varchar(100)	Cognome o ragione sociale
`nome`	varchar(35)	Nome (solo persone fisiche)
`d_nascita`	timestamp	Data di nascita
`sesso`	char(1)	M/F
`cod_fiscale_p_iva`	varchar(16)	Codice fiscale o Partita IVA
`fk_vf_comune_nas`	integer FK	Comune di nascita
`fk_vf_nazione_nas`	char(3) FK	Nazione di nascita

`vv_notifica` — Notifica (290K righe)¶

Traccia l'intero ciclo di notifica: produzione, trasmissione, postalizzazione, esito, ricezione.

Colonna	Tipo	Note
`id`	integer PK
`fk_vv_figura`	integer FK	Soggetto destinatario
`fk_vv_residenza`	integer FK	Indirizzo di notifica
`fk_vf_mezzo_notifica`	char FK	Mezzo: posta, PEC, PND, mani proprie
`d_trasmissione`	timestamp	Data invio
`d_postalizzazione`	timestamp	Data postalizzazione
`nr_raccomandata`	varchar	Numero raccomandata
`d_esito`	timestamp	Data esito
`fk_vf_tipo_esito`	integer FK	Tipo esito (consegnato, irreperibile, ecc.)
`d_notifica`	timestamp	Data perfezionamento notifica
`id_notifica_pnd`	varchar	ID notifica su PND (Piattaforma Notifiche Digitali)
`iun_pnd`	varchar	IUN (Identificativo Univoco Notifica) PND

`vv_pagamento` — Pagamento (260K righe)¶

Registrazione dei pagamenti ricevuti (bollettino, POS, PagoPA, contanti).

Colonna	Tipo	Note
`id`	integer PK
`fk_vv_verbale`	integer FK	Verbale pagato
`fk_vv_notifica`	integer FK	Notifica di riferimento
`importo_versato`	numeric	Importo totale versato
`d_versamento`	timestamp	Data versamento
`fk_vf_tipo_pag`	char FK	Tipo: scontato, ridotto, pieno
`fk_vf_modal_pag`	char FK	Modalita: bollettino, POS, PagoPA
`parte_oblazione`	numeric	Quota sanzione
`parte_notifica`	numeric	Quota spese notifica
`parte_spese`	numeric	Quota spese accertamento
`d_accredito`	timestamp	Data accredito

`vv_ricorso` — Ricorso (3.3K righe)¶

Ricorsi presentati al Prefetto o al Giudice di Pace.

Colonna	Tipo	Note
`id`	integer PK
`fk_vv_controded`	integer FK	Controdeduzioni collegate
`d_ricorso`	timestamp	Data presentazione ricorso
`d_udienza`	timestamp	Data udienza (se GdP)
`tipo_rico`	char(1)	Tipo: P=Prefetto, G=Giudice di Pace
`fk_vf_prefettura`	integer FK	Prefettura competente
`fk_vf_giudice_pace`	integer FK	Giudice di Pace competente
`cogn_den_rico`	varchar	Cognome ricorrente
`cod_fiscale_p_iva_rico`	varchar(16)	CF ricorrente

`vv_veicolo` — Veicolo (284K righe)¶

Registry dei veicoli coinvolti nelle violazioni.

Colonna	Tipo	Note
`id`	integer PK
`targa`	varchar	Targa veicolo
`fk_vf_tipo_veicolo`	char FK	Tipo: auto, moto, camion, ecc.
`fk_vf_nazione`	char(3) FK	Nazione di immatricolazione
`fk_vf_fabbrica`	integer FK	Casa costruttrice
`modello`	varchar	Modello
`telaio`	varchar	Numero di telaio
`cilindrata`	integer	Cilindrata (cc)

Altre Tabelle Operative Rilevanti¶

Tabella	Righe	Descrizione
`vv_ottico`	2M	Documenti scansionati / immagini da autovelox
`vv_residenza`	224K	Indirizzi di residenza dei soggetti
`vv_preavviso`	274K	Preavvisi di violazione (sosta)
`vv_dett_ispettiva`	387K	Dettagli attivita ispettiva
`vv_dett_blocchetto`	344K	Dettagli blocchetti verbali
`vv_verba_veicolo`	384K	Associazione verbale-veicolo
`vv_punti`	17K	Decurtazione punti patente
`vv_lotto`	98K	Lotti di lavorazione (produzione, trasmissione, ricezione)
`vv_ispettiva`	111K	Attivita ispettive
`vv_blocchetto`	22K	Blocchetti verbali assegnati agli agenti
`vv_fermo`	0	Fermi amministrativi
`vv_sequestro`	0	Sequestri veicoli
`vv_operatore`	772	Operatori/agenti del sistema

Tabelle di Lookup (`vf_*`)¶

Le principali tabelle di riferimento:

Tabella	Righe	Descrizione
`vf_comune`	8.699	Tutti i comuni italiani
`vf_info_comune`	7.915	Informazioni aggiuntive comuni
`vf_violazione`	4.970	Articoli CdS e violazioni
`vf_importi_viol`	16.428	Importi per violazione (storicizzati)
`vf_punti_viol`	4.971	Punti patente per violazione
`vf_nazione`	244	Nazioni
`vf_provincia`	115	Province italiane
`vf_fabbrica`	883	Case costruttrici veicoli
`vf_giudice_pace`	475	Giudici di Pace
`vf_tipo_esito`	66	Esiti notifica
`vf_mezzo_notifica`	19	Mezzi di notifica
`vf_tipo_veicolo`	36	Tipi veicolo
`vf_tipo_verbale`	25	Tipi verbale

Tabelle Parametri (`vp_*`)¶

Tabella	Righe	Descrizione
`vp_par_init`	1	Parametri inizializzazione sistema (100 colonne)
`vp_template`	258	Template documenti (PDF, lettere, ecc.)
`vp_report`	50	Configurazione report
`vp_report_par`	93	Parametri dei report
`vp_contatore`	44	Contatori progressivi
`vp_task_scheduler`	37	Configurazione job schedulati
`vp_par_pagopa`	1	Parametri integrazione PagoPA
`vp_par_servizio_pnd`	1	Parametri integrazione PND

Data Warehouse (`dwh_c_a662`)¶

Lo schema DWH implementa un modello star schema per il reporting:

Tabelle Fatti¶

Tabella	Righe	Descrizione
`dwh_f_agente`	645K	Fatti per agente verbalizzante
`dwh_f_verbale`	370K	Fatti verbale
`dwh_f_postalizzazione`	276K	Fatti postalizzazione/notifica
`dwh_f_postalizzazione_vio`	266K	Fatti postalizzazione per violazione
`dwh_f_pagamento`	255K	Fatti pagamento
`dwh_f_ricorso`	-	Fatti ricorso
`dwh_f_violazione`	-	Fatti violazione

Tabelle Dimensioni¶

Tabella	Descrizione
`dwh_d_tempo`	Dimensione temporale
`dwh_d_agente1`, `dwh_d_agente2`	Agenti verbalizzanti
`dwh_d_strada`	Strade
`dwh_d_violazione`	Violazioni CdS
`dwh_d_tipo_verbale`	Tipi verbale
`dwh_d_tipo_esito`	Esiti notifica
`dwh_d_modalita_pagamento`	Modalita di pagamento
`dwh_d_contravventore`	Contravventori
`dwh_d_ricorso`, `dwh_d_ricorso_ente`	Ricorsi
`dwh_d_preavviso`	Preavvisi

Staging Area (`sa_*`)¶

51 tabelle di staging che replicano i dati operativi per l'alimentazione del DWH. Seguono la naming convention sa_ + nome tabella sorgente (es: sa_vv_verbale replica vv_verbale).

Schema Globale (`own_generale`)¶

Configurazione centralizzata condivisa tra tutti i reparti.

`vg_db` — Connessioni Database (7 righe)¶

Configurazione delle connessioni ai database dei reparti.

Colonna	Tipo	Descrizione
`cod`	char(8) PK	Codice database
`db_url`	varchar(100)	URL JDBC
`db_driver`	varchar(100)	Driver JDBC
`jndi_name`	varchar(100)	Nome JNDI datasource
`nome`	varchar(15)	Nome database
`ip`	varchar(40)	Indirizzo server
`usr` / `pwd`	varchar(20)	Credenziali

`vg_cliente` — Clienti/Reparti (7 righe)¶

Elenco dei reparti/comuni configurati nel sistema.

Colonna	Tipo	Descrizione
`cod`	char(8) PK	Codice cliente
`tipo`	char(2)	Tipo reparto
`descr`	varchar(40)	Descrizione
`descr_comune`	varchar(40)	Nome comune
`fk_vg_db`	char(8) FK	Database associato
`f_scheduler_attivo`	integer	Scheduler attivo (0/1)

`vg_par_geco` — Parametri GeCo (1 riga)¶

Configurazione globale dell'applicazione.

Colonna	Tipo	Descrizione
`nome`	varchar(50)	Nome applicazione
`descr`	varchar(200)	Descrizione
`tema_css`	varchar(50)	Tema CSS
`logo`	bytea	Logo applicazione
`keycloak_base_url`	varchar(255)	URL Keycloak SSO
`keycloak_realm`	varchar(100)	Realm Keycloak
`keycloak_client_id`	varchar(100)	Client ID Keycloak

Altre Tabelle¶

Tabella	Righe	Descrizione
`vg_par_mail`	1	Configurazione server email
`vg_operatore_gs`	1	Operatori sistema globale
`vg_scelta_gs`	49	Menu/scelte sistema globale
`vg_tablet`	1	Tablet registrati
`vg_mail_mezzo_trasm`	-	Email per mezzo trasmissione
`databasechangelog`	-	Log migrazioni Liquibase
`databasechangeloglock`	1	Lock migrazioni Liquibase

Relazioni Principali (FK)¶

Il diagramma seguente mostra le relazioni tra le tabelle core:

graph TD
    VV[vv_verbale] --> VF_COM[vf_comune]
    VV --> VV_STR[vv_strada]
    VV --> VV_OP[vv_operatore]
    VV --> VP_PAR[vp_par_init]
    VV --> VV_PRE[vv_preavviso]

    INF[vv_infrazione] --> VV
    INF --> VF_VIO[vf_violazione]
    INF --> VV_VV[vv_verba_veicolo]

    VV_VV --> VV_VEI[vv_veicolo]

    FIG[vv_figura] --> VV
    FIG --> VV_RES[vv_residenza]
    FIG --> VV_ANA[vv_anagrafica]

    NOT[vv_notifica] --> FIG
    NOT --> VV_RES
    NOT --> VF_ME[vf_mezzo_notifica]
    NOT --> VV_LOT[vv_lotto]

    PAG[vv_pagamento] --> VV
    PAG --> NOT

    RICO[vv_ricorso] --> VV_CON[vv_controded]

    IN_NOT[vv_infra_notif] --> INF
    IN_NOT --> NOT

    IN_PAG[vv_infra_pag_cauz] --> INF
    IN_PAG --> PAG

    IN_RICO[vv_infra_rico] --> INF
    IN_RICO --> RICO

Sequenze¶

Le chiavi primarie delle tabelle vv_* usano sequenze PostgreSQL con naming convention:

VV_VERBALE_ID_SEQ
VV_NOTIFICA_ID_SEQ
VV_PAGAMENTO_ID_SEQ
...

Pattern: <NOME_TABELLA_UPPER>_ID_SEQ

JPA Mapping¶

Le entita JPA sono definite in MLib (com.mdatasystem.entities.*):

Persistence unit: GeCo.All
Provider: EclipseLink 2.5.1
Configurazione: MLib/src/main/resources/META-INF/persistence.xml

persistence.xml manuale

Le entita sono elencate esplicitamente in persistence.xml (150+ entity class). Quando si aggiunge una nuova entita JPA, deve essere registrata manualmente in questo file.

Note sulla Sicurezza¶

Dati Sensibili (GDPR)

Le tabelle vv_anagrafica, vv_residenza, vv_figura contengono dati personali soggetti a GDPR. L'accesso deve essere loggato e limitato ai soli operatori autorizzati. Non esporre mai questi dati in log o messaggi di errore.

Schema Database¶

Architettura Database¶

Convenzioni di Naming¶

Prefissi tabelle (schema own_c_a662)¶

Prefissi tabelle (schema dwh_c_a662)¶

Convenzioni colonne¶

Schema Operativo (own_c_a662)¶

Tabelle Core — Ciclo di Vita Verbale¶

vv_verbale — Verbale (390K righe)¶

vv_infrazione — Infrazione (390K righe)¶

vv_figura — Soggetto del Verbale (654K righe)¶

vv_anagrafica — Anagrafica Soggetti (152K righe)¶

vv_notifica — Notifica (290K righe)¶

vv_pagamento — Pagamento (260K righe)¶

vv_ricorso — Ricorso (3.3K righe)¶

vv_veicolo — Veicolo (284K righe)¶