Single View in the Public Sector: Designing an Entity-Centric Integration Architecture with MongoDB

Every institution accumulates silos. It is not a design choice. It is history sedimented into systems.

Mainframe for core processes. Oracle for public management. SQL Server for consolidation. CRM in a different stack. Files exchanged nightly. APIs added later. Each system optimized for its own workflow, not for the citizen.

The result is fragmentation. The same person exists in multiple representations. Different identifiers. Different lifecycle states. Different semantics. The institution knows everything. But nowhere does it see everything at once.

This is where the Single View becomes an architectural pattern, not a feature.

The Pattern: Aggregation Layer, Not Replacement

A Single View is not a data warehouse. It is not a cache. It is not a reporting mart.

It is an operational aggregation layer that centralizes data from heterogeneous sources into a user-centric, hierarchical model, decoupled from legacy schemas and optimized for modern services    .

The core idea is simple: move from process-centric storage to entity-centric representation.

In legacy systems, data is organized by procedure: payments table, eligibility table, sanctions table. To reconstruct a Pensioner, you join.

In a Single View, you model the Pensioner directly.

---

Example: Legacy Landscape

Typical pension domain:

• DB2 mainframe for private pension management
• Oracle for public pension management
• SQL Server consolidating synthetic payment data
• Web services exposing fragments
• Batch files exchanged nightly

Each system holds partial truth. Reconstructing the full entity requires cross-system joins, often at runtime, often under load.

As your blueprint highlights, this creates lock contention, performance degradation, and rigid change management  .

---

Modeling the Single View in the Public Sector with MongoDB

Instead of flattening everything into a pre-agreed “common relational model,” we represent the entity as a document.

Pensioner Single View (initial scope: payments)

{
  "_id": "CF:RSSMRA80A01H501Z",
  "entityType": "Pensioner",
  "anagraphics": {
    "firstName": "Mario",
    "lastName": "Rossi",
    "dateOfBirth": "1980-01-01",
    "residence": {
      "city": "Roma",
      "province": "RM"
    }
  },
  "pensions": [
    {
      "pensionId": "PN-12345",
      "type": "OldAge",
      "management": "Private",
      "sourceSystem": "DB2-GPP",
      "status": "Active"
    }
  ],
  "payments": [
    {
      "paymentId": "PAY-2025-01",
      "competenceMonth": "2025-01",
      "grossAmount": 1800.50,
      "netAmount": 1650.30,
      "items": [
        { "code": "BASE", "amount": 1500.00 },
        { "code": "TAX", "amount": -149.70 }
      ],
      "sourceSystem": "SQLServer-Payments",
      "ingestionTimestamp": "2025-01-20T10:21:00Z"
    }
  ],
  "metadata": {
    "sources": [
      { "system": "DB2-GPP", "lastSync": "2025-01-20T09:00:00Z" },
      { "system": "Oracle-SIN", "lastSync": "2025-01-20T09:05:00Z" }
    ],
    "version": 3
  }
}

This is not denormalization for convenience. It is a deliberate shift to a hierarchical, JSON-native model aligned with how the entity is consumed  .

When new services arrive, we extend the document. We do not redesign the database.

{
  "communications": [
    {
      "channel": "AppIO",
      "lastNotification": "2025-01-20T11:00:00Z",
      "preferences": {
        "language": "it",
        "format": "digital"
      }
    }
  ]
}

No downtime. No global schema migration. Controlled evolution.

---

Integration Architecture: Feeding the Single View

The key question is not modeling. It is ingestion.

Your technical blueprint already defines the integration axes: ETL, CDC, Data Stream    .

We formalize them into a layered architecture.

1. Batch ETL (Initial Load / Reference Data)

Used for:

• Historical backfill
• Periodic low-frequency datasets
• Reference tables

Flow:

Legacy DB → ETL (DataStage, SSIS, Spark) → Transformation → MongoDB bulk insert

Ordered bulk writes are recommended to preserve consistency and performance, especially for large initial migrations.

This is appropriate for first-time population of the Single View cluster.

---

2. CDC + Streaming (Near Real-Time)

For operational freshness, batch is not enough.

We introduce:

• CDC from DB2 / Oracle / SQL Server
• Kafka (or equivalent) as streaming backbone
• Transformation layer enriching domain object
• Upsert into MongoDB Single View

Flow:

Source DB → CDC → Kafka → Domain Transformer → MongoDB upsert

Each change event updates the relevant fragment of the entity document.

Example upsert pattern:

db.pensioners.updateOne(
  { _id: "CF:RSSMRA80A01H501Z" },
  {
    $set: {
      "payments.$[p].netAmount": 1700.00,
      "metadata.sources.$[s].lastSync": new Date()
    }
  },
  { arrayFilters: [ { "p.paymentId": "PAY-2025-01" }, { "s.system": "DB2-GPP" } ] }
)

MongoDB’s document model allows fine-grained updates inside nested structures without rewriting the entire entity.

---

3. Read/Write Decoupling (CQRS Pattern)

The Single View can act as:

• A pure read model (offloading heavy queries from legacy)
• A read/write operational layer for new services
• Or both 

In the first phase, legacy remains system of record. MongoDB acts as ODS.

In the second phase, new microservices write directly to MongoDB for newly introduced attributes, while legacy continues feeding core data.

This gradual offloading aligns perfectly with your pension blueprint vision  .

---

Data Quality: Necessary, Not Overemphasized

Single View does not magically solve data quality. But it creates the right place to manage it.

At ingestion:

• Identity resolution rules
• Conflict resolution (source priority, timestamp precedence)
• Versioning metadata
• Lineage tracking inside the document

Instead of reconciling inconsistencies through runtime joins, we consolidate once and serve consistently.

The Single View becomes the stabilized representation.

---

Scalability and Operational Requirements

As highlighted in your blueprint:

• High read peaks (millions of accesses in days)
• Mixed operational and analytical workloads
• Hierarchical models with hundreds of attributes
• Sharding and scale-out requirements 

MongoDB natively supports:

• Horizontal sharding
• Replica sets for HA
• Rich aggregation framework
• Secondary indexes on nested fields   

This is what makes it viable for public administration workloads at enterprise scale.

---

Why This Matters for Public Administration

In public sector contexts:

• Citizens are represented differently across domains.
• Legal changes introduce new attributes continuously.
• Access peaks are predictable and massive.
• Regulatory constraints demand traceability and auditability.

Relational ODS layers tend to collapse under schema rigidity and change management complexity  .

A document-oriented Single View:

• Models the citizen as a first-class entity.
• Evolves without breaking consumers.
• Offloads legacy systems.
• Enables microservices and API-driven architectures.
• Serves as foundation for analytics and AI without duplicating logic  .

---

Final Thought

The journey from legacy silos to Single View is not about replacing systems overnight.

It is about introducing an aggregation layer that:

• Decouples reads from writes
• Consolidates truth per entity
• Evolves with regulation
• Scales with access peaks
• Enables new digital services

And when that layer is document-native, horizontally scalable, and integration-friendly, the Single View stops being a slide in a presentation.

It becomes the new operational backbone.