Legal database
The specialized schema and validity checks exist. Full legacy import and several derived-data producers are incomplete.
Legal PostgreSQL owns canonical legal data. The schema retains pgvector embeddings as rebuild artifacts; the API uses Qdrant as a derived candidate index, with only vectors, identity references and necessary filters. Storage & indexes owns the payload allowlist, examples and synchronization contract. Corpus originals may live in Blob storage with verified references. The views below describe the SQL domain model, split so the keys, cardinalities and important fields remain legible. Table names are plural snake_case; tables live in the legal, search and ops schemas. Repeated entities are the same tables, not copies.
Resources and legal text
Section titled “Resources and legal text”resources is the canonical identity. Active or withdrawn resources have exactly one matching subtype; erased resources retain only a tombstone. normative_versions applies to acts and articles, while text_snapshots records acquired text. A parser correction can add a snapshot without changing legal validity.
erDiagram
direction LR
resources ||--o{ source_mappings : identifies
resources ||--o| judgments : specializes
resources ||--o| acts : specializes
resources ||--o| articles : specializes
acts ||--o{ articles : contains
resources ||--o{ normative_versions : has_validity
resources ||--o{ text_snapshots : retains
normative_versions o|--o{ text_snapshots : materializes
text_snapshots ||--o{ text_sections : structures
resources ||--o{ citations : originates
resources o|--o{ citations : resolves_target
resources {
uuid id PK
text kind "judgment, act, article"
text status "active, withdrawn, erased"
}
source_mappings {
uuid id PK
uuid resource_id FK
text source_system
text source_table
text source_id
text source_url
}
judgments {
uuid resource_id PK, FK
text court
text jurisdiction
text grade
text number
date decision_date
text outcome
}
acts {
uuid resource_id PK, FK
text act_type
text number
int year
text issuing_authority
date publication_date
}
articles {
uuid resource_id PK, FK
uuid act_id FK
text article_number
text internal_document
text structural_path
}
normative_versions {
uuid id PK
uuid resource_id FK "Acts or articles only"
daterange validity "Half-open, nullable if unknown"
text validity_coverage "known, open_ended, unknown"
text source_reference
}
text_snapshots {
uuid id PK
uuid resource_id FK
uuid normative_version_id FK "Nullable for judgments"
text title
text content "Null after purge"
text content_hash
text content_status "retained, purged"
text access_status "available, blocked"
bool is_published
text original_blob_key
text original_sha256
datetime acquired_at
}
text_sections {
uuid id PK
uuid text_snapshot_id FK
text kind
int ordinal
int start_offset
int end_offset
text structural_locator
}
citations {
uuid id PK
uuid source_resource_id FK
uuid target_resource_id FK "Nullable until resolved"
text target_kind
text target_source_id
text relation_kind
text raw_reference
text resolution_status
float confidence
}
Search representations and execution
Section titled “Search representations and execution”The ER below retains both global and chunk embeddings. Its vector columns describe retained SQL artifacts, not duplicate runtime search authorities. SQL retains ownership, recipe input and publication manifests; the API serves global semantic/hybrid candidates from Qdrant. Neither summaries nor full text move into Qdrant payloads. The additional operational tables search.qdrant_builds and search.projection_clock are documented with the publication mechanism; chunks remain in the model and are not moved by this implementation.
global_embeddings serves judgment-summary, article-text and act-profile discovery. Passage vectors are chunks within a snapshot generation. Both use immutable embedding_profiles; a configuration chooses profiles for legal text, summaries, classification and private documents. Jobs and attempts record execution separately from source identity.
erDiagram
direction LR
resources ||--o{ global_embeddings : represents
judgment_enrichments o|--o{ global_embeddings : summary_source
text_snapshots o|--o{ global_embeddings : text_source
embedding_profiles ||--o{ global_embeddings : embeds
text_snapshots ||--o{ index_generations : indexes
embedding_profiles ||--o{ index_generations : configures
index_generations ||--o{ chunks : contains
embedding_profiles ||--o{ configurations : selects
configurations ||--o| configuration_defaults : defaults
jobs o|--o{ index_generations : builds
jobs ||--o{ job_attempts : attempts
resources {
uuid id PK
}
judgment_enrichments {
uuid id PK
uuid judgment_id FK
}
text_snapshots {
uuid id PK
uuid resource_id FK
}
embedding_profiles {
uuid id PK
text provider
text model
int dimensions
text storage_kind "vector, halfvec"
text distance
text recipe_version
}
global_embeddings {
uuid id PK
uuid resource_id FK
uuid judgment_enrichment_id FK "Summary only"
uuid text_snapshot_id FK "Article or act only"
uuid embedding_profile_id FK
text representation_kind
text input_hash
text input_text
text status "building, ready, failed"
bool is_published
bool is_selected
vector embedding
}
configurations {
uuid id PK
uuid legal_text_profile_id FK
uuid summary_profile_id FK
uuid classification_profile_id FK
uuid private_document_profile_id FK
uuid prototype_set_id FK
text fingerprint
text status "building, supported, retired"
}
configuration_defaults {
bool singleton PK
uuid configuration_id FK
}
index_generations {
uuid id PK
uuid text_snapshot_id FK
uuid embedding_profile_id FK
uuid job_id FK
text representation_kind
text pipeline_version
text status "building, ready, failed"
bool is_selected
int expected_chunk_count
}
chunks {
uuid id PK
uuid index_generation_id FK
uuid text_snapshot_id FK
uuid resource_id FK
uuid embedding_profile_id FK
int ordinal
text content_hash
text structural_locator
bool is_published
vector embedding
}
jobs {
uuid id PK
text kind
text status
text idempotency_key UK
int fencing_token
bool is_cancel_requested
}
job_attempts {
uuid id PK
uuid job_id FK
int attempt_no
text status
text worker_id
datetime started_at
datetime finished_at
}
The profile-to-configuration edge represents four named foreign keys, not a single shared profile. chunks also carries a composite generation/snapshot/resource foreign key and copies filter fields such as validity and court. The source and generation remain the authority for those values.
Judgment participants and enrichments
Section titled “Judgment participants and enrichments”persons stores resolved identity; a participation stores how someone appears in one judgment. Roles can be multiple, and representation links a lawyer’s participation to a party’s participation in the same judgment. Enrichments are derived records, separate from judicial text.
erDiagram
direction LR
judgments ||--o{ judgment_participations : involves
persons o|--o{ judgment_participations : identifies
judgment_participations ||--o{ participation_roles : holds
judgment_participations ||--o{ party_representations : lawyer
judgment_participations ||--o{ party_representations : party
judgments ||--o{ judgment_enrichments : enriches
judgments {
uuid resource_id PK, FK
}
persons {
uuid id PK
text kind "natural, legal, unknown"
text normalized_name
text resolution_method
}
judgment_participations {
uuid id PK
uuid judgment_id FK
uuid person_id FK "Nullable if unresolved"
text participation_kind "judge, lawyer, party"
text observed_name
text observed_address
text resolution_method
}
participation_roles {
uuid judgment_participation_id PK, FK
text role_code PK
text raw_role
text role_mapping_version
}
party_representations {
uuid lawyer_judgment_participation_id PK, FK
uuid party_judgment_participation_id PK, FK
uuid judgment_id FK
}
judgment_enrichments {
uuid id PK
uuid judgment_id FK
text subject
text description
text outcome_description
text extracted_outcome
decimal awarded_amount
decimal claimed_amount
decimal legal_costs
text currency
bool is_selected
bool is_accessible
text extraction_model
text pipeline_version
}
Judgment classification
Section titled “Judgment classification”Taxonomies and categories define labels; prototype sets version the vectors used to classify them. Assignments belong only to judgments and can retain snapshot, embedding, profile and prototype provenance.
erDiagram
direction LR
taxonomies ||--o{ categories : defines
categories o|--o{ categories : parents
taxonomies ||--o{ category_prototype_sets : versions
embedding_profiles ||--o{ category_prototype_sets : configures
category_prototype_sets ||--o{ category_prototypes : contains
categories ||--o{ category_prototypes : represents
judgments ||--o{ judgment_classifications : receives
categories ||--o{ judgment_classifications : labels
text_snapshots o|--o{ judgment_classifications : source
global_embeddings o|--o{ judgment_classifications : input
category_prototype_sets o|--o{ judgment_classifications : provenance
judgments {
uuid resource_id PK, FK
}
text_snapshots {
uuid id PK
}
global_embeddings {
uuid id PK
uuid embedding_profile_id FK
}
embedding_profiles {
uuid id PK
int dimensions
}
taxonomies {
uuid id PK
text name
text version
}
categories {
uuid id PK
uuid taxonomy_id FK
uuid parent_category_id FK "Nullable"
text external_key
text level
text label
}
category_prototype_sets {
uuid id PK
uuid taxonomy_id FK
uuid embedding_profile_id FK
text version
text status "building, ready, failed"
int expected_prototype_count
}
category_prototypes {
uuid id PK
uuid category_id FK
uuid prototype_set_id FK
uuid embedding_profile_id FK
text input_hash
text representation_kind
vector embedding
}
judgment_classifications {
uuid id PK
uuid judgment_id FK
uuid category_id FK
uuid text_snapshot_id FK "Nullable"
uuid prototype_set_id FK "Nullable"
uuid embedding_profile_id FK "Nullable"
uuid global_embedding_id FK "Nullable"
text assignment_role
float score
text classifier_version
}
Removal records
Section titled “Removal records”The schema also records logical denial and cleanup work. A removal intent belongs to a resource, its ledger row persists the decision, and blob items track external deletion separately.
erDiagram
resources ||--o{ data_removal_intents : requests
text_snapshots o|--o{ data_removal_intents : replacement
jobs o|--o{ data_removal_intents : executes
data_removal_intents ||--o| erasure_ledger : records
data_removal_intents ||--o{ blob_removal_items : cleans
resources {
uuid id PK
}
text_snapshots {
uuid id PK
}
jobs {
uuid id PK
}
data_removal_intents {
uuid id PK
uuid resource_id FK
uuid replacement_text_snapshot_id FK
uuid job_id FK
text kind "redaction, erasure"
text status "blocked, cleaning, completed"
datetime blocked_at
datetime active_store_cleaned_at
datetime backup_expires_at
}
erasure_ledger {
uuid id PK
uuid data_removal_intent_id FK, UK
uuid resource_id FK
text kind
datetime blocked_at
}
blob_removal_items {
uuid id PK
uuid data_removal_intent_id FK
text blob_key
datetime deleted_at
}
Resource model
Section titled “Resource model”Every resource has exactly one matching subtype in this scope: judgment, act or article. Source mappings preserve external identities and acquisition provenance without confusing them with canonical identity.
Judgments retain court, procedure, dates and identifiers. Acts retain issuing authority, type and publication identity. Articles belong to an act and preserve their textual number, internal document and structural path; article number alone may not uniquely identify an annex provision.
Full text lives once in text_snapshots. Optional text_sections describe real locatable structure. Unstructured sources have zero section rows, not a synthetic duplicate of the full text. Indexing covers the full snapshot independently of section availability.
global_embeddings holds judgment-summary, article-text and act-profile discovery vectors. Passage vectors live in chunks under index_generations. Both use immutable embedding_profiles; configurations select compatible profiles and configuration_defaults selects the current default. The diagram shows these search relationships without listing every foreign key.
Three kinds of version
Section titled “Three kinds of version”| Change | New object |
|---|---|
| Amendment or change in legally applicable text | Normative version and its first text snapshot |
| Parser correction or reacquisition of the same legal text | Text snapshot under the same normative version |
| Model, chunking or embedding recipe change | Derived generation for the same snapshot |
Never invent historical text or infer legal validity from acquisition timestamps. Normative versions belong only to acts/articles; judgments have their own decision dates.
Known validity uses half-open date intervals without overlaps for the same resource. Unknown validity stays null and explicit. current uses today’s date in Europe/Rome; asOf(date) selects the requested interval; all exposes versions with their validity labels. Unknown intervals cannot silently qualify as current law.
Citations
Section titled “Citations”Citations connect resources and retain raw reference, relation kind, source locator, confidence and resolution state. Unresolved or out-of-subset targets remain representable. A citation relationship is not evidence that a proposition is supported.
Judgments & classification explains participants and derived analysis. Storage & indexes covers publication and Search configurations covers model selection.
Enforcement and scope
Section titled “Enforcement and scope”services/legal-core-api/schema/target.sql defines subtype/ownership guards and normative validity constraints; target_test.sql exercises invalid owners, validity intervals and representation publication. Keeping technical snapshots distinct from legal versions prevents a parser correction from being presented as a change in law. Ingestion of all legacy metadata, automatic citation resolution and classification generation remain partial or unimplemented even where tables exist.