System Architecture

Repository shape#

One pnpm workspace (Node 26, TypeScript strict with noUncheckedIndexedAccess, Turborepo for the task graph), split into applications and versioned workspace packages. Business rules, schemas, formatting, and connector logic live in packages; the web app stays thin.

Applications. apps/web is Next.js 16 App Router: React Server Components for read-heavy surfaces, client components only for interactive islands, and a tRPC v11 API (Zod input schemas, superjson transport) with one feature router per domain under a single root. apps/worker runs BullMQ 6 over Redis with named per-concern queues and declarative Job Schedulers (cron patterns and fixed intervals, all pinned to Asia/Ho_Chi_Minh). Two smaller services exist: a Python forecasting service and an MCP server.

Packages. @yng/schema owns the Drizzle ORM model over Postgres 16, migrations, RLS policy SQL, and the tenancy runtime. @yng/metrics holds metric definitions and materializers. @yng/connectors-* is one package per source plus a shared core. @yng/ai is the only path to a language model. @yng/ui, @yng/charts (ECharts), and @yng/i18n cover presentation, and @yng/alerts, @yng/plugin-host, @yng/crypto, @yng/eventbus, @yng/storage, and @yng/exports cover the remaining cross-cutting concerns.

A Cube semantic layer sits beside the app for governed ad-hoc queries. Its schema is generated from the Drizzle model by a script and checked for drift in CI, so it can never describe a table shape that no longer exists.

flowchart TB
  MW["Edge middleware<br/>host classification, request origin"]
  subgraph APPS["Applications"]
    direction LR
    WEB["apps/web<br/>RSC pages + tRPC v11"]
    WRK["apps/worker<br/>BullMQ 6 queues + promoters"]
    CUBE["Cube semantic layer<br/>(generated schema)"]
  end
  subgraph PKG["Workspace packages"]
    direction LR
    SCH["@yng/schema<br/>model, RLS, tenancy"]
    MET["@yng/metrics"]
    CON["@yng/connectors-*"]
    AI2["@yng/ai"]
    PH["@yng/plugin-host"]
    UI["@yng/ui · charts · i18n"]
  end
  subgraph DATA["Stores"]
    direction LR
    PG[("Postgres 16")]
    RDS[("Redis")]
    OBJ[("Object storage")]
  end
  MW --> APPS
  WEB -->|"transactional outbox"| WRK
  APPS --> PKG
  PKG --> DATA

Multi-tenancy#

Tenancy is two levels: an organization owns brands, and every fact or asset table carries both org_id and brand_id. A brand is a partition inside an org, not a separate tenant, so org-wide administration reads across brands while brand-scoped reporting does not.

Database enforcement. Row-level security is applied to every org-scoped table, and brand-partitioned tables carry a second, brand-level policy leg on top of it; each table gets ENABLE and FORCE ROW LEVEL SECURITY so the table owner is not exempt. Coverage is total by construction: a database-backed inventory check fails the pipeline when any table lacks its policy. Policies read two session variables through immutable helper functions: org-only tables require org_id = app_current_org(), and brand-partitioned tables add brand_id IS NULL OR app_current_brand() IS NULL OR brand_id = app_current_brand(), which makes a null brand context the org-wide rollup. An unset variable resolves to NULL and the policy denies, so a query with no context returns zero rows rather than everything. Tables deliberately outside RLS each carry a threat-modelled entry in a checked-in exceptions file.

Context pinning. runWithRlsContext(orgId, brandId, fn) opens one transaction and issues set_config(..., is_local => true) for both variables on that transaction's single connection, then runs the callback inside an AsyncLocalStorage scope whose database proxy routes every query to that same connection. This shape is load-bearing: a session-level set_config under an autocommitting driver evaporates before the next statement, and on a pool the write and the read can land on different connections. Nested scopes reuse the ambient transaction and restore the outer pin on exit. The same call also sets a statement timeout and an idle-in-transaction timeout, so one slow resolver cannot hold a pooled connection open indefinitely.

Procedure wrapping. orgProcedure and brandProcedure wrap the whole resolver, not a helper inside it, so every query a resolver makes is already pinned. Because tRPC returns middleware failures as a result envelope rather than a throw, the wrapper converts an unsuccessful result back into a throw inside the transaction, which is what makes a failed mutation roll back. Resolvers that need external I/O use orchestrated variants that open no ambient transaction and take short explicit database phases instead, because holding a tenant-pinned connection across a network call is a cross-tenant availability risk.

Non-bypass runtime identities. Each runtime service (web, worker, semantic layer, archive) connects as its own Postgres role, provisioned NOSUPERUSER NOBYPASSRLS NOINHERIT, because BYPASSRLS skips even FORCE RLS. Privileges start from a full revoke and are re-granted from a declared manifest in code: table grants for the app and worker roles, and per-column SELECT grants for the Cube role. The applications assert their own identity at boot and refuse to start if the connected role is wrong or privileged. Cross-role work that genuinely needs to cross a tenant boundary goes through fixed-shape SECURITY DEFINER functions granted to exactly one role, so the boundary is a small enumerated surface rather than a broad grant.

Tenant hosts. Orgs are reachable on a subdomain of a base domain, and an org may attach a verified custom domain. Host resolution happens before any org context exists, which is a bootstrapping problem: the lookup itself cannot be tenant-scoped. It is solved with SECURITY DEFINER locator functions that exchange one exact host string for one org id and nothing else. They cannot list orgs, prefix-search, or read anything but that id. The resolved org is then pinned into the request context, where the domain wins over the session cookie, and the request proceeds in a normal RLS scope. Absolute URLs are built only from a host that classifies as trusted; an unverifiable host produces no redirect.

Data pipeline#

Connectors are grouped into families, and family is the data plane. marketplace and marketing are first-party, content_intel is first-party brand-owned mention data, and market_intel is third-party estimates. The distinction is mechanical: first-party planes flow through the warehouse and are covered by the reconciliation gate, while third-party estimates land in their own store, are labelled as estimates wherever shown, and are barred from becoming a first-party KPI.

Bronze. Every canonical ingest writes raw_events with the exact source payload and a sha256 hash of its key-sorted serialization. A unique index over (org_id, connector_type, stream_name, external_id, payload_hash) plus an insert that ignores conflicts makes re-ingest a no-op. Because the source bytes are kept, a replay re-derives downstream state without paying for the vendor API again. Non-canonical streams land in a staging table instead, so Bronze holds only the three canonical streams.

Silver. Per-stream promoters turn Bronze and staging rows into typed facts. Idempotency is structural: intra-batch duplicates collapse last-wins before the write (Postgres cannot update the same conflict key twice in one statement), then a chunked multi-row upsert applies ON CONFLICT DO UPDATE on a declared natural key. Where a key column is nullable, the constraint is declared NULLS NOT DISTINCT, because under the default a null-keyed row never conflicts with itself and silently duplicates on every sync.

Gold. The agg_* rollup tables hold what dashboards read. brand_id is a member of every one of their unique keys. This is not cosmetic: a rollup key that omits the brand produces a row that is the sum of all brands, and the permissive null-brand leg of the RLS policy then makes that row visible under every brand.

Durable async work. A mutation never publishes to Redis directly. It inserts a job_outbox row in the same RLS-pinned transaction as the business row it belongs to, so the state change and the intent to do async work commit or roll back together. The worker claims batches with FOR UPDATE SKIP LOCKED under a short lease, revalidates each claimed row against a Zod schema, publishes to BullMQ outside any transaction, then marks the row published in a separate short transaction guarded by its own lease. The job id is stable, so a crash between "Redis accepted" and "row marked published" is replay-safe. An expired lease is reclaimed by the claim predicate itself, so there is no separate reaper.

Watermarks and self-healing. Each connector stream keeps a cursor and a high-water mark, advanced only on a successful run. Backfills advance the high-water mark without persisting a cursor, because a bounded historical page token would corrupt the incremental frontier. Failures are classified rather than blanket-retried: authentication errors, decrypt failures, template configuration errors, and deterministic constraint violations are permanent and go to a dead-letter table once, deduplicated on connector, job type, and error code; everything else is transient and retried. A later successful sync resolves that connector's open dead letters, so the queue drains itself. A vendor rate limit parks the connector with a cooldown and a degraded health status instead of dead-lettering it.

flowchart TB
  subgraph SRC["Sources"]
    direction LR
    MP["Marketplace APIs<br/>+ file uploads"]
    MK["Ad, web, and<br/>search accounts"]
    TP["Third-party market<br/>+ social estimates"]
  end
  BR["Bronze: raw_events, sha256 dedup, append-only<br/>watermarks advance only on success · permanent failures dead-letter once"]
  SI["Silver: idempotent promoters, typed facts"]
  GO["Gold: agg_* rollups, brand_id in every key"]
  RD["Dashboards, reports, Cube, decision engine"]
  EST["Market intel store,<br/>labelled estimate"]
  MP --> BR
  MK --> BR
  TP -.->|"skips the warehouse"| EST
  BR -->|"replay without re-calling the source"| SI
  SI --> GO
  GO --> RD
  EST -.->|"signal only, never a first-party KPI"| RD

Durable async work follows the outbox path: a mutation in apps/web writes the business row and a job_outbox row in the same transaction; the worker claims outbox rows with SKIP LOCKED under a lease and publishes them to the BullMQ queues that drive the promoters above.

Composability#

An org's product surface is data, not a build. Nothing is per-customer code.

Plugins. A plugin is a declarative manifest in a compiled-in catalog, validated by a strict Zod schema at module load, so a malformed manifest fails the build. The catalog spans feature and connector kinds and grows by adding a manifest, not by branching code. Per-org enablement resolves in a fixed order: an installation row wins, then a legacy org flag where one is declared, then the manifest default. Dependencies then resolve to a fixed point, so turning off a parent turns off everything transitively downstream of it.

Pillars. Six metric pillars (ecom, marketing, social, crm, market, audience) are derived per request from the enabled plugin set plus a commerce data probe. Five map to an owning plugin; ecom has none and turns on from an active marketplace credential or any order history, with a fallback so a fresh tenant is never left with nothing. The same map is the runtime gate: a pillar resolver fails closed to an empty result when its owning plugin is off. Navigation visibility composes from this in a fixed chain: plugin ownership and replacement, then pillar membership, then product-parked items, then role menu policy, then per-org hidden items, then per-org ordering and labels.

Widgets and surfaces. One widget registry declares every widget type, each with its data source kind, metric arity, renderer, and picker behaviour; every other list in the codebase is a derived view of it. Configuration is typed in both directions: a Zod union per data-source kind on the write path, and Zod-free narrowers on the read path so the registry can ship to client bundles, with compile-time parity pinned between them. Every dashboard page also declares a co-located report surface manifest, covering every navigation destination (one route can serve several). A generator cross-validates those against the plugin and template registries and emits the single registry that nav bindings, editor affordances, and the viewer's plugin gate all read. The plugin gate is derived from the manifest rather than declared, so a template cannot ship without its gate, and a page that is not classified fails the build.

AI layer#

Configuration. Language models are configured per org as an ordered list of connections, each holding an ordered list of models. API keys are stored as AES-256-GCM envelopes (random IV and auth tag per write, versioned keys) and never returned to a client. A per-org switch selects whether the org uses its own credentials or the platform's; a bring-your-own-key org never silently falls back to platform credentials.

One resolver, one failover chain. A single resolver reads the applicable connections, decrypts, maps provider slugs to adapters, and flattens everything into one ordered candidate list. Providers sit behind one interface; supporting a new one is one adapter, not a new integration path. Failover is a hand-written language model wrapper rather than a middleware chain, because middleware can only wrap one inner model. Only prompt and type validation errors are fatal; every transport, authentication, rate-limit, and server error advances to the next candidate. Streaming uses peek-then-commit: leading control frames are buffered so the chain can still fail over before the first content token, then replayed onto the live stream. A static audit forbids a second implementation of this resolution path.

Prompts. System prompts assemble from named slots that an org can override per industry, resolving org-and-industry, then org-wide, then the built-in default. Two directives are re-pinned by the resolver on every resolution and cannot be edited away: the output language, and the contract that a drafting model proposes shape only and never a threshold. A prompt is guidance; the parser is the gate.

Anti-fabrication. The system prompt carries a footer instructing the model to use only supplied numbers and to state missing data rather than guess, but enforcement is mechanical and happens after generation. A proof-carrying-numbers gate drops any sentence containing a numeral not bound to a supplied fact token; it never rewrites a number, so it cannot introduce a wrong one. Quoted text in generated audience narrative is admissible only as a mention id plus character offsets that re-extract byte for byte from the source, with clause alignment required so a span cannot invert a claim's polarity by cutting off a negation. If nothing survives, nothing is published. Streamed chat cannot be gated mid-flight, so numerals are traced back to tool results client-side and a warning is surfaced instead. Separately, a non-streaming completion is never cached or published without a truncation check.

Model identity. The provider and model name are never shown on an end-user surface. Streamed response metadata carries only a finish reason; provider and model are persisted for administrator diagnostics. A static audit scans end-user routes for vendor names and model id patterns and fails the push. Token and cost usage is written to a per-org ledger in integer micro-VND, and the audience generation path meters against an optional per-org budget whose month boundary is local time, not UTC.

Quality architecture#

Correctness is enforced by gates rather than convention, layered by strength.

• Single-source metric helpers. Each metric definition lives in exactly one module, and a family of single-source audits fails the build when a second definition of the same number appears in a router or a dashboard.
• Reconciliation against external oracles. A database-backed harness runs several hundred assertions across every metric-bearing surface, with exact equality as the default and the few tolerances justified inline. Expected values anchor to frozen ground truth derived independently from operator exports, never to a second in-code derivation, because a check that re-derives a number with the same code agrees with itself by construction.
• Static audit fleet. The audits are enumerated in one place that both the pre-push hook and CI call, covering timezone bucketing, SQL binding hazards, tenancy pins, single-source rules, i18n coverage, and bundle boundaries. Suppression requires an explicit inline token with a reason, and several audits have no suppression token at all.
• Tenancy verification. A gate connects as the real non-superuser runtime roles and proves against live data that RLS is enforced, not merely declared: correct posture, force-RLS on, zero rows without context, zero rows for a wrong org, the historical failure mode still failing closed, and nested scopes restoring their outer context.
• Immutability where it matters. The decision ledger is append-only by database trigger, which rejects UPDATE and TRUNCATE for every caller. Corrections are new rows that supersede old ones, so a decision's evidence cannot be quietly rewritten after the fact.

CI runs this in four jobs: the static and unit gate, production image builds with runtime identity assertions, the reconciliation and RLS gates against a freshly migrated Postgres, and an isolated browser smoke test.

Related#

• Cloud Architecture
• Data Foundation
• Governance and Honest Numbers
• Data Warehouse