Skip to content
MSO CloudDocumentation

Data planes and the warehouse

On this page

In short#

Three planes never mix: first-party marketplace, first-party marketing, third-party market intelligence. First-party data flows through Bronze, Silver and Gold; estimates live in their own store.

First-party: marketplace and marketing planes, reconciledSourcesSeller APIs, exportsAd, web, CRMSheets and filesfirst-partyBronzeRaw, append-onlyContent-hash dedupeTemplate version keptreplayableSilverTyped factsIdempotent promotersNatural-key upsertssame input, same factsGoldRollups dashboards readBrand in every keyNightly and on demandread surfaceFirst-party KPIsDashboards, reportsAssistant answersDecision measurementreconciliation gateThird-party: market intelligence plane, estimatesSourcesMarket-data feedsSocial listeningthird-partyEstimate storeIts own store, outside Bronze, Silver and GoldNever reconciled: no external ground truth existslabelled wherever it appearsLabelled viewsMarket pagesHypothesis inputnever a KPIMoney is integer VND · day buckets in Vietnam time · one helper per metric · sample rows flagged
First-party data passes Bronze, Silver and Gold and is reconciled; third-party estimates stay in their own store and reach only labelled views.

Three data planes#

Plane Sources Lands in Checked by
First-party marketplace Seller APIs, seller CSV/XLSX exports Bronze, Silver, Gold Reconciliation against outside ground truth
First-party marketing Ad, web-analytics, search, email and CRM accounts Bronze staging, Silver, Gold Reconciliation, assertions per grain
Third-party market intel Market-data feeds and files, social listening Its own estimate store Not reconciled by design; labelled

Marketplace is the money plane. Marketing conversions are platform-attributed, never summed across ad platforms and never shown as marketplace revenue. Third-party data has no ground truth to reconcile against, so it carries a badge and stays out of first-party KPIs and the measured outcome of a decision. HR, finance and warehouse records arrive through the same mapping contract as marketing files and are first-party; a content-intelligence corpus is a client-approved first-party deliverable, not a fourth plane (Organization datasets and measures).

The Platform API is a second reader of the third-party plane, and the only one outside MSO Cloud: connected apps read the social trend feed and the older per-org trend rows through one scope, platform:social.read. Marketplace and marketing planes have no scope. The routes that serve the shared trend feed are described in Platform API and events.

Shared dataset plane#

A fourth kind of table sits beside the three org-scoped planes: a shared dataset, synced once by a platform connector and read by every org that holds it. Its tables carry no org_id, no is_sample and no tenant data, so Postgres row-level security cannot gate them; the org gate is a single reader module (Tenancy and security). The first shared dataset is the social trend feed (ADR 0061): trends, their history and the review decisions, plus platform_connector_runs and platform_connector_state for sync bookkeeping. It belongs to the third-party market-intelligence plane and keeps its label and its exclusion from first-party KPIs.

Shared dataset plane: one sync, one gated readerVendor APIOne account, one session300 calls per 60 sUpdates about every 12 hPlatform connectorWorker, hourly checkFull fetch on change or 24 hSingle flight, fencedShared tablesshared_trend_itemsshared_trend_historyplatform_connector_stateReader with org proofresolveTrendFeedScopePlugin row decidesUsable rows onlyIn-app trends pageSession of a signed-inorg memberAssistant toolsgetSocialTrendsgetSocialTrendDetailPlatform API v1Four trend-feed routesToken of exactly one orgApp projectionLocal copy, replaced byrevision; 3 h grant leaseNo org_id and no RLS on these tables: the org gate is the reader, and a static audit allows no second door.Orgs without the feed read nothing; apps read their own projection, never the shared tables.
The shared plane next to the three org-scoped planes: written by the worker only, read through one proof-carrying module.
Part Rule
Writer The worker only; a super admin's review decision is the one web write
Readers shared-trends-read.ts, behind an org proof; the in-app page, the assistant and the Platform API all use it
Version platform_connector_state.revision, raised in the same transaction as any change a reader can see
Empty answer An empty or partial vendor fetch deletes nothing

Operator steps: docs/runbooks/shared-trend-feed.md.

The six pillars#

Pillar Plane Covers
ecom First-party marketplace Marketplace and offline orders, inventory, cost of goods
marketing First-party marketing Ad spend, web analytics, search data from the organization's accounts
crm First-party marketing Leads and deals; abstains when no lead or deal feed is connected
audience First-party deliverable Corpus, mentions and sentiment behind content-intelligence decks
social Third-party listening Buzz and sentiment from a listening feed; a signal, never a KPI
market Third-party estimate Category, competitor and product estimates; never a measured outcome

ecom has no owning plugin: it turns on with a live marketplace connection or any order history. Every other pillar is owned by one plugin and resolves to an empty result when that plugin is off (Organization types, Connectors and data planes).

The medallion warehouse#

Layer Holds Guarantee
Bronze Raw, append-only ingest; staging rows with template version Same payload twice is a no-op; fixes replay without the vendor
Silver Typed facts from per-stream promoters Replaying Bronze reproduces the same Silver
Gold Rollups dashboards and reports read Brand in every key; refreshed nightly and on demand

Orders, creators and inventory are stored with a content hash; every other stream lands in staging. Mechanics: Ingest pipeline.

Money and time#

Vietnam time (UTC+7)18:003 Oct 00:0006:0012:0018:004 Oct 00:0006:0012:00Day 3 Oct, Vietnam timewhat the screen and the export meanDay 3 Oct, bare UTC castrefused by the date audits7 h dropped7 h borrowedOrder at 02:30 on 3 Oct:Vietnam time counts it on 3 Oct; the UTC cast files it under 2 Oct, so the two totals for 3 Oct differ.Day buckets, filters and chart labels all anchor to Asia/Ho_Chi_Minh, so a month on screen equals the month in the download.
Why every date path uses Vietnam time. A UTC day edge is seven hours late: it drops the start of the day and borrows the start of the next one.

Money is a signed 64-bit integer of dong, serialized as a safe integer and formatted through one helper; averages and rates round once, at display. Time is Vietnam time on every date path: day buckets in Asia/Ho_Chi_Minh, projected as text and filtered with local day edges, so the screen equals the download. Every metric surface owes a reconciliation assertion whose expected value traces to external ground truth, never to a second derivation in code; a figure at a vendor ceiling renders as "at least N" rather than inventing a denominator (Measurement and honest numbers).

The signal registry#

Under the pillars sits a versioned, organization-scoped catalog of named signals, each with a schema, a unit, a time grain and a provenance tier (first-party, third-party or derived). A feature store holds per-period values; a missing value is stored as absent, not zero. Every proposed decision reads from this registry. Next step: From signal to decision.

Where it stops#

Third-party estimates are periodic, not real time, and never reconciled. Lead and deal feeds are often thin, so crm metrics abstain rather than fill the gap. Finance variance and waterfall cards: Project status.

Decisions#

Decision Why Enforced by
Third-party data has its own store and label; excluded from KPIs, measurement and reconciliation An estimate summed with an order total looks exact and is neither Plane-isolation and audience-isolation audits; chat asserts zero market data when off
Every money value is a signed 64-bit integer of dong, formatted by one helper Float money drifts; a total one dong off its download is not trusted Column types, one formatter in @yng/charts, exact-integer reconciliation
Each metric is defined once; dashboards, reports, exports, alerts and chat call it Two implementations disagree the first time a filter, merge or time zone changes Single-source audits; reconciliation after real promoters on a real database
Day edges, SQL buckets and chart labels anchor to Asia/Ho_Chi_Minh; no UTC on date paths A UTC day edge pulls seven hours of the neighbouring day into a range Audits refuse bare date casts and UTC day labels; browser check under a month filter