Analytics Reporting Engine

System perspective

01 / SOLUTION DESIGNPurpose, scope, and design principles

The Analytics Reporting Engine converts operational order and payment data into consistent, site-scoped reporting. A Go modulith coordinates historical imports and real-time inbox processing, maintains PostgreSQL reporting data, serves tenant analytics, and gives operators an authenticated control plane. A separate React application presents the reports; a separate GitOps repository defines their Kubernetes deployment.

This document presents the target operating architecture for engineering and CTO review. Components are described in their intended operational roles. It covers application behavior, data ownership, deployment, delivery, and recovery as one system.

What the solution includes

The source systems, identity providers, and control database sit outside the application process boundary. The same reporting database supports API reads and controlled ETL writes; consistency therefore depends on their shared lifecycle and locking contracts.

How to read this design

Start with the application and ETL flows to understand behavior. The database chapters explain the six schemas and every application table at business and relationship level. Deployment and delivery chapters explain where the processes run and how reviewed changes reach them. The final chapters cover operational signals, failure recovery, capacity, verification, and decisions that still need an owner-approved contract.

ERDs deliberately omit columns. Solid relationship edges denote declared relational references; dashed edges denote derivation, matching, or conceptual association, as labeled in each diagram. A relationship in a business flow does not imply a database foreign key. Expand any diagram for closer inspection; all diagrams and document controls work without a network connection.

Technology rationale

02 / SOLUTION DESIGNWhy these components and patterns

Each major choice solves a particular problem. Its benefit depends on retaining the associated operating contract; adopting the technology alone does not establish correctness or availability.

03 / SOLUTION DESIGNApplication architecture and domain ownership

The application is a Go modulith with separate runnable processes. A single backend repository governs domain contracts, the shared analytics model, import behavior, operator workflows and public HTTP compatibility. This is a code ownership and integration decision: the operator web application, public API, dispatcher, workers, scheduler and site synchronizer retain separate execution lifecycles. They can be operated and scaled according to their responsibilities without creating independent, competing definitions of an order or an import.

Each domain exposes a provider-owned public Go API and keeps implementation and database access private. Consumers call that API instead of importing another domain's repository or internal packages. Cross-cutting authentication, configuration, database connections, logging and external-system adapters belong to the platform layer. Public Go APIs are module contracts; their existence does not imply a public HTTP endpoint or a separately deployed network service.

The independent migration command owns schema changes for the selected environment and database. QA and production verification have separate harness commands with different mutation permissions. These composition roots assemble service APIs and platform dependencies; they do not move domain behavior into command-line entrypoints.

Operator experience and control-plane capabilities

Ops UI presents the operational dashboard, database and migration information, health checks, site inventory, ETL job history, releases, monitors, settings and restricted connection administration. Server-rendered pages and htmx partials use the same Go application model. Actions enter the owning domain through its public API, so UI convenience cannot replace service-level safety checks.

Ops Core provides immediate ETL queueing, site-sync queueing, cancellation and reset, guarded site deletion, retained-staging cleanup and controlled failure-evidence export or pruning. It also exposes configuration, connection checks, schedules and operational summaries. Jobs and audit events record who requested work and what scope was accepted. Backup, migration, incident, monitoring and release records form the operational catalogue; displaying a record is separate from implementing the associated external execution system.

Cloud Turso/libSQL is the durable control plane for jobs, attempt ownership, users, roles, sessions, settings and operational records. PostgreSQL is the authority for site lifecycle, import runs, source/staging evidence and analytics facts. A queued Ops job and its ETL run are linked but answer different questions: the job identifies requested work and execution ownership; the run explains data processing and promotion outcomes. Diagnostic exports assist investigation but do not become either authority.

04 / SOLUTION DESIGNCustomer reporting and external application dependencies

The customer application is a separate React/Vite frontend. Its desktop and mobile shells share routes and reporting stores, with the shell switching at the responsive breakpoint. Shared application state supplies the selected site, date range, comparison and filter choices. Route-specific stores request data, calculate presentation totals and shape results for KPI cards, charts and tables.

The Analytics Public API supplies daily and monthly aggregates for orders, sources, items, categories, payment modes and states, discounts, tables, waiters, captains, stations and chefs, plus monthly customer-order aggregates. Order listing supports the existing page interface and a site-bound cursor interface. This compatibility surface lets the reporting application change backend implementation without changing every report's external contract.

The browser also uses Cognito-authenticated AppSync queries for site settings, staff, categories, services and customer lists/search. Menu reports therefore combine facts from the Analytics API with metadata from AppSync. Customer summary insights use a separate customer-insights HTTP service. These integrations remain explicit application dependencies: moving analytics facts and aggregates into the Go backend does not absorb the AppSync graph or customer-insights service.

The dedicated Analytics client accepts GET requests only, fixes requests to its configured HTTPS API origin and path, retrieves the current Cognito ID token and sends it as a bearer token. It sends no credential cookies. Missing sessions or authentication rejection return the user to sign-in; denied site access produces an application alert. Analytics error reporting retains safe status and reason categories rather than tokens or complete request objects.

The customer-insights call uses a separate Axios path rather than this shared client. Its service ownership and authentication contract must therefore be assessed independently; the Analytics API's bearer and site checks cannot be attributed to it. Similarly, browser site selections and local storage are presentation inputs, never server-side entitlement evidence. A unified frontend site-state contract remains a separate implementation decision where the existing stores use different storage keys.

05 / SOLUTION DESIGNIdentity and authorization boundaries

Operator access and customer access are deliberately separate trust paths. Operators sign in to Ops UI through GitHub OAuth. The callback validates OAuth state, exchanges the authorization code and applies the approved organization/team admission policy, including explicit bootstrap-administrator handling. The application maintains its own server-side session and roles in Turso. OAuth callback URLs and secure cookies belong to the public operator hostname.

Ops authorization uses four roles: reader, db_admin, admin and superadmin. Readers inspect operational views. Database operators perform permitted data operations; administrators additionally control broader operational settings and privileged actions. Superadministrators manage secrets and role assignments. A production replacement of an already-live site requires an administrator or superadministrator. Mutation handlers require a valid session, permitted role and CSRF token, while the domain records the action's audit context.

ETL queueing, including replacement of a live site, does not require typed confirmation. Its controls are environment scope, role, site eligibility, concurrency protection, CSRF and audit. Other destructive or production flows retain their own confirmation contracts. Treating every action as one generic confirmation policy would obscure these distinct responsibilities.

Customer sign-in uses Amplify and Cognito. The Analytics Public API verifies a Cognito ID token against the configured issuer and approved frontend client audience, including signature, token type, subject and time validity. Signing keys come from the configured Cognito key endpoint with bounded refresh and cache age. A token cannot choose a different issuer or key-discovery endpoint.

After authentication, the site authorizer compares the requested site with the verified immutable site claim. This contract depends on administrator-controlled provisioning and site attributes. A matching path, request body, local-storage value, Origin header, UI role or identity-looking header grants no authority by itself. Valid browser identities receive site reads only. Ingest, delete and cancel-replace requests are denied before source or repository work.

CORS grants browser access only for exact configured origins; it is not an authentication mechanism. Missing or invalid credentials produce authentication rejection, while an authenticated caller requesting another site or a disallowed operation receives authorization rejection. Public documentation and non-tenant routes do not expose tenant records. Local token verification also means that immediate account revocation is not a guaranteed behavior: an immediate-revocation requirement would need a separate architecture decision.

06 / SOLUTION DESIGNJob dispatch, ownership and cancellation

An operator queues an import for one environment and site through Ops UI. Admission validates the permitted environment and role, configured site allowlist, active source mirror, metadata freshness and supported import options. Site metadata must be no older than 24 hours plus the allowed five-minute margin. An existing queued or running import for that environment/site prevents a competing request. The accepted request and audit event enter Turso together and receive a durable job identity.

The environment-bound dispatcher claims queued work up to its configured concurrency cap and creates a finite Kubernetes worker Job for each attempt. The dispatcher controls lifecycle while the worker executes ETL Core. Site synchronization uses its own queue consumer. This division prevents a reporting request or an operator page load from becoming an unbounded background import.

Each attempt has a durable identity, lease and recorded Kubernetes reference. Worker heartbeats renew ownership, and the pipeline checks its ownership fence at processing and promotion boundaries. A worker that loses ownership cannot continue claiming success or overwrite the outcome of a replacement attempt. The dispatcher reconciles observations against persisted intent and identity; a missing Pod alone is insufficient to assume that work never started or that a site is safe to release.

Cancellation crosses the same boundaries as execution. The control plane records the request, the worker observes cancellation or loses its valid lease, and reconciliation requests termination of the corresponding Kubernetes Job. Job and Pod identity checks avoid deleting unrelated resources. Site guards remain in place through ambiguous creation or termination races until reconciliation can resolve ownership safely.

ETL worker loss is not automatically retried. In particular, spot_preempted is reserved for a strict evidence match: a persisted attempt-to-Pod-to-VM association and a completed GCP preemption operation for that VM within the recorded observation window. Spot placement, a restart, node disappearance or a generic failure message is insufficient. Missing, delayed, unreadable or mismatched evidence leaves a generic failure classification. Either outcome fences the ETL attempt and requires an operator decision to rerun.

A terminal worker result and a completed data transition are interpreted together. Cancellation before promotion has a different consequence from cancellation after replacement. The operator therefore uses persisted run and site state to choose recovery, instead of assuming that restarting a process resumes the last safe checkpoint.

07 / SOLUTION DESIGNETL lifecycle: replacement, catchup and live transition

The principal import mode is replace_site. It rebuilds a site's analytical history from MongoDB orders and DynamoDB payments, then runs catchup in the same queued job. A separate catchup mode supports explicit delta work where the site's lifecycle permits it. Neither mode changes the rule that source-backed evidence, validation and promotion determine whether target data can become authoritative.

Extraction and normalization

The worker starts a run and captures source high-watermark baselines using the target database's clock. It streams orders in batches, fetches associated payments and stages normalized results rather than modifying final facts during extraction. Streaming source handling and PostgreSQL bulk copy keep historical imports from requiring the entire site in application memory. Original source representations are carried separately from typed normalized models so diagnosis can distinguish captured evidence from reconstructed compatibility data.

Every historical or catchup extraction query is constrained to the selected site, the approved historical period beginning 1 April 2025, orders with a present total, and closed or completed business transactions. Transfer orders are excluded. One-off, allowlisted operator filters may narrow this population; they cannot remove these mandatory business rules. A filtered replacement therefore deliberately replaces the site with the qualifying population, making filter scope part of the operation's meaning. Separately authorized current-state classification can inspect reopening or rejection without changing these extraction rules.

ETL and live ingestion share normalization behavior through ETL Core's public contract. Monetary values become integer minor units using the established rounding convention; fractional item quantities remain fractional. An explicitly supplied zero amount is preserved, while an absent amount is derived from rate and quantity. Invalid nonfinite values or out-of-range conversions are rejected. Weekly summaries use ISO year and week together. These compatibility choices are concrete behavior, while broader approval of quantity and amount defaults remains a product contract decision.

Validation and atomic promotion

Staging validation checks the relationship between extracted source evidence and staged facts, usable payment identities, duplicate payment transactions, cross-site order conflicts and payment-to-order integrity. Transformation failures and validation failures produce diagnostic evidence and block promotion. An import may report completed_with_errors without having replaced any facts; a successful process exit alone does not mean its data was promoted.

For an already-live site, ingestion continues during extraction, staging and validation. A failed pre-promotion validation preserves the previous facts and their live state. Once staged data is valid, the replacement explicitly blocks fresh ingestion and obtains the site's promotion lock. This is the boundary at which the application stops accepting new live writes into the data set being replaced.

Historical promotion is one database transaction: remove the site's affected facts, insert the staged replacement, rebuild its analytical aggregates and commit. Row-by-row aggregate triggers are suppressed only within that bulk transaction; aggregate rebuilding completes before commit. Concurrent live mutation uses the same site-lock discipline, so its lifecycle check and writes cannot interleave through the replacement boundary. A failed transaction rolls back instead of exposing a half-replaced site.

Overlapping catchup and state meaning

After historical promotion, the same job repeatedly extracts overlapping change windows from the recorded source baselines, validates the pass and promotes small deltas. Overlap tolerates boundary timing without treating every re-read record as a new fact. Catchup compares staged and target content, including affected child and customer data, so its success criterion is zero net target change rather than simply zero extracted rows. Payments for orders not yet eligible for extraction can be deferred until their orders qualify.

Catchup is intentionally bounded. A pass containing more than 1,000 staged orders fails closed and requires a full replacement; configurable pass and runtime limits prevent an import from chasing a permanently moving source indefinitely. Reaching a limit leaves the site non-live. A zero-net pass records its evidence, marks the site live and completes the run.

A cancellation or failure after historical promotion leaves replacement data in place but keeps ingestion blocked until recovery converges. The defined V1 recovery is an audited Rerun full replace, restarting historical extraction instead of resuming an old checkpoint. Explicit delta mode is a separate supported action, not a promise of checkpointed recovery. Checkpointed resume requires its own decision and correctness proof.

Successful promotion removes its staging data; retained failures and pass diagnostics follow the authorized cleanup policy. Run summaries, counts, events and lifecycle state remain the evidence of what happened. The live flag describes ingestion eligibility. It is not a separate product guarantee of dashboard readiness, complete source delivery or an agreed freshness service level.

08 / SOLUTION DESIGNTransactional ingestion, durable receipts and corrections

The direct-create contract accepts an order with its items, discounts and payments as one logical delivery. The trusted pending-inbox service invokes ingestion through its public Go contract; HTTP callers remain subject to the separate HTTP identity and operation checks. Ingestion validates payload consistency, normalization and lifecycle eligibility before accepting new facts. Its transaction writes the related facts, aggregate effects, live-ingest bookkeeping and durable acceptance receipt together. The real-time apply operation additionally owns revision comparison, corrections and soft void, while provider-owned result lookup supports recovery before duplicate work is rehydrated.

A delivery identity is scoped by site and operation. An explicit idempotency key identifies a delivery; the compatibility path derives identity from the order when no key is supplied. The API hashes the accepted typed payload after its defined identity defaults, excluding raw source-evidence envelopes. JSON object formatting therefore does not create a new delivery, while changed accepted content under an existing identity is a conflict.

An exact direct-create retry returns the recorded response without inserting the order again. Reusing an identity with different accepted content is rejected, as is colliding with existing order or child identities without a matching receipt. Revision-aware application uses a distinct operation: it compares authoritative source state with the applied version and performs an upsert, a no-change outcome or a soft void. This also handles facts already populated by historical ETL without misclassifying them as duplicate delivery failures.

The repository acquires the site's lifecycle lock and serializes aggregate mutation within that site, then protects the order and receipt identity. It rechecks the receipt and live eligibility inside the transaction before writing. This closes races between duplicate requests, concurrent corrections and historical promotion. Different sites retain independent progress, while writes affecting one site's aggregates follow a consistent order.

Commit errors create a special ambiguity: the database may have committed even when the caller did not receive confirmation. The API attempts receipt resolution after an uncertain commit. A matching durable receipt returns the accepted result. Automatic transaction retry is limited to PostgreSQL failures known to have aborted the transaction; an arbitrary network error is not permission to execute the mutation again. If acceptance cannot be resolved, the caller must treat the outcome as uncertain and retry the same delivery identity through the producer's eventual delivery policy.

Receipts survive fact deletion, full-site replacement and application rollback. An authorized exact retry may replay its earlier accepted response even while fresh ingestion is blocked. That response acknowledges the original delivery; it does not assert that the fact still exists and does not resurrect data removed by a later operation. Receipts store acceptance identity and result, not a durable archive of the full original source payload.

Source-backed restoration is a distinct operation that fetches authoritative order and payment data. The cancel-replace capability likewise validates source identity and replaces an existing order transactionally; ordinary deletion is a separate guarded mutation. These paths share normalization and lifecycle locking, but must not be advertised as having the direct-create receipt's exact replay semantics. Browser Cognito identities are denied all of them.

The real-time design assigns transport receipt to the queue poller, processing to the pending-inbox service and gap detection to an independent reconciliation CronJob. It applies latest authoritative source state using updatedAt version checks without relying on event arrival order. Reopening, rejection and authoritative tombstones soft-void existing orders; amendments and reclosure use revision-aware upsert. Bounded backoff handles processing failures, and reconciliation automatically repairs missing or stale eligible facts under separate governed repair identities. EventBridge, SQS and the PostgreSQL inbox preserve notifications and work tracking, not a continuous archive of full source documents.

09 / SOLUTION DESIGNSite synchronization and supporting workflows

Site Master Sync scans the configured DynamoDB site master and updates the analytics site mirror. It records source presence and refresh time while preserving ETL and live-state ownership. A nonempty successful scan is required before missing sites can be marked inactive; an empty source result cannot deactivate the entire estate. Inactive or stale site metadata then blocks unsafe import admission. The bounded verification path can refresh only its owned site without declaring other sites missing.

The scheduler evaluates due non-ETL schedules, records enqueue or skip outcomes and advances schedule timing. The domain worker performs the resulting job. Production scheduled work retains its approval requirements, and production site synchronization retains its flow-specific confirmation. Enabled recurring ETL imports are excluded from the operating model; operators explicitly request ETL through Ops UI.

Cronmonitor validates monitoring configuration, runs supported checks and records results. Kubernetes mode disables host crontab and systemd actions, so preserved host recovery assets do not become part of cluster operation. Release Management defines the sequence from draft through QA approval, production approval and deployed state, with rejection where allowed. It owns transition rules; source review, image publication, GitOps reconciliation and deployment evidence are separate cooperating workflows.

Control and coordination

10 / SOLUTION DESIGNTurso control-plane architecture

Cloud Turso/libSQL is the operational authority for jobs, operator identity, configuration, health observations, and release records. PostgreSQL is the authority for imported data and ETL data-plane evidence. This separation keeps operator workflows independent of the reporting schema, but it also makes reconciliation a first-class responsibility: neither store can stand in for the other when deciding whether a worker stopped or a data change committed.

The control model contains fourteen application tables. Their roles are summarized below without a field-level dictionary. Migration bookkeeping is separate from this application inventory.

Cross-store consistency

A successful operator request first records governed work in Turso. The consumer claims that work and establishes an execution attempt. ETL creates its corresponding PostgreSQL run and evidence, performs data-plane transactions, and records its outcome. The dispatcher and operator views must reconcile these identities and outcomes rather than assume a transaction spans Turso, Kubernetes, and PostgreSQL.

Cancellation consequently has two parts: the control request and proof that the data writer has relinquished ownership. A missing Pod, expired lease, or queue transition alone is insufficient evidence that no process can still write. Attempt fencing, durable Job/Pod identity, worker heartbeat, and PostgreSQL lifecycle state provide the checks needed to recover safely.

Configuration bootstrap and recovery implications

Runtime starts with the Turso URL and a CSI-mounted token, loads the environment-scoped configuration, and only then opens the relevant source and target connections. The mounted token is read at startup; rotation includes controlled restarts. Normal runtime has no local file-database substitute. A Turso availability failure therefore affects control-plane access, new work, and configuration resolution even when the reporting database itself is available.

Recovery planning must cover both stores. Restoring PostgreSQL does not restore the matching queue or operator configuration; restoring the queue does not replay reporting transactions. The recovery procedure must reconcile open attempts, site state, ingestion receipts, and the selected reporting restore point before new work is admitted.

11 / SOLUTION DESIGNReal-time order delivery through a durable inbox

The real-time path connects MongoDB Atlas order changes to analytics without making a POS transaction wait for analytics availability. Atlas emits change notifications through its native EventBridge integration. EventBridge routes the selected events to an SQS Standard queue. A Go poller on GKE receives messages and records their identities and source pointers in a PostgreSQL inbox. A separate Go service processes pending inbox work, reads the current MongoDB order and associated DynamoDB payments, and invokes the owning ingestion service.

This design uses source events as notifications that an order may need attention. Latest authoritative MongoDB state, checked against the source-version contract, determines the operation; stale event intent does not override it. Processing does not rely on source events arriving in order and does not reconstruct the historical document as it existed at event time. An EventBridge archive can preserve notifications for investigation and redelivery, but a pointer event is not a full order or payment archive.

Atlas native capture is the producer path. A Lambda publishing helper, fallback publish outbox and Lambda sweeper are not mandatory components of this topology. Existing application write paths remain responsible for valid business writes; analytics notification capture is a separate integration. EventBridge provides the fan-out boundary so other subscribers can receive appropriate source notifications without sharing the analytics queue or inbox.

The two Go services have different responsibilities. The poller performs transport admission and durable receipt. It does not hydrate orders or mutate analytical facts. The pending-inbox service owns processing progress, source hydration and application outcomes. Both belong to the Go modulith's ownership model, with separate runnable lifecycles and provider-owned APIs; deployment separation does not justify duplicating normalization or analytics mutation logic.

Trusted event identity and tenant scope

A source event has a stable identity distinct from the MongoDB order identity. The event identity survives transport redelivery and intentional notification replay; the order identity groups the changes affecting one business object. MongoDB's updatedAt timestamp supplies the source-version comparison. These three concepts serve different purposes and cannot substitute for one another.

Admission validates the expected event source, environment, site and order scope against the configured integration. An event-supplied site identifier is a routing input, not an authorization grant. Queue ownership, source account, target database and trusted site scope must agree before the pointer enters processing. Duplicate event identities are accepted as the same notification only when their trusted routing identity agrees; conflicting reuse must be visible as an admission conflict.

The Atlas event selection and EventBridge transformation preserve the source event identity, site/order association and update timestamp without copying the full operational document onto the bus. Identifiers still carry sensitive business context and receive access controls. Admission rejects or holds notifications whose trusted site association cannot be established; the processor does not infer tenant authority from an arbitrary identifier.

12 / SOLUTION DESIGNTransport receipt and application acknowledgement

The first acknowledgement boundary is durable inbox receipt. The poller validates a message, inserts or verifies the corresponding inbox record, and commits that database operation before deleting the SQS message. A database error or unresolved commit outcome is not permission to discard the message. Redelivery uses the stable event identity to recover the durable receipt rather than creating independent work for the same notification.

The second boundary is application completion. The pending-inbox service applies the source state through ingestion, obtains a committed application result and then records completion in the inbox. These boundaries intentionally occur at different times. Deleting the SQS message means that PostgreSQL has accepted responsibility for the notification; it does not mean the order has appeared in reports.

These are tunable design baselines, not throughput promises. Standard SQS is the selected transport. Duplicate and out-of-order notifications are expected inputs; correctness comes from durable event deduplication, per-order processing and source-version checks. Worker concurrency and batch sizing must preserve those invariants.

FIFO releases its queue-level restriction when a message is deleted, while this architecture deletes after inbox receipt and before application. Independent inbox workers, source-read latency and processing retries can therefore change application order even behind a FIFO queue. This is an architectural consequence of the two-stage handoff; the PostgreSQL guards remain necessary. AWS documents the underlying Standard delivery model and FIFO message-group behavior.

A failure before durable receipt belongs to transport recovery. A failure after durable receipt belongs to inbox processing. Invalid envelopes, unavailable database connections and admission conflicts must remain distinguishable from missing payments, lifecycle holds and source-state conflicts. A queue DLQ cannot automatically capture poisoned inbox work after the corresponding SQS message has been deleted.

13 / SOLUTION DESIGNProcessing current source state

The pending-inbox service claims eligible work under a durable ownership mechanism and serializes application for each trusted environment, site and order. Ownership must remain valid through the mutation boundary; an expired or superseded worker cannot finalize a newer worker's result. Parallelism across unrelated orders is useful, while overlapping mutations for the same order must observe the same version and receipt rules.

Processing first resolves whether that event or application identity already has a committed result. A completed delivery can be acknowledged from its durable result without reading today's source document again. If no result exists, the service hydrates current MongoDB state and DynamoDB payments, establishes the trusted source identity and version, and determines the applicable business operation. The event is a reason to inspect the order; its historical type must not blindly override newer authoritative source state.

A delayed notification can fetch a newer authoritative state than the notification described. The processor applies that current state rather than executing stale event intent. It first checks that the fetched updatedAt is at least the event's update timestamp, then compares it with the applied source version. Fetched source states older than the applied version are recorded as skipped_stale; they cannot roll analytics backward. An old notification can still discover and apply a newer authoritative source state. Timestamp ties with conflicting content are treated as explicit conflicts, not permission to overwrite a newer interpretation.

A source read older than the event's required version remains pending for a backoff retry. After the configured maximum of N processing attempts, unresolved stale reads move to failed work for investigation. Transient source failures follow bounded retry handling. Expected ETL or live-state holds stay pending with a next-attempt time; they do not return to SQS or use its transport DLQ. The exact backoff schedule and N are operating parameters.

Hydration, eligibility and missing-source classification

The processor fetches one order and its payments by trusted site and Mongo order identity. It reuses ETL Core's source mapping and normalization, preserving fractional quantities, explicit zero amounts and established currency conversion. MongoDB and DynamoDB are read independently, so their combined result is not an atomic cross-source snapshot. Missing required payments or failed source validation holds application for processing recovery rather than committing an incomplete accepted order.

The normal order fetch applies the mandatory analytics extraction rules: site scope, approved historical period, present total, closed or completed status and exclusion of transfers. Consequently, an empty result can mean an open or reopened order, an otherwise ineligible order, a mismatched site or a genuinely missing document. It cannot by itself prove a deletion.

The source-state classifier distinguishes authoritative reopening, rejection or deletion from an unavailable or filtered-out document while preserving mandatory ETL filters. It accepts tombstone evidence only from a trusted deletion signal or authoritative scoped absence check. A transient read error, absent payment set or filtered-out document is never silently converted into a tombstone.

Reopened or rejected source orders are soft-voided when previously ingested: the analytical row remains for audit but no longer contributes to active reporting aggregates. Authoritative deletion evidence follows the same soft-void path. A rejected order that has never been ingested records a no-write outcome. Reclosure restores the active analytical state through revision-aware upsert; amendments, admin edits and merged-order corrections use the same controlled replacement behavior. Latest source state governs each decision, so a delayed reopen notification cannot void an order that the source has since reclosed.

Reuse ingestion through its owning API

The inbox service calls the ingestion domain through its provider-owned Go API. This internal call is separate from the customer HTTP authorization path. Customer Cognito tokens continue to authorize site reads only; they are neither required nor sufficient as producer write credentials. The integration's service identity, queue permissions, source access and environment/site trust define the worker's authority.

Ingestion retains ownership of normalized facts, aggregate effects, lifecycle locking and acceptance receipts. It checks source-site activity and live eligibility, acquires the normal mutation locks and rechecks the gate inside the transaction. During historical replacement or catchup holds, fresh work remains pending in the inbox after transport acknowledgement. Expected lifecycle delay does not become repeated SQS delivery or queue DLQ churn.

The owning ingestion API exposes revision-aware apply alongside its direct-create contract. It selects creation for a missing qualifying order, upsert or replacement for a newer amendment/reclosure, and soft void for authoritative removal from the active order population. An existing fact inserted by historical ETL is compared with current source state rather than treated as a failed duplicate merely because it lacks a live-delivery receipt. Already-current data receives a recorded no-change outcome; a newer state is applied with its version and acceptance record.

14 / SOLUTION DESIGNTransaction boundaries and crash recovery

The application transaction commits analytical facts, child data, aggregate effects, applied source version and the corresponding acceptance receipt together. The version guard and ownership check participate in this same correctness boundary. Updating the inbox to completed is a subsequent, recoverable database commit. The ingestion service owns its transaction; the inbox service resolves and records its result through the provider API rather than reaching into its repository or assuming a shared connection creates atomicity.

This separation leaves a deliberate crash window: facts and receipt may commit while the inbox still looks pending. Recovery finds the committed application result before rehydrating that event, then finishes inbox bookkeeping without applying the mutation again. The provider's receipt-resolution operation correlates the inbox work with the accepted operation, source version and result.

A receipt hashes accepted content and rejects reuse of the same identity with different content. Blindly fetching an updated MongoDB document under an already-accepted identity could therefore create a false replay conflict. Receipt resolution precedes hydration for duplicate work, preserving the distinction between resolving a previous attempt and applying a new source revision. A conflict is recorded for investigation rather than silently counted as success or escaped by changing its key.

An uncertain commit is not equivalent to a failed mutation. Receipt resolution establishes whether the attempted application committed before processing recovery decides to retry. Automatic database transaction retry is limited to known-aborted failures. Inbox source-read retries use backoff and the bounded processing-attempt policy independently of transport redrive.

Application receipts distinguish ingest, replacement and void outcomes and record the event/application identity, accepted source version and result. Correction and soft void commit their aggregate effects with these records just as creation does. A receipt survives later changes and acknowledges its earlier operation; it does not claim that an order is still active or still has that version. Reclosure is a new revision-aware operation, not resurrection caused by replaying an old create receipt.

An optional applied-acknowledgement event may update an AWS-side status mirror for other systems. That is a third, external delivery boundary. Failure to publish the mirror update does not undo a committed PostgreSQL mutation. The mirror's own delivery and recovery mechanism must be defined before it is treated as an authoritative view of application progress.

15 / SOLUTION DESIGNIndependent reconciliation and explicit repair

A dedicated Kubernetes reconciliation CronJob compares authoritative source state with analytical state independently of the notification stream. It detects missing or stale analytical data and incomplete application outcomes, then repairs eligible discrepancies through the owning ingestion API. It reuses source-reading and normalization contracts while remaining separate from operator-managed historical imports.

The CronJob scans changed source data every 15 minutes over a two-hour overlap. A daily T-1 check after day closure compares the previous day's order counts and monetary totals, alerting on discrepancies. Persistent coverage tracking identifies missed intervals and extends recovery beyond the routine two-hour lookback after a longer outage. A moving window alone does not establish that every earlier interval was reconciled.

Comparison must account for orders modified on older business dates, payment-only changes in DynamoDB, reopened or rejected orders and authoritative deletions. A closed-order MongoDB query can find eligible facts to add or refresh, but cannot alone discover all target facts that should be removed. Daily count and total checks are useful signals; matching totals do not prove matching order identities, payment details or aggregate dimensions.

Missing or stale eligible facts are repaired automatically from current source data. Each repair carries a separate governed repair identity, trusted site/environment scope, source version and recorded result. Reopened, rejected or authoritatively deleted orders follow the same soft-void policy as event processing. Ambiguous source evidence or conflicting versions is held for investigation; automatic repair does not infer tombstones from failed reads or silently launch a broad historical replacement.

EventBridge archive replay remains useful for recovering notifications, but duplicate inbox identities and durable receipts intentionally suppress duplicate application. Replaying archived events is therefore not a general repair procedure for corrupted or removed target facts. Explicit repair must establish its own authority, source version and recorded result while preserving the original delivery evidence.

The reconciliation CronJob is not an enabled etl_import schedule. Historical ETL remains immediate and operator-managed. Reconciliation uses bounded comparison and individual revision-aware repair operations; it does not invoke a full historical replacement merely because a timer fired. ETL catchup retains its separate lifecycle transitions, capacity limits and promotion semantics.

16 / SOLUTION DESIGNRemaining real-time parameters

Standard transport, current-source precedence, soft void, revision-aware upsert, bounded retry and automatic reconciliation repair are defined. The reference leaves the following narrower parameters to be specified.

17 / SOLUTION DESIGNReal-time operating signals and failure isolation

The pipeline has three distinct backlogs: notifications waiting for transport delivery, accepted inbox work waiting for application, and source/target discrepancies waiting for reconciliation or repair. A single queue-depth chart cannot represent all three. Operations must identify which boundary owns a delayed order before choosing retry, redrive, or repair.

Initial monitoring thresholds

The supplied ingestion design proposes the following starting alert thresholds. They are operational tuning inputs, not measured capacity or contractual availability/freshness commitments. Expected ETL holds should remain visible while being classified separately from unexplained processing delay.

Capture age, queue age, inbox age, apply latency, and reconciliation coverage should be reported separately. After PostgreSQL accepts a notification, a fast-draining SQS queue can hide a slow processor. Likewise, low inbox depth can hide missed capture, which only source coverage and reconciliation can expose.

Platform behavior: Atlas native EventBridge delivery, Atlas trigger coverage and resumption, SQS long polling, and EventBridge rule retries and failed-target handling. These links provide optional platform background; the design and diagrams are embedded in this page.

18 / SOLUTION DESIGNPostgreSQL data architecture

The analytics database is a site-scoped reporting model with its own import workspace and lifecycle evidence. It holds 49 application tables across six schemas, plus two category views. Orders and payments retain normalized business facts; order, payment, CRM and operations summaries serve reporting; ETL metadata controls when a site can accept fresh ingestion and retains a durable event inbox; staging separates unvalidated source data from published facts. These are logical ownership boundaries inside one PostgreSQL database, allowing fact changes, aggregate maintenance and relevant evidence to commit together.

The same model belongs to separate QA and production databases. Turso remains the Ops control-plane and authentication store; its jobs, users, sessions and approvals are outside this PostgreSQL inventory. PostgreSQL migration bookkeeping is also outside the 49 application tables. A PostgreSQL import run records a logical association with its Turso job, but no cross-database foreign key or distributed transaction joins the two stores.

Ownership, readers and writers

ETL Core owns historical normalization, staging, validation, promotion and catchup. Analytics Public API owns online order mutations and the reporting query adapter. Both paths maintain the same fact model and normalization semantics; neither maintains a second reporting database. Database triggers are the immediate writers of most summaries during ordinary mutations. ETL replacement instead rebuilds those summaries in bulk within the publication transaction.

The GKE SQS poller owns durable event acceptance into the PostgreSQL inbox. A separate hydration/apply worker claims that work, reads authoritative source state and invokes the owning ingestion service contract. It does not become another direct writer of reporting facts or aggregate tables. This separates transport availability from source access, site lifecycle gates and business mutation.

Site Master Sync owns source-site mirroring and sync evidence. ETL Core owns import lifecycle changes; Analytics Public API checks those lifecycle controls and records accepted or blocked ingestion. Ops Control Plane reads evidence for operator workflows and performs authorized diagnostic cleanup and site-data removal. Reporting clients reach site-filtered API queries, rather than receiving direct table access.

Tenant isolation combines environment-separated databases with site-scoped query and mutation contracts. Most published fact and aggregate tables do not foreign-key to the mirrored site table. The site lifecycle is therefore an enforced service gate for participating writers, not an automatic relational restriction on arbitrary SQL. Enforced references, authorized service behavior and logical reporting associations are distinct parts of the integrity model.

The catalog uses “API” for Analytics Public API and “ETL” for ETL Core. “Aggregate maintenance” means PostgreSQL triggers during ordinary fact mutation and ETL bulk rebuilding during replacement. Ops diagnostic reads and authorized cleanup apply across ETL and staging evidence; those tables are not customer-facing reporting endpoints.

19 / SOLUTION DESIGNOrders: published facts and reporting summaries

order_schema preserves the order header and its item, discount and charge components. A source order instance has a globally unique identity in this database; service logic also enforces site ownership when reading or modifying it. The item, discount and charge tables have enforced references to the order header. Their references establish parent existence, but do not independently prove that a child's site agrees with its parent. Site agreement remains an application validation responsibility.

Order-source classifications are optional on the underlying order. Channel summaries include an order only when all required classifications are present; this makes them an attributed subset, not an unconditional reconciliation of all sales. Empty source strings remain distinct from absent classifications. Table, waiter, captain and customer breakdowns similarly depend on their source attribution being present.

Discount summaries count discount records, not distinct orders. Item summaries count quantities, which can be fractional. Amounts use integer currency minor units; item value preserves an explicit source amount, including zero, and only falls back to rounded rate multiplied by quantity when that amount is absent. Category views sum item summaries at query time and are ordinary views, not separately persisted materialized views.

20 / SOLUTION DESIGNPayments: transaction facts and collection reporting

payment_schema treats a payment transaction as an independent fact. An order may match several transactions, while a payment carries the logical identity of its associated order. There is no database foreign key from payments to orders. ETL validates order matching before publication, and the API constructs related facts in one transaction. Direct database writes would therefore need to preserve the relationship themselves.

Payment modes and states are constrained vocabularies in the published model; staging retains source text so normalization or rejection happens before publication. Transaction identity must be present and unambiguous. Daily payment periods follow the normalized payment transaction date, which need not be the order's reporting day. Consequently, order counts, payment counts and same-day sales/collection amounts answer different business questions and should not be equated without an explicit reconciliation rule.

21 / SOLUTION DESIGNCRM: purchasing history within a site

crm_schema is a purchase-summary model, not a global customer directory. Customer identity is the mobile identity as represented within a particular site. Matching mobile identities across sites does not merge them into one customer. Orders without customer attribution contribute to sales totals without creating a customer summary.

The association from orders to customers is logical. Neither customer summary is a mandatory parent of an order, and no foreign key links the two CRM tables. This permits summaries to be recreated from retained orders without creating artificial customer-master dependencies. It also means these totals describe the history retained in this analytics system, not necessarily every purchase ever made in the source estate.

22 / SOLUTION DESIGNOperations: restaurant dimensions of sales and preparation

operations_schema contains ten derived tables and no standalone employee, table or kitchen master. Table, primary-waiter and captain summaries derive from order headers. Chef and station summaries derive from item facts. Their labels are source-attributed dimensions, not foreign-key references to workforce or restaurant configuration tables.

“Occupancy” names an order-count/value breakdown by restaurant table; it does not measure occupied minutes, seat capacity or utilization percentage. Chef and station counts are item-row counts, despite legacy aggregate naming that resembles order counts. Their amounts use recorded item amounts; they must not be interpreted as another independent measure of total order sales. These distinctions prevent attractive but invalid cross-chart comparisons.

23 / SOLUTION DESIGNETL metadata: lifecycle, inbox and delivery memory

etl_schema bridges operational orchestration and the published dataset. A site mirror combines source presence with analytics lifecycle state. Import runs belong to sites and retain phase evidence independently of whether staging survives. Site sync has its own run hierarchy because a source scan can update many sites without performing an analytics import.

Run-owned evidence uses enforced references and generally cascades when its owning run is removed. Composite run/site relationships on failures, cleanup and staging prevent evidence from being attached to a run belonging to another site. A site's optional last historical run also has an enforced same-site reference. This creates a deliberate dependency in both directions: removing a referenced run requires handling the site's historical pointer first.

Business receipts deliberately stand outside this deletion tree. They have no foreign keys to orders, sites or import runs, and no cascading relationship to any of them. Under the direct-create contract, a repeated accepted delivery returns its original response without recreating a subsequently deleted or replaced order. Reusing the delivery identity for changed canonical content produces a conflict. Receipt-based replay and explicit restoration from current authoritative sources are therefore different operations.

Transport acceptance is not business application

The inbox is PostgreSQL's durable acceptance record for an event delivered through Atlas Orders Trigger, EventBridge and SQS. The poller acknowledges SQS only after the inbox insert or verified duplicate acceptance commits. After that boundary, PostgreSQL owns retryable work even if the hydration worker is unavailable or the site is temporarily blocked. The SQS acknowledgement proves a durable handoff; it does not prove the order has been applied.

The inbox and business receipts have complementary responsibilities. The inbox answers whether an event was received, who currently owns processing, when it can retry and how processing ended. A business receipt answers which ingestion operation was durably accepted and supports safe replay of that outcome. An applied inbox outcome must refer logically to a confirmed business result; it cannot be inferred from successful message receipt alone. Neither table is a substitute for a raw-source archive.

The inbox has no promised foreign keys to sites, orders, runs or receipts. These are logical associations so transport acceptance can survive absent facts and independent lifecycle changes. Worker claim ownership, lease renewal and expiry, per-order revision precedence, and repeatable hydration of order/payment state are required target contracts. Source state older than the event's required revision must be retried; an old event must not overwrite a newer applied revision. A changed source reread must not silently change the payload behind an already accepted business delivery identity.

24 / SOLUTION DESIGNStaging: a validation workspace scoped to a run

staging_schema is the boundary between source interpretation and published analytics. Every table belongs to an import run; each staged row is constrained to that run's site. The staged facts intentionally do not foreign-key to each other. This lets extraction retain incomplete or invalid source combinations long enough to explain them, rather than losing diagnostic context at the first relational rejection.

Raw records, staged orders and staged payments are matched by source and business identity during validation; those matches are logical, not foreign-key joins. Validation checks raw-to-staged population parity, payment identity presence/uniqueness, cross-site order collisions and payment/order association. During catchup, payments for source orders outside the extracted eligible population can be deferred with evidence, rather than published as unmatched facts.

Staging is incrementally populated. Bulk copy can fall back to individual inserts that retain successful rows and record row failures, so partially populated staging is an expected diagnostic state. It is not a partially published dataset. Promotion refuses blocking validation or ETL failures, and published facts remain unchanged when validation fails.

25 / SOLUTION DESIGNConsistency, retention and schema evolution

Publication and online mutation

Four trigger families maintain order, item, payment and discount summaries. Inserts add a contribution; deletes subtract it; updates subtract the previous contribution and add the replacement. They execute in the fact transaction, so rollback removes both fact changes and their aggregate effects. Empty contribution groups are pruned by count or item quantity. Foreign keys do not cascade order composition deletion: the order-deletion routine explicitly removes payments, charges, discounts and items before removing the header.

A confirmed reopen, rejection or source tombstone soft-voids a previously ingested order: its audit record remains, while its contribution is excluded from all affected order, item, payment, customer and operational reporting in the same transaction. Repeated voids contribute no further subtraction. Reclosure and amendments replace the relevant contribution exactly once under source-version and receipt guards. A logical void is therefore a reporting transition with atomic aggregate maintenance, not merely a flag that leaves totals unchanged.

Historical replacement acquires the site's exclusive transaction lock, suppresses per-row aggregate triggers locally, replaces supported facts and customer summaries, then rebuilds derived reporting tables before committing. The suppression is transaction-scoped. Existing readers see a committed dataset, while participating writers cannot interleave with publication. Staging cleanup and its evidence commit with successful promotion. This atomic publication boundary does not imply that extraction, every catchup pass and final lifecycle transitions are one long transaction.

Catchup uses bounded passes and ordinary delta maintenance under the same exclusive site lock. A pass with more than 1,000 staged orders fails closed and requires full replacement. Source-window overlap, extracted/promoted populations and net changes are recorded; a zero-net pass allows the site to become live. Failure after historical publication preserves the committed dataset but keeps fresh ingestion blocked pending recovery.

Online mutations take a shared site lock, serialize aggregate changes within the site, and lock the order identity. Fresh ingestion rechecks lifecycle eligibility within its transaction. For direct-create ingestion, the order, supported children, payment facts, trigger updates, accepted-ingestion evidence and business receipt commit atomically. A matching prior receipt is replayed before requiring fresh-write eligibility. Bounded retries cover database failures known to abort the transaction; an uncertain commit is resolved through the receipt before another write is attempted.

The provider-owned revision-aware contract handles creation, correction, replacement and soft void as distinct recorded outcomes. Facts, source-version protection, aggregate effects and the business receipt commit together; inbox completion follows as a recoverable handoff. A worker resolves the committed result before retrying an uncertain attempt or marking inbox work applied. A crash between business application and inbox completion converges through outcome recovery without duplicate aggregate effects.

These guarantees operate per database transaction. Independent reporting requests may observe different committed moments; the API does not promise one shared snapshot across an entire dashboard assembled from multiple calls. Likewise, Turso job transitions and PostgreSQL publication are coordinated through services and evidence, not a cross-store atomic commit.

Retention serves distinct purposes

Successful historical or catchup publication removes raw source records and staged candidate facts for the run, and records cleanup accounting. Validation observations, run events, entity counts, catchup-pass history and failure records remain diagnostic evidence. Failed or interrupted work can retain staging for explicit investigation and authorized terminal-run cleanup. There is no universal age-based staging expiry established by these migrations.

Failure payload pruning removes sensitive raw payload content while retaining the failure record and recording the pruning action. Receipts survive fact replacement, deletion and application rollback, but store delivery proof and a response rather than a complete raw-source archive. Neither staging, thin-event inbox records nor business receipts supply a durable historical restoration archive. Inbox cleanup must preserve unfinished work, replay deduplication and the required outcome history; an age-based inbox policy is not assumed. Long-term fact retention, source-archive access and restoration policy remain explicit architecture decisions.

Migration ownership and forward preservation

PostgreSQL application migrations use tern under the environment-specific database owner role. Runtime roles receive data access separately and do not own schema evolution. Schema, table, sequence and function access is removed from the generic PostgreSQL public role; default privileges also prevent later objects from reopening that access. Service ownership is therefore enforced through repository boundaries and credential/role configuration, rather than a different database per service.

The ordered migration history first establishes the six-schema model. Its original canonical baseline includes a destructive reset of those application schemas and must be treated as an initialization boundary, not a reusable refresh procedure. Subsequent migrations correct ISO weekly grouping and reconcile retained totals, add durable ingestion receipts, and revoke legacy public access. The weekly repair takes explicit table locks, uses a bounded lock timeout and commits the repaired function and data together.

Weekly correction, ingestion receipts and access revocation are deliberately forward-only. Their down migrations refuse to discard repaired totals, erase delivery memory or reopen broad access. Application rollback therefore preserves these database guarantees. The inbox is added through an additive migration that preserves facts and receipt history. Claim leases, revision consistency and logical-void eligibility are maintained within the inbox and ingestion contracts; the table-level model does not prescribe additional business tables. Future schema changes must respect retained data and the compatibility of ETL, API and inbox workers; re-running the canonical reset would violate that preservation model.

26 / SOLUTION DESIGNDeployment architecture

The solution runs on the regional GKE Autopilot cluster anahrms-prod in asia-south1, within the analytics-hrms Google Cloud project. The target operating model combines continuously running application services on regular ARM64 capacity with finite ETL import Jobs on ARM64 Spot capacity. Real-time ingestion adds two independent regular-capacity Go services: an SQS poller that persists messages into PostgreSQL, and an inbox processor that hydrates source data and invokes the ingestion domain. A shared public Gateway serves the customer application and operator interface, while Kubernetes administration remains private. Cloud SQL provides the PostgreSQL data plane; remote Turso provides application control, configuration, and ETL queue coordination.

This architecture describes the complete operational system. Replica controls, claim switches, stage-zero manifests, and controlled activation candidates are operational mechanisms for pausing, draining, releasing, and recovering that system. They are not a second architecture. The regional cluster and database are shared infrastructure, so environment separation is enforced at several layers rather than inferred from separate hardware.

Environment and namespace boundaries

QA and production use separate PostgreSQL databases and owner/runtime roles on the same regional HA Cloud SQL instance, anahrms-prod-pg18. Their data and SQL permissions are isolated, but CPU, memory, storage, maintenance, backups, and instance availability remain shared. A QA experiment must therefore respect the instance-wide consequences of connection pressure, expensive queries, or infrastructure changes. Turso is a shared remote control plane with environment-scoped records; it is not modeled as one isolated Turso server per namespace.

OpsUI's control-plane environment is prod, while data-plane authority is configured independently through allowed targets and the selected SQL identity. An operator's application role, the requested environment, target allowlisting, and database privileges must agree before an action proceeds. Moving a service into the Ops namespace or authenticating successfully to OpsUI does not remove those boundaries.

MongoDB Atlas native triggers deliver source changes to EventBridge, which routes them into the approved SQS Standard queue. The poller pulls from SQS and commits a durable PostgreSQL inbox entry before acknowledging transport delivery. The separate processor claims inbox work, reads the authoritative MongoDB order and DynamoDB payment data, and calls the existing ingestion service. There is no public ingestion webhook and no MongoDB hydration inside the poller. Independent reconciliation checks source truth against persisted Analytics results so event capture and queue delivery are not the only detection paths for divergence.

Foundation and failure domains

The foundation uses a custom VPC, private GKE nodes, separate node/Pod/Service address ranges, Private Google Access, and Private Service Access for Cloud SQL. A regional Cloud Router and Cloud NAT provide outbound connectivity for private workloads that call external systems. NAT addresses are allocated by the foundation and are not an architectural promise of a fixed source address. The Kubernetes control plane uses an IAM-protected DNS endpoint with IP-based endpoints disabled; private nodes do not imply that every authorized administrative client must originate inside the VPC.

Cloud SQL uses PostgreSQL 18 with regional HA, IAM authentication, backups, point-in-time recovery, and deletion protection. These protect different failure modes: database HA addresses an instance failure, backups and PITR support data recovery, and deletion protection guards resource removal. None establishes an application recovery objective by itself. Replica counts, topology-spread policy, disruption budgets, workload autoscaling, SQL connection budgets, and business availability targets require explicit capacity decisions; this design does not invent an SLO, a worker maximum, or a guaranteed monthly cost.

27 / SOLUTION DESIGNPublic routing, TLS, and network boundaries

The named global public address is attached to the analytics-public Gateway, using the managed global external GKE Gateway class. Cloud DNS hosts the delegated Analytics zone. Certificate Manager supplies hostname certificates through a certificate map, with DNS authorization records supporting managed issuance. The Gateway exposes HTTPS on port 443 and uses HTTP on port 80 for redirects to HTTPS. Certificate authorization records and traffic-address records have distinct purposes and must both match the intended hostname contract.

The more specific API route takes precedence over the frontend root route. The browser therefore addresses /api/<site>/…, while the Go service retains its /site/<site>/… transport contract. This preserves the existing backend URL structure behind a coherent public origin. Legacy /newdashboard frontend entry points redirect to the root; missing versioned assets return 404 rather than a misleading HTML page. Neither deployment depends on a new subpath-hosting convention.

Gateway health policies check frontend readiness on its HTTP listener and API readiness on the API management listener. Kubernetes separately uses startup, liveness, and readiness probes. Liveness indicates a process can continue; readiness indicates the dependency and consumer conditions required for useful service. Background consumers can remain alive during a controlled pause while their application readiness reports that consumption is unavailable. The management listener is not a general public administration API.

Default-deny with explicit operational egress

Application namespaces use default-deny ingress and egress, then add the flows required by their workloads. Gateway and Google health-check source ranges may reach the frontend, API, and OpsUI listeners appropriate to their namespace. Ordinary Pod-to-Pod access is not implicitly allowed simply because both Pods belong to Analytics. This matters because the main application domains communicate through their in-process APIs or durable control/data stores; the system does not require an unrestricted service mesh between every process.

Egress permits DNS, the GKE metadata endpoint used for workload identity, outbound HTTPS, and the private Cloud SQL proxy destination. The SQS poller requires HTTPS to SQS and AWS STS plus its PostgreSQL path. The inbox processor requires MongoDB, DynamoDB, AWS STS, and PostgreSQL; it does not consume or acknowledge SQS messages. MongoDB TCP 27017 access is granted to the processor, approved reconciliation execution, and ETL source readers, rather than inherited by the poller. QA harness source access remains scoped to its own workload label.

The HTTPS rule is intentionally broad at the network-policy layer: Turso, Google APIs, GitHub, Cognito/AppSync, SQS, STS, DynamoDB, and other approved HTTPS dependencies still require application authentication and authorization. These policies are port and IP controls, not an asserted FQDN allowlist or a guarantee that every TLS destination is trusted. EventBridge publishes into SQS within AWS; GKE initiates its long-poll requests over outbound HTTPS, so the ingestion path adds no public listener.

PostgreSQL clients connect to a Cloud SQL Auth Proxy in the same Pod at loopback port 5432. The proxy uses private IP and automatic IAM database authentication, and opens its encrypted upstream connection to the Cloud SQL private address on port 3307. The proxy obtains identity from the workload service account; applications do not carry a PostgreSQL password. A proxy provides authentication and transport security, but it cannot create missing VPC reachability. Database access requires both the private route and the correct IAM/database grants. No public SQL address is needed for application execution or reviewed migration Jobs.

28 / SOLUTION DESIGNWorkload placement and bounded execution

Application images target linux/arm64. Regular application workloads select the analytics-arm ComputeClass, whose defined machine type is regular c4a-standard-2. ETL import Jobs select analytics-etl-spot, which requires the same machine type on Spot capacity. Both classes refuse scale-up through an unspecified fallback when their requirement cannot be satisfied. The application placement contract does not silently become an all-node architecture promise for GKE-managed system components.

The public API, frontend, and OpsUI are long-lived Deployments. Their rolling-update behavior allows new ready Pods to replace old instances while retaining service endpoints. Scheduler, site synchronization, and dispatcher Deployments use Recreate to avoid retaining an older active claimant during replacement. One dispatcher coordinates each environment. It reads durable queue state, creates finite Kubernetes Jobs, and reconciles execution ownership; it does not run the import inside its own long-lived process.

Independent streaming consumers

The SQS poller and inbox processor are separate long-running Deployments on analytics-arm. They have separate process lifecycles, readiness, credentials, and concurrency controls. The poller performs bounded long-poll receives and durable inbox insertion, then deletes only the delivery covered by a successful commit. Processor delays and expected ingestion gates do not extend transport acknowledgment indefinitely because PostgreSQL owns the accepted work. Conversely, a poller restart cannot erase accepted inbox work, and a PostgreSQL commit failure leaves the message available for redelivery.

Scaling follows two backlogs. SQS depth and oldest-message age describe transport pressure; pending inbox depth and oldest eligible work describe processing pressure. Increasing pollers can drain SQS while increasing PostgreSQL backlog, so queue scaling must include inbox admission and database connection limits. Processor scaling must respect source read capacity, PostgreSQL throughput, idempotency, and concurrent work ownership. These signals define a queue-aware scaling policy; this architecture assigns no arbitrary replica count, autoscaler product, or threshold.

Graceful shutdown stops receiving or claiming new work, then completes or relinquishes owned work within its delivery/claim contract. Independent source reconciliation runs as Kubernetes CronJobs: changed-data comparison every 15 minutes with a two-hour overlap, and a daily previous-day check after business closure. Each uses bounded execution, overlap, retry, and resource controls. Its comparisons and repairs remain independent of message transport, while sharing ingestion rules and source-access boundaries. The detailed reconciliation windows and repair semantics belong to the ingestion design. Ordinary streaming and reconciliation execution are distinct from Spot ETL imports and their manual whole-import retry policy.

Finite import execution

The environment's configured concurrency cap controls the number of active imports, with one active import per environment/site. Setting that cap to zero pauses new imports while reconciliation continues. Disabling claims stops the consumer loop and is therefore a different control. Neither action terminates an already running worker. A safe import drain sets admission to zero, retains the reconciliation path needed for existing attempts, and waits for fenced terminal ownership before releasing execution slots or replacing infrastructure.

Import workers are finite Jobs with no automatic whole-import retry. Their image, service account, target environment, namespace, and ComputeClass are supplied explicitly by the dispatcher contract. Site deletion uses regular ARM capacity rather than the Spot import class. Worker resource requests include application memory, local scratch storage, the SQL proxy, and platform overhead; fitting an image in the registry does not demonstrate that a Job fits the node's allocatable resources. Concurrency and resource sizing are therefore coordinated with Cloud SQL capacity instead of treated as independent settings.

Attempt ownership persists beyond a Pod. The control plane records the attempt, Kubernetes Job and Pod identities, underlying VM identity, selected image, heartbeat/lease, and cancellation/finalization state. A missing Pod, expired lease, or forced delete is insufficient proof that a database writer has stopped. Reconciliation fences writer authority before a slot is reused. Spot preemption is classified only when the durable attempt-to-VM mapping matches completed GCP preemption evidence in the terminal observation window. Missing, late, mismatched, or unreadable evidence produces a generic failure; an operator decides the rerun.

Containers run without root privileges, with read-only root filesystems, dropped Linux capabilities, and explicit writable temporary or scratch volumes. Frontend execution uses an unprivileged Nginx account; Go services use a non-root runtime account. Ordinary application Pods disable Kubernetes API token automount. The dispatcher is the deliberate exception because its job is to call the Kubernetes API using constrained RBAC.

29 / SOLUTION DESIGNIdentity and configuration bootstrap

The architecture separates runtime access, schema ownership, job orchestration, observation, and publication. GKE Workload Identity binds an explicit Kubernetes service account to an explicit Google service account. The Kubernetes namespace is part of that trust relationship. A service-account name alone is not sufficient evidence of access, and credentials are not shared merely because two workloads run in the same cluster.

The dispatcher uses namespace-scoped permissions to create, read, watch, patch, and delete Jobs, plus read access to Pods. Cluster scope is limited to nodes.get; nodes.list is deliberately absent. Its Google custom role contains only compute.instances.get and compute.zoneOperations.list for preemption attribution. The worker it starts uses the target environment's runtime identity, keeping orchestration authority separate from data-writing authority.

Google-to-AWS federation

Each cross-cloud workload uses its namespace-bound Kubernetes identity to obtain a Google service-account OIDC token for the approved audience, then calls AWS sts:AssumeRoleWithWebIdentity. The role trust names Google as the federated principal and uses exact comparisons for the immutable service-account subject and intended audience. AWS maps accounts.google.com:sub to token sub, accounts.google.com:oaud to token aud, and accounts.google.com:aud to azp when present, otherwise to aud. The trust must bind those actual claims correctly; a service-account email or an unrestricted Google issuer is insufficient. See AWS OIDC condition-key semantics.

The poller role grants sqs:ReceiveMessage, sqs:DeleteMessage, sqs:ChangeMessageVisibility, and sqs:GetQueueAttributes only on its approved queue. The processor's separate role grants only the DynamoDB read actions and table/index scope required for payment hydration. Credentials are short-lived, refreshed before expiry, and held outside manifests and browser configuration. QA and production bind their own principals and resources; the queue ARN, region, role ARN, audience, and allowed Google subject are reviewed configuration, not guessed constants. MongoDB access continues through the approved Turso-managed connection contract.

Three configuration layers

Bootstrap configuration contains the remote Turso database URL and a token-file reference. The GKE Secret Manager CSI driver mounts the approved token version into each authorized Pod using its workload identity. The application reads the exact URL key and token-file setting, validates both, and connects to Turso before loading application constants and connection records. Mounted bootstrap mode skips local dotenv files. The normal runtime does not use an embedded control database or local source-credential files.

Reviewed runtime ConfigMaps supply nonsecret infrastructure controls: environment, allowed targets, listeners, proxy endpoint, worker image, execution mode, namespace, service account, and compute placement. Turso supplies business, authentication, and environment-specific source/target configuration. Its connection values are operator-managed plaintext protected by the database access boundary; the retired encryption-key mechanism is not part of startup. This distinction prevents a Kubernetes ConfigMap from turning into an uncontrolled copy of OAuth secrets, source credentials, or database connection payloads.

Token rotation is a coordinated lifecycle operation. A pinned Secret Manager version does not automatically track a new version, and mounted-file changes do not rebuild clients already holding credentials. Rotation therefore updates the approved reference, restarts affected consumers in a controlled order, verifies their operation, and revokes the old credential after the required cutovers. Secret values never belong in GitOps manifests, browser configuration, retained CI logs, or release summaries.

30 / SOLUTION DESIGNBuild, publication, and GitOps delivery

Three repositories divide the release responsibilities. The backend repository owns the Go services, workers, migration executable, and verification harnesses. The frontend repository owns the React/Vite bundle and its Nginx image. slim-depl owns the reviewed deployment composition and platform definitions. GitHub is the source, review, and workflow system; Artifact Registry is the immutable runtime artifact store. Historical binary archives and GitHub Releases are not runtime distribution or rollback dependencies.

Source and runner trust

Development and QA preparation take place on qa. Production source reaches main through a reviewed qa → main pull request using rebase-and-merge; direct pushes to main are prohibited. QA image workflows are manually dispatched from the canonical QA branch and default to build-only behavior unless publication is selected. Production workflows run from pushed release tags and require an annotated SemVer tag selecting the exact current origin/main commit and checked-out source. Tags are immutable release identities and are never moved or reused after a failed attempt.

A GitHub-hosted preflight validates repository, source/ref, selected scope, and runner configuration before a self-hosted build is scheduled. Runner administration verifies group membership, repository access, and the required Linux/build labels independently of the workflow. The SSD host may perform Analytics CI builds and image publication only. It is not an application host, a deployment-control host, or a GKE runtime dependency. Publisher authentication uses temporary OIDC-derived access tokens.

QA federation accepts the canonical immutable GitHub owner/repository identity on refs/heads/qa. Production has separate backend and frontend Workload Identity Pools, each containing its own provider, so a sibling provider cannot reuse a repository attribute to reach another component's publisher. Production trust requires the expected immutable IDs, a tag ref type, and a refs/tags/v prefix. Workflow validation adds the stricter annotated SemVer and exact-main checks. Each production publisher has repository-local Artifact Registry writer access and its own WIF principal binding, without direct project roles.

The QA-to-production denial workflow exercises the trust boundary explicitly: a QA token must be rejected by the production provider's attribute condition before publisher impersonation. A timeout, malformed provider reference, or later IAM denial is not equivalent proof. Build identity, publisher identity, cluster workload identity, and GitOps reconciliation identity remain separate chains even though their artifacts participate in one release.

Image inventory and frontend configuration

Backend publication verifies generated source and all nine Go workspace modules before building ARM64 images with the pinned Go/ko toolchain. Its build families cover OpsUI, public API, ETL dispatcher and worker, scheduler, site synchronization, migration, and environment-specific verification harnesses. The release inventory includes the independently deployable SQS poller and inbox processor and the image used by the reconciliation CronJobs. Each image has an explicit service owner and a digest recorded in the release inventory.

Release validation requires every artifact used by the selected environment manifests, including both streaming consumers, reconciliation, the frontend, and the appropriate verification harnesses. The inventory covers continuously running Deployments and finite work: ETL workers, reconciliation Jobs, migrations, and harnesses all retain an immutable executable identity.

The backend emits immutable digests, OCI source revision labels, and software bills of materials. QA uses the source SHA as the publication tag; production adds the production suffix to that full-SHA identity. Runtime manifests pin digests, including the dispatcher setting used to create worker Jobs and the detached harness/migration images. The SQL proxy and frontend base images are also pinned. A tag helps identify a release; the immutable digest selects executable content.

The frontend build installs dependencies from its lockfile, runs its tests, compiles Vite, and packages the bundle in a digest-pinned unprivileged Nginx runtime. Its public build inputs select AWS region, Cognito user pool and browser client, AppSync endpoint, authentication mode, and the Analytics base URL ending in /api. QA and production use different Analytics URLs while sharing the approved Cognito/AppSync authentication plane. These values are public browser settings, never service credentials. They are embedded into the bundle: changing Pod environment variables cannot retarget an already built frontend image.

QA obtains its public inputs from repository variables; production's approved values are encoded and validated by its workflow/build contract. Each frontend inventory retains a checksum of that public configuration alongside source identity and image digest. Changing the API origin or authentication plane therefore requires a new reviewed build, not an untracked runtime edit. Backend and frontend release identities are independently recorded and combined explicitly by GitOps staging.

31 / SOLUTION DESIGNPromotion, operational ownership, and recovery

Release staging consumes separate clean-source, published backend and frontend inventories. It validates environment, architecture, required image set, source/tag provenance, route contract, and relevant execution controls, then produces a checksummed private candidate. Promotion verifies that candidate against the expected clean GitOps source and writes the reviewed image pins locally. Staging and promotion do not publish another image, apply Kubernetes resources, or sync Argo. The resulting GitOps diff follows the same QA review and production rebase-promotion process as source changes.

A release identity binds the source repositories and full commits, immutable image digests, frontend public-configuration identity, GitOps revision and manifest path, rendered-manifest checksum where available, and validation results. Recording all of these makes recovery reproducible across independently versioned repositories. A successful compilation, registry push, manifest render, Kubernetes readiness check, and authenticated product check establish different properties. The release process retains each relevant result without treating one as a substitute for the others.

Manual reconciliation and resource ownership

Argo reconciles application Deployments, Services, runtime ConfigMaps, reconciliation CronJobs, and approved HTTPRoutes/health policies from the selected GitOps revision. Sync remains manual, namespace creation remains disabled, pruning remains disabled, and shared-resource conflicts fail the sync. Production application templates follow the release branch; a deliberate exact revision pin records a selected deployment and must be preserved when updating Application metadata. AppProjects constrain repositories, destination namespaces, and accepted resource kinds, including the application-owned reconciliation CronJobs. Their bounded service-account permissions and scheduling controls are reviewed with the application manifests. Jobs generated by these CronJobs follow the normal scheduled-work lifecycle and its retention policy; they are distinct from the manually executed migration and verification Jobs described below.

Platform ownership covers namespaces, GCP/Kubernetes identities, AWS federation trust, RBAC, Secret Manager/CSI bootstrap, ComputeClasses, the shared Gateway, DNS/TLS infrastructure, and platform network policy. Atlas trigger delivery, EventBridge routing/archive, SQS queues and dead-letter handling, and their resource policies are reviewed infrastructure configuration with explicit owners. In particular, the production policy bundle is managed as platform configuration outside the application tree; the ability to authorize NetworkPolicy resources in an AppProject does not establish ownership of that bundle. Reviewing ownership prevents an application sync from taking control of infrastructure or credentials that another process manages.

Migration, QA E2E, production read-only smoke, and ledger-inspection Jobs are detached from the Argo application trees. Their separate, digest-pinned execution prevents a routine sync from unexpectedly mutating data or replaying a one-shot operation. QA E2E uses approved real source scope against the QA database. Production smoke uses the read-only harness, and ledger inspection uses a read-only command that does not initialize a missing ledger table. The smoke harness enforces read-only behavior while its environment runtime SQL identity retains normal application privileges. A migration-capable identity does not turn these observer executions into schema-change authorization.

Pause, restore, and rollback

Controlled stage-zero and interface-only candidates provide deliberate activation and recovery boundaries. Their validators constrain the allowed changes, retain exact source/configuration receipts, and reject unexpected namespace, route, image, identity, target, or consumer-setting drift. These tools describe bounded operational transitions; they are not general-purpose automation for enabling every background consumer. A broader operational change needs its own reviewed candidate that preserves the same provenance and ownership invariants.

Recovery begins by identifying an exact compatible image, configuration, schema, and GitOps receipt. Stop new work through the appropriate admission control, preserve dispatcher reconciliation, and establish that active writers are safely fenced before replacing consumers. Restore the intended manifests through a reviewed change and manual Argo sync, then verify routing, readiness, identity, and the affected user/data behavior. Recovery tooling that saved an activation baseline restores that exact baseline rather than synthesizing a generic rollback from memory.

Application rollback and database rollback are separate decisions. Restoring an earlier image does not reverse a schema migration or repair data, and automatic schema Down execution is not a release recovery strategy. Likewise, changing DNS or deleting broad IAM/database resources is not a substitute for restoring an exact release. Finite Jobs require identity-aware reconciliation and cleanup; pruning a deployment tree must never become an accidental worker-cancellation mechanism. This preserves a recoverable operational system across code, infrastructure, and data changes.

Operate and recover

32 / SOLUTION DESIGNObservability and operational control

The observation model combines process probes, Kubernetes workload state, structured application logs, durable job/run events, operator audit, and cloud infrastructure telemetry. These signals answer different questions. Liveness asks whether a process is responsive; readiness asks whether it should receive work; a completed ETL run asks whether a governed data transition finished; reconciliation asks whether the resulting facts and summaries agree.

The foundation defines infrastructure logging and metrics. An application-wide trace collector, metrics backend, or automatic distributed tracing pipeline is a separate design choice; the document does not imply one from the existence of logging or telemetry responsibilities. End-to-end tenant availability, latency, freshness, backup age, and alert delivery need explicit measurement and response ownership.

Safe diagnosis

Routine output should expose a bounded reason class, service, phase, environment, status, and approved correlation evidence. Authentication failures must not expose tokens, headers, claims, identities, or verifier internals. Source payloads and ETL failure details belong behind role checks and audit. Detailed investigation can follow a job to its PostgreSQL run and Kubernetes attempt through authorized tools without putting customer data into a general architecture or release document.

Health checks and business correctness are complementary. A ready API can still serve an incorrect aggregate if data transformation is wrong. A green import count can coexist with a failed browser authentication path. Monitoring should therefore follow both the request path and the data lifecycle.

Incident response by failure boundary

33 / SOLUTION DESIGNRecovery, retention, and lifecycle management

Application rollback, database restoration, and business-data replay are different operations. Rolling back an image changes executable behavior. Restoring Cloud SQL changes durable state to a selected recovery point. Re-extracting authoritative sources rebuilds business facts under the ETL rules. None is a universal substitute for the others.

Database resilience

The database foundation uses PostgreSQL 18 on a regional HA Cloud SQL instance with private connectivity, IAM authentication, automated backups, point-in-time recovery, storage-growth controls, and deletion safeguards. QA and production databases share this instance. The shared foundation is therefore a common resource and recovery boundary even though application identities and databases are separated.

Backup count, retention windows, maintenance scheduling, and final-backup policy are controlled platform settings. An available backup is not a measured recovery objective. Recovery time and acceptable data loss must be agreed, then verified with an isolated restore and application-level reconciliation. Regional HA addresses a regional deployment's instance/zone failure modes; a cross-region disaster-recovery architecture requires a separate decision.

Restoring PostgreSQL also rewinds its inbox. Messages acknowledged after the selected restore point may already have disappeared from SQS. Recovery must therefore account for that interval through retained notification replay and independent source reconciliation; queue redelivery alone cannot recover every accepted inbox entry. Receipts and source versions must be reconciled before replay is allowed to change restored facts.

Restore sequence and cross-store reconciliation

1. Define the incident boundary, required restore point, compatible application/configuration identity, and recovery owner. Freeze relevant new mutations while preserving worker reconciliation.
2. Establish that affected writers have stopped or lost their write authority. Preserve queue, run, attempt, and audit evidence needed to explain the incident.
3. Restore into an approved isolated destination and inspect schema compatibility, business totals, tenant separation, receipts, and site lifecycle state.
4. Reconcile the restored PostgreSQL state with Turso jobs and release/configuration records. Classify attempts whose queue state is newer than the data restore point.
5. Resolve source-backed repair or replay under the retained ingestion and ETL rules. Explicitly account for previously accepted operations and their receipts.
6. Perform a controlled endpoint/connection cutover, verify authenticated reads and governed writes as applicable, and resume consumers only after the recovery state is coherent.

Data retention and cleanup

Successful historical promotion and completed catchup passes have targeted staging cleanup behavior. Retained validation failures, source captures, and pass evidence serve diagnosis; their disposal is a governed action. Ingestion receipts intentionally survive order deletion and replacement so that an old accepted delivery does not recreate facts. Audit history and release/recovery records have a different purpose again.

A universal age-based purge, durable source archive, replay retention period, and restoration SLA are not defined by the schema alone. Their policies must specify data ownership, privacy and access, retention duration, deletion behavior, and reconciliation after restore. A receipt stores an accepted outcome and identity; it is not a raw-source archive.

Schema migration follows its own compatibility plan. Later PostgreSQL repairs preserve corrected weekly summaries, receipts, and revoked broad access during application rollback. Routine recovery must not reverse those safeguards through a destructive schema downgrade.

34 / SOLUTION DESIGNCapacity, performance, and cost model

The design separates interactive reads from finite import workers at the process and scheduling layers, while both ultimately consume the shared database. ARM64 application placement and Spot imports establish a cost/placement strategy, not a demonstrated throughput guarantee. Capacity is bounded by source access, normalization, staging volume, database write/lock pressure, catchup convergence, API query patterns, and concurrent workload mix.

Shared connection budget

Peak database connections = API replicas × API pool size + active ETL workers × worker pool size + poller replicas × inbox-write pool size + processor replicas × ingestion pool size + connections for reconciliation, sync, operations, migrations and harnesses + recovery headroom.

The budget is evaluated across both environment databases because their instance is shared. A local Cloud SQL proxy provides authentication and transport; it does not remove application connection pressure or provide a global query pool. A replacement transaction can affect latency through locks even when connection limits are respected.

What must be measured

Measure tenant request latency by report and date range, import duration by source volume, per-phase ETL time, catchup convergence, transient scratch growth, peak resident memory, source throttling, database locks/connections, and API behavior while imports run. A synthetic benchmark or candidate index is evidence for a specific experiment, not proof that an index is part of the production schema or that a tenant SLO is met.

Single-component replica configuration is not an end-to-end availability guarantee. Replica counts, disruption budgets, topology spreading, autoscaling policy, and cross-region recovery must follow agreed availability and recovery objectives. The architecture does not choose numerical targets that the product and operations owners have not specified.

Cost ownership

The cost model includes regular application capacity, Spot worker runtime, regional HA Cloud SQL, storage and backups, load balancing, NAT, registry storage, network traffic, logs/metrics, Turso, source-system reads, Atlas trigger execution, EventBridge routing/archive/replay, SQS and dead-letter queue requests and retention, and CI capacity. Track shared platform costs separately from incremental per-environment and per-import costs. A budget decision must identify its scope, observation window, and response to excess cost; an inexpensive worker alone does not establish an inexpensive system.

35 / SOLUTION DESIGNEngineering verification and acceptance model

Verification follows the architecture's boundaries. Unit checks prove local behavior, database integration proves transactional behavior, environment tests prove deployed composition, and browser checks prove the actual identity and routing path. Evidence remains tied to the source, configuration, image, and environment that produced it.

Release review should connect these conclusions rather than reduce them to a single green status. A healthy Pod does not establish correct data; a passing data harness does not establish tenant isolation at the public Gateway; a successful image publish does not establish a reversible deployment.

36 / SOLUTION DESIGNArchitectural decisions requiring explicit contracts

The operational target is described above. The following topics remain design questions in the source contracts. They are included for engineering and CTO decisions, not as a live deployment status list. Defining them would change product behavior, risk tolerance, or platform responsibility and therefore requires an explicit decision.

Design basis

Prepared from the Analytics Reporting Engine, Analytics Admin UI, and Analytics GitOps source and their accepted domain/deployment contracts on 6 October 2026. The real-time design also incorporates the supplied order-event architecture and the approved Atlas producer, separate poller/processor, and SQS Standard decisions. Table inventory combines the canonical PostgreSQL model with the target inbox; service contracts distinguish direct-create receipts from revision-aware application and recoverable inbox completion. Infrastructure and delivery descriptions come from maintained manifests, workflows, and bootstrap contracts. This page contains the explanations and diagrams needed to read it independently; it does not require repository access or external assets.

37 / SOLUTION DESIGNShared vocabulary