From Legacy Silos to Single View in the Public Sector

Every institution accumulates silos. It is not a design choice; it is history sedimented into systems. A mainframe for core processes, Oracle for public management, SQL Server for consolidation, a CRM on a different stack, files exchanged nightly, APIs added later. Each system is optimised for its own workflow, not for the citizen.

The result is fragmentation. The same person exists in multiple representations, with 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 rather than a feature.

An aggregation layer, not a replacement

A Single View is not a data warehouse, not a cache and not a reporting mart. It is an operational aggregation layer that centralises data from heterogeneous sources into a user-centric, hierarchical model, decoupled from legacy schemas and optimised for modern services. The core idea is simple: move from process-centric storage to entity-centric representation. In legacy systems data is organised by procedure, a payments table, an eligibility table, a sanctions table, and to reconstruct a pensioner you join. In a Single View you model the pensioner directly.

Consider a typical pension landscape: a DB2 mainframe for private pension management, Oracle for public pensions, SQL Server consolidating synthetic payment data, web services exposing fragments and batch files exchanged overnight. Each system holds a partial truth. Reconstructing the full entity requires cross-system joins, often at runtime and often under load, which brings lock contention, performance degradation and rigid change management.

Modelling the Single View as a document

Instead of flattening everything into a pre-agreed common relational model, the entity is represented as a document in MongoDB. A pensioner document, scoped initially to payments, looks like this.

{
  "_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 denormalisation for convenience. It is a deliberate shift to a hierarchical, JSON-native model aligned with how the entity is actually consumed. When new services arrive, the document is extended rather than the database redesigned. Adding a communications channel, for instance, is a new sub-document.

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

No downtime, no global schema migration, controlled evolution.

Feeding the Single View

The key question is not modelling. It is ingestion. The integration axes are the familiar three, batch ETL, change data capture and streaming, and they are best formalised into a layered architecture.

Batch ETL handles the initial load and reference data: historical backfill, periodic low-frequency datasets, reference tables. The flow runs from the legacy database through an ETL engine such as DataStage, SSIS or Spark, into transformation and then a MongoDB bulk insert. Ordered bulk writes are recommended to preserve consistency and performance on large initial migrations. This is the right tool for first-time population of the Single View cluster.

For operational freshness, batch is not enough. Change data capture from DB2, Oracle and SQL Server feeds Kafka, or an equivalent streaming backbone; a transformation layer enriches the domain object; the result is upserted into the Single View. Each change event updates the relevant fragment of the entity document, and MongoDB’s document model allows fine-grained updates inside nested structures without rewriting the whole entity.

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" } ] }
)

The third axis is read/write decoupling, in the spirit of CQRS. The Single View can act as a pure read model that offloads heavy queries from legacy, as a read/write operational layer for new services, or as both. In a first phase legacy remains the system of record and MongoDB acts as an operational data store. In a second phase new microservices write directly to MongoDB for newly introduced attributes, while legacy continues feeding the core data. The offloading is gradual by design.

Data quality, necessary but not overemphasised

A Single View does not magically solve data quality, but it creates the right place to manage it. At ingestion the layer applies identity resolution rules, conflict resolution by source priority or timestamp precedence, versioning metadata and lineage tracking inside the document itself. Instead of reconciling inconsistencies through runtime joins, the data is consolidated once and served consistently. The Single View becomes the stabilised representation.

Scale and operations

Public pension workloads have a recognisable shape: high read peaks, with millions of accesses concentrated in a few days, mixed operational and analytical traffic, hierarchical models with hundreds of attributes, and a clear need for sharding and scale-out. MongoDB supports this natively through horizontal sharding, replica sets for high availability, a rich aggregation framework and secondary indexes on nested fields. That is what makes it viable for public administration at enterprise scale.

The public sector adds its own constraints. Citizens are represented differently across domains, legal changes introduce new attributes continuously, access peaks are predictable and massive, and regulation demands traceability and auditability. Relational operational data stores 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 and serves as the foundation for analytics and AI without duplicating logic.

Why it matters

The journey from legacy silos to a 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 and enables new digital services. 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.