Skip to content

Legal database

Partial

The specialized schema and validity checks exist. Full legacy import and several derived-data producers are incomplete.

Code and tests

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 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
  }

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.

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
  }

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
  }

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
  }

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.

ChangeNew object
Amendment or change in legally applicable textNormative version and its first text snapshot
Parser correction or reacquisition of the same legal textText snapshot under the same normative version
Model, chunking or embedding recipe changeDerived 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 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.

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.