Ole Germann Kristensen

Systems, Integration & Architecture

  • NorthGrid: A Hybrid Integration case

    NorthGrid: A Hybrid Integration case

    For this case I wanted to work through a fairly awkward situation: an agreement system has to stay on-premises, cloud connectivity can disappear for four hours, and billing still needs every committed change. Those requirements give the design something concrete to answer.

    This fictional case shows how I would structure a hybrid integration platform serving both operational and analytical workloads.

    Hybrid integration landscape linking on-premises servers, an electricity grid and cloud data services.
    A hybrid integration platform connects operational systems, cloud services and analytics.

    The case

    NorthGrid is a fictional electricity supplier. Its agreement system remains on-premises, while billing, CRM, notifications, a market partner and analytics consume its data. Meter readings arrive continuously and feed operational monitoring and a warehouse.

    The existing estate contains direct database reads, scheduled files, point-to-point APIs and BizTalk flows. Downstream teams often discover failures before the integration team does.

    Governing constraints

    IDConstraint
    C1The agreement system remains on-premises for at least three years.
    C2Cloud connectivity may be unavailable for four hours.
    C3A committed agreement must never be lost.
    C4Duplicate delivery is permitted; duplicate business effects are not.
    C5Billing requires ordering per agreement, not global ordering.
    C6PII must not appear in normal telemetry.
    C7Warehouse downtime must not block billing or CRM.
    C8BizTalk must be migrated incrementally.

    System context

    Customer portal, source systems and meters connect through an integration platform to operational, analytical and external consumers.
    System context for the fictional NorthGrid integration platform.

    The platform separates three interaction modes.

    ModeMechanismReason
    Immediate query or commandAPIThe caller needs an immediate answer.
    Agreement distributionDurable message topicA business event needs independent subscribers, retry and per-key ordering.
    Meter readingsPartitioned event streamThe workload is high-volume, time-oriented and replayable.

    Five decisions

    • D1: Commit business state and an outbox row atomically.
    • D2: Store and forward locally during cloud disconnection.
    • D3: Use at-least-once delivery with idempotent consumers.
    • D4: Keep operational consumers independent of analytical availability.
    • D5: Migrate BizTalk one contract boundary at a time.

    Agreement distribution

    Agreement database and outbox publish through a local buffer and message topic to operational and analytical consumers.
    Durable agreement distribution with independent operational and analytical consumers.
    AgreementDB → Topic : AgreementChanged.v1
[at-least-once, ordered by agreementId]

    The agreement transaction updates its business tables and inserts an outbox row in one local transaction. A relay publishes the event asynchronously. Cloud availability is therefore removed from the source transaction.

    No component claims exactly-once transport. Each consumer enforces uniqueness on the original eventId in the same transaction as its business effect. Delivery may be repeated; the business outcome may not.

    Operational and analytical separation

    Billing processes committed agreement events through its own subscription. Analytics advances an independent checkpoint and owns its own recovery and backfill.

    Operational correctness must not depend on warehouse availability.

    Billing and analytics can therefore be looking at different revisions for a while. Each consumer needs a visible last-processed revision and a recovery plan. Support should be able to tell whether a difference is expected lag or a missing event.

    Four hours without cloud connectivity

    Local servers buffer messages while disrupted connectivity separates them from cloud services.
    Local store-and-forward keeps business data safe during a cloud outage.
    Connectivity moves from connected to disconnected, buffering, protection, recovery and normal operation.
    The complete lifecycle of a prolonged connectivity outage.

    An outage design requires three capacities:

    1. Retention: how much data can be preserved?
    2. Catch-up: how quickly can the backlog be drained while live traffic continues?
    3. Protection: what happens before storage is exhausted?
    buffer ≥ peak_rate × outage_window × payload_size × safety_factor

catch_up_rate > live_rate

    Recovery needs spare capacity

    If catch-up capacity only equals normal traffic, the backlog never disappears. The architecture therefore reserves additional recovery throughput and defines a protection state before storage reaches its limit.

    Security and telemetry

    Telemetry may contain technical identifiers, approved business keys, contract version, stage, outcome, latency, retry count and classification.

    It must not contain full customer payloads, credentials, access tokens, unrestricted headers or raw failed messages outside controlled quarantine.

    traceparent connects one technical execution. correlationId connects related business work. Neither replaces audit records.

    Incremental BizTalk modernisation

    Migration progresses from behaviour inventory through contract freezing, parallel comparison, ownership transfer and retirement.
    Boundary-by-boundary modernisation replaces behaviour without a big-bang rewrite.

    I would migrate one externally observable behaviour at a time, such as delivery of an accepted agreement to billing. Several orchestrations, maps and pipeline components may contribute to that behaviour. Before moving it, inventory ordering, retry, context properties, correlations, maps, custom components, operator actions, throughput and known failure behaviour.

    The replacement may run in shadow mode to compare outputs, but only one path may own each external side effect. Ownership moves once, after the acceptance baseline is met.

    The Azure and SQL pieces I would use

    For agreement distribution, I would use a SQL Server outbox in the source transaction, an on-premises .NET relay and an Azure Service Bus topic with separate subscriptions for billing, CRM and analytics. The durable local buffer in the diagram can initially be the outbox itself. A separate local broker would need a reason, such as several local producers or a different operational ownership boundary.

    The relay marks a row as published after the broker acknowledges it. A crash between acknowledgement and that update will publish it again. The eventId therefore stays unchanged across attempts. If several relay instances run, their claim and lease mechanism must prevent them from casually overtaking revisions for the same agreement.

    I would set SessionId to agreementId where ordered processing is required and include the source revision in the payload. Service Bus sessions provide ordered handling for related messages; they cannot repair a producer that publishes source revisions out of order. A consumer should detect a revision gap and hold dependent work for reconciliation. Dead-lettering an earlier revision also needs an explicit policy for the later ones.

    For a consumer whose business effect is a local database update, I would store the processed eventId and that update in the same transaction, backed by a unique constraint on consumer identity and eventId. For an external billing API, that transaction cannot include the remote effect. The billing contract needs a stable idempotency key or a status query and reconciliation path. This distinction matters when an HTTP response is lost.

    Put numbers on the four-hour buffer

    As an illustrative sizing exercise, assume a sustained outage arrival rate of 20 agreement events per second with 4 KiB per event. Four hours produces 288,000 events, about 1.10 GiB of payload. A factor of three gives roughly 3.30 GiB for this payload budget. SQL row overhead, indexes, transaction logs, backups and other database activity require additional capacity; this calculation is only a starting point.

    To drain those 288,000 events in one hour while 20 new events per second continue arriving, the complete downstream path must sustain at least 100 events per second: 20 live plus 80 recovery. I would benchmark the target writes as well as the relay. One heavily used agreement session can limit progress even if the broker has spare aggregate throughput.

    Alerts should include the oldest unpublished outbox row, database free space and estimated time to exhaustion. Before storage fills, the application needs a defined response: throttle or reject new work visibly, or enter an agreed degraded mode. Quietly dropping committed events would violate the case requirement. Database durability, backup and restore testing are also required; the outbox only addresses the handoff between the transaction and messaging.

    Keep meter history recoverable

    For the meter stream I would evaluate Event Hubs with a partition key based on meter identity, separate consumer groups and retained raw readings in storage for longer recovery. Partition and retention settings need measured arrival volume and a backfill requirement. Agreement messages and meter readings have different payload sizes and processing costs, so I would size and load-test them separately.

    Verification

    TestPass condition
    Four-hour network cutNo committed agreement is lost.
    Deliver each event five timesOne business effect per consumer.
    Disable the warehouse for two hoursBilling and CRM remain within SLO.
    Run contract v1 and v2 togetherBoth complete without producer rollback.
    Restore connectivityAgreement backlog clears within 60 minutes.
    Scan telemetryNo restricted payload field or secret is present.

    The first implementation I would verify

    I would implement the agreement outbox, one relay and a test billing consumer first. The first demonstration should include a disconnected network, a relay crash after publication and a duplicate delivery. The database state and the billing result should be inspected after each test.

    The figures and pass conditions here are design assumptions for a fictional case. They give the team a way to test the proposal before extending it to every consumer. I would keep the test results beside the configuration and runbook so the next change can be checked against the same expectations.

  • ORDER!

    ORDER!

    Survival games have given me a very practical appreciation for labels. At the beginning, everything fits in one box. Later, I spend half my time opening boxes because I know I put a particular tool somewhere.

    A sign saying wood or tools saves a surprising amount of effort.

    I think about enterprise systems in a similar way. A service name, a team name or an acronym should help someone find the thing they need. When the same label means several things, people can spend an entire meeting agreeing with each other while imagining different systems.

    Put the meaning where people can find it

    Take the word domain. It could mean a DNS domain, an identity domain or a business area. I would put those meanings in one searchable index with links to the teams that maintain the definitions. A common place to look is useful even when different teams legitimately use a word differently.

    I would keep each entry short:

    • Term and context.
    • Definition and owner.
    • Related systems.

    For a business term such as available stock, I would add the calculation and an example. If warehouse and sales need different definitions, name those variants and explain their relationship. Forcing both into one vague sentence loses information.

    This can start as Markdown in a shared repository or a maintained page in the company wiki. The useful part is the agreement to update it when a contract or system changes. A glossary that requires a separate annual clean-up project will spend much of its life out of date.

    Give services a stable identity

    Names change when teams reorganize. I would give an integration a stable identifier and keep its display name separate. That identifier can appear in deployment configuration, logs, dashboards and runbooks. Searching for it should lead to the same flow even after the team owning it has changed.

    id: warehouse-stock-import
displayName: Warehouse stock import
owner: warehouse-integrations
source: warehouse
target: erp
contract: StockSnapshot.v1
runbook: docs/runbooks/warehouse-stock-import.md
status: active

    This is an illustrative catalog entry. In a real repository I would validate required fields in the build and check that the runbook path exists. A link to the owning team’s maintained contact page is better than copying a person’s name into ten configuration files. The catalog can stay small until there is a reason to automate more of it.

    Keep a place for the awkward things

    I still want a miscellaneous category. There will be a one-off PowerShell fix, an unexplained mapping exception or a small experiment that does not yet deserve its own structure. Making a home for those things is more practical than stopping every task to invent a taxonomy.

    Before running that script

    Even a miscellaneous script needs a few facts. I would record who added it, why it exists, which environment it affects and whether it is safe to run more than once. A production SQL correction should also have a review and a record of the affected keys. Calling it miscellaneous should have no effect on the access or review it requires.

    For an experiment, I would add a review date and a sentence describing what would make it worth keeping. That gives future me a starting point. A directory of scripts named final-fix-2 tells me very little about whether any of them should still be used.

    Let repeated work suggest the next category

    Suppose miscellaneous starts filling up with failed-message replays, mapping overrides and stock corrections. I would look at the reasons they are needed. Perhaps several are compensating for the same missing validation in the source. Perhaps the operator interface needs a supported correction function.

    Grouping those scripts under manual interventions is useful if it leads to shared controls: input validation, a preview of affected records, an audit entry and a documented recovery step. Moving them into a neatly named folder without changing how they are used buys much less.

    I would review a small portion of the list regularly, beginning with the items that touch production or get used repeatedly. Retire a script when its replacement is in service and its retention needs have been handled. Keep an archive record explaining the replacement so an old support note still makes sense.

    Make the structure earn its upkeep

    A label is doing its job when someone can find the right service, understand what it owns and reach the people responsible for it. I would test the catalog by asking a colleague unfamiliar with a flow to locate its runbook and explain how to recover a failed message. The points where they get stuck show what needs improving.

    That is the level of order I am after. I want enough structure to stop losing tools in boxes, and enough room to keep the odd useful thing until I understand where it belongs. In a software estate, stable identifiers, a searchable vocabulary and a small amount of maintained metadata go a long way.

  • Local AI Workflows: Building an Incident Assistant

    Local AI Workflows: Building an Incident Assistant

    The local AI workflow I would start with is an incident assistant for an integration team. An operator selects a failed flow, and the assistant prepares a short note containing the observed error, relevant runbook passages and a suggested next check. That is a small enough job to test properly.

    The operator can open the sources and decide what to do.

    The attraction of running it locally is straightforward: the selected operational context can stay on company infrastructure, and a repeated summary has no external model token charge. There is still a machine to operate and software to maintain. I would make the first version useful on one class of incident before trying to give it access to the whole estate.

    Define the result before choosing the model

    Take a warehouse reservation timeout. The useful output would identify the affected integration, when the timeout occurred, whether a booking or reservation reference was received, and the runbook section for checking an uncertain outcome. It should distinguish recorded facts from suggested explanations. An invented statement that the warehouse is down could send the operator in the wrong direction.

    I would ask for a compact structured result: summary, observedFacts, suggestedChecks and sourceIds. The application validates that shape and verifies that cited source IDs were actually supplied to the model. A valid structure alone says nothing about whether the explanation is correct, so the interface should show the original error and cited passages beside the draft.

    A concrete first implementation

    One possible stack is an internal ASP.NET Core application, a PostgreSQL database holding approved documentation and incident metadata, and Ollama serving a local Qwen3 8B model. This is a proposed starting point, not a claim that I have benchmarked it for every company. The model needs to fit in memory with the chosen context size, and the team needs to test its language and task quality.

    The application handles identity and retrieves context before calling the model. Ollama should be reachable only from the application or a controlled administration path. The browser does not need direct access to the model server. I would begin with one concurrent generation, a bounded queue, a request timeout and a clear busy response when capacity is exhausted.

    Each request should include an integration ID and a bounded time window. The server retrieves a small set of relevant errors and the approved runbook for that integration. It then sends only that material and the task instructions to the model. That keeps the first implementation understandable and gives us a way to inspect exactly what produced an answer.

    Start retrieval with identifiers and text search

    For an error code such as WH-RESERVATION-TIMEOUT, an exact match against a maintained runbook is a good first retrieval method. PostgreSQL has full-text search for broader document queries. I would combine explicit integration and error-code filters with text search before adding a vector index.

    Each stored passage needs a document ID, version, source URL or path, last-reviewed date and access information. When a runbook changes, the index should update or remove old passages. Otherwise the assistant can give technically plausible advice for a retired version of the integration.

    Embeddings may help when people describe the same issue in different words. I would add them if a test set shows that useful passages are being missed. That introduces another model, an indexing job and a decision about chunk size. Those are manageable tasks, but I want a measured retrieval problem to justify them.

    Enforce access before building the prompt

    Apply permissions during retrieval

    The server must apply the user’s access permissions during retrieval. Telling the model to ignore restricted documents is too late once their contents are already in the prompt. The same rule applies to cached answers: a result produced for one user cannot be reused for another without checking what information it contains.

    Incident text and documents can contain misleading instructions, either deliberately or by accident. I would treat them as data and keep the first assistant read-only. It gets no shell, database write access or replay endpoint. A suggested command can be displayed as a draft for review. Executing it would be a separate feature with explicit authorization and an audit trail.

    Logs also need filtering before indexing. Credentials, access tokens and full customer payloads usually add risk without improving the incident explanation. An allowlist of useful fields is easier to reason about than hoping a later redaction step catches everything.

    Measure what the operator can use

    I would collect thirty representative, sanitized incidents and write the expected facts and useful source passages for each. Include missing logs, contradictory timestamps, a retired runbook and an error with no known procedure. Someone familiar with the integrations should judge whether the suggested next check is supported by the evidence.

    For each run, record:

    • Retrieval quality and unsupported claims.
    • Edits needed and response time.
    • Whether the operator could finish the note sooner.

    Compare against opening the runbook directly. If retrieval finds the wrong procedure, changing the generation model may leave the underlying problem untouched.

    Record model identity, prompt version and document versions with the test results. Run the same examples when any of them changes. Keep the previous model and configuration available so an update can be rolled back if familiar incidents start producing worse advice.

    Budget for the complete request

    Local cost includes hardware, electricity, storage, patching and the time spent maintaining the documents and application. Measure electricity at the wall over a representative workload. For capacity, include retrieval, prompt processing, generation and time waiting in the queue. A generation-only tokens-per-second result overstates what an end user receives across the complete workflow.

    Ollama’s generation response exposes timing and token-count fields that can help with these measurements. I would also record total request duration at the application boundary. Compare cost per accepted incident note, including retries, against any external alternative being considered.

    An external model can remain an explicitly selected option for approved data. Automatic fallback deserves particular care: a local outage must not silently change where incident information is processed. The workflow should have a visible failure mode when a request cannot be completed under its data-handling rules.

    The first delivery

    I would deliver one working incident type, a small approved document set, a quality report, a restore procedure and a list of cases the assistant cannot handle yet. That gives the team something they can use and improve. Additional integrations can follow once their runbooks, access rules and examples are ready.

    The company then owns a useful piece of application logic: selecting context, controlling access, recording evidence and reviewing a draft. A model replacement still needs testing, but those parts can remain in place. That is the practical reason I would put effort into a local workflow.