Data Model

:::info Source Sourced from services/marketplace-service/DATA_MODEL.md in the documentation repo. :::

Companion: 12 Data Models · 13 Security

1. Storage Overview

Store	Purpose
Postgres 16 (schema marketplace)	Primary system of record
Redis 7	Idempotency, saga locks, price cache
NATS JetStream	Outbox publishing, event stream
S3 / object store	Listing hero / screenshot assets

2. Schema Overview (Postgres)

All tables live under schema marketplace. RLS enforced on every tenant-scoped table.

listings                  orders
pricing_plans             order_lines
listing_assets            licenses
coupons                   license_seat_allocations
coupon_redemptions        purchase_sagas
provider_earnings         saga_step_history
earnings_lines            outbox
                          processed_events

3. Table Definitions

3.1 listings

CREATE TABLE marketplace.listings (
  id               TEXT PRIMARY KEY,            -- 'lst_<ulid>'
  provider_tenant_id TEXT NOT NULL,
  course_id        TEXT NOT NULL,
  course_version_id TEXT NOT NULL,
  visibility       TEXT NOT NULL CHECK (visibility IN ('unlisted','public')),
  state            TEXT NOT NULL CHECK (state IN ('draft','submitted','approved','live','suspended','retired')),
  marketing        JSONB NOT NULL,
  refund_policy    JSONB NOT NULL,
  revenue_share    JSONB NOT NULL,
  submitted_at     TIMESTAMPTZ,
  approved_at      TIMESTAMPTZ,
  suspended_at     TIMESTAMPTZ,
  suspension_reason TEXT,
  retired_at       TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  version          INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_listings_provider ON marketplace.listings (provider_tenant_id);
CREATE INDEX idx_listings_course   ON marketplace.listings (course_id, course_version_id);
CREATE INDEX idx_listings_state    ON marketplace.listings (state) WHERE state IN ('submitted','live');
CREATE INDEX idx_listings_live_fts ON marketplace.listings USING GIN ((marketing->'tagline') gin_trgm_ops) WHERE state='live';

3.2 pricing_plans

CREATE TABLE marketplace.pricing_plans (
  id               TEXT PRIMARY KEY,            -- 'pln_<ulid>'
  listing_id       TEXT NOT NULL REFERENCES marketplace.listings(id) ON DELETE CASCADE,
  kind             TEXT NOT NULL CHECK (kind IN ('one_time','subscription','seat_pack','site_license')),
  currency         CHAR(3) NOT NULL,
  price_amount     BIGINT NOT NULL,              -- minor units (cents)
  seats            INT,
  interval_months  INT,
  perpetual_offline_access BOOLEAN NOT NULL DEFAULT false,
  active           BOOLEAN NOT NULL DEFAULT true,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  version          INT NOT NULL DEFAULT 1
);

CREATE INDEX idx_plans_listing ON marketplace.pricing_plans (listing_id);
CREATE INDEX idx_plans_active  ON marketplace.pricing_plans (listing_id) WHERE active;

3.3 orders

CREATE TABLE marketplace.orders (
  id                 TEXT PRIMARY KEY,            -- 'ord_<ulid>'
  buyer_tenant_id    TEXT NOT NULL,
  buyer_user_id      TEXT NOT NULL,
  currency           CHAR(3) NOT NULL,
  status             TEXT NOT NULL CHECK (status IN ('created','pending_payment','paid','fulfilled','refunded','failed')),
  subtotal_amount    BIGINT NOT NULL,
  discount_total     BIGINT NOT NULL DEFAULT 0,
  tax_total          BIGINT NOT NULL DEFAULT 0,
  totals_amount      BIGINT NOT NULL,
  applied_coupons    TEXT[] NOT NULL DEFAULT '{}',
  payment_intent_id  TEXT,
  failure_reason     TEXT,
  placed_at          TIMESTAMPTZ NOT NULL,
  paid_at            TIMESTAMPTZ,
  fulfilled_at       TIMESTAMPTZ,
  refunded_at        TIMESTAMPTZ,
  refund_deadline    TIMESTAMPTZ,
  saga_id            TEXT NOT NULL UNIQUE,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  version            INT NOT NULL DEFAULT 1
);

CREATE INDEX idx_orders_buyer   ON marketplace.orders (buyer_tenant_id, buyer_user_id);
CREATE INDEX idx_orders_status  ON marketplace.orders (status) WHERE status IN ('pending_payment','paid');
CREATE INDEX idx_orders_refund  ON marketplace.orders (refund_deadline) WHERE status IN ('paid','fulfilled');
CREATE INDEX idx_orders_placed  ON marketplace.orders (placed_at DESC);

3.4 order_lines

CREATE TABLE marketplace.order_lines (
  id                TEXT PRIMARY KEY,            -- 'oln_<ulid>'
  order_id          TEXT NOT NULL REFERENCES marketplace.orders(id) ON DELETE CASCADE,
  listing_id        TEXT NOT NULL,
  pricing_plan_id   TEXT NOT NULL,
  course_id         TEXT NOT NULL,
  course_version_id TEXT NOT NULL,
  quantity          INT NOT NULL CHECK (quantity >= 1),
  unit_price_amount BIGINT NOT NULL,
  subtotal_amount   BIGINT NOT NULL
);
CREATE INDEX idx_lines_order ON marketplace.order_lines (order_id);

3.5 licenses

CREATE TABLE marketplace.licenses (
  id                     TEXT PRIMARY KEY,         -- 'lic_<ulid>'
  tenant_id              TEXT NOT NULL,            -- buyer
  provider_tenant_id     TEXT NOT NULL,
  listing_id             TEXT NOT NULL,
  course_id              TEXT NOT NULL,
  course_version_id      TEXT NOT NULL,
  pricing_plan_kind      TEXT NOT NULL,
  scope                  TEXT NOT NULL CHECK (scope IN ('individual','org')),
  seats                  INT NOT NULL,
  remaining_seats        INT NOT NULL,
  valid_from             TIMESTAMPTZ NOT NULL,
  valid_until            TIMESTAMPTZ,
  state                  TEXT NOT NULL CHECK (state IN ('active','expired','revoked')),
  source                 TEXT NOT NULL CHECK (source IN ('purchase','gift','manual')),
  order_id               TEXT,
  refund_deadline        TIMESTAMPTZ,
  perpetual_offline_access BOOLEAN NOT NULL DEFAULT false,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  version                INT NOT NULL DEFAULT 1,
  CHECK (remaining_seats >= 0 AND remaining_seats <= seats),
  CHECK (valid_until IS NULL OR valid_until > valid_from)
);

CREATE INDEX idx_licenses_tenant  ON marketplace.licenses (tenant_id);
CREATE INDEX idx_licenses_course  ON marketplace.licenses (course_id);
CREATE INDEX idx_licenses_state   ON marketplace.licenses (state);
CREATE INDEX idx_licenses_expiry  ON marketplace.licenses (valid_until) WHERE state='active' AND valid_until IS NOT NULL;
CREATE INDEX idx_licenses_order   ON marketplace.licenses (order_id);

3.6 license_seat_allocations

CREATE TABLE marketplace.license_seat_allocations (
  id             TEXT PRIMARY KEY,
  license_id     TEXT NOT NULL REFERENCES marketplace.licenses(id) ON DELETE CASCADE,
  user_id        TEXT NOT NULL,
  status         TEXT NOT NULL CHECK (status IN ('active','released','consumed_on_refund')),
  assigned_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  released_at    TIMESTAMPTZ,
  UNIQUE (license_id, user_id, status) -- one active allocation per user per license
);
CREATE INDEX idx_seats_user ON marketplace.license_seat_allocations (user_id);

3.7 coupons & coupon_redemptions

CREATE TABLE marketplace.coupons (
  id              TEXT PRIMARY KEY,
  code            TEXT NOT NULL,
  tenant_scope    TEXT,
  provider_scope  TEXT,
  discount        JSONB NOT NULL,
  usage_cap       INT,
  usage_count     INT NOT NULL DEFAULT 0,
  per_user_cap    INT,
  valid_from      TIMESTAMPTZ NOT NULL,
  valid_until     TIMESTAMPTZ,
  active          BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  version         INT NOT NULL DEFAULT 1,
  CHECK (usage_cap IS NULL OR usage_count <= usage_cap)
);
CREATE UNIQUE INDEX uniq_coupon_code_scope
  ON marketplace.coupons (code, COALESCE(tenant_scope, ''));

CREATE TABLE marketplace.coupon_redemptions (
  coupon_id   TEXT NOT NULL REFERENCES marketplace.coupons(id),
  order_id    TEXT NOT NULL,
  user_id     TEXT NOT NULL,
  redeemed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (coupon_id, order_id)
);
CREATE INDEX idx_redemption_user ON marketplace.coupon_redemptions (user_id);

3.8 purchase_sagas & saga_step_history

CREATE TABLE marketplace.purchase_sagas (
  id                 TEXT PRIMARY KEY,             -- 'sga_<ulid>'
  order_id           TEXT NOT NULL UNIQUE,
  state              TEXT NOT NULL CHECK (state IN ('started','awaiting_payment','licensing','enrolling','fulfilled','compensating','failed')),
  current_step       TEXT,
  correlation_id     TEXT NOT NULL,
  compensation_reason TEXT,
  awaiting_payment_timeout_at TIMESTAMPTZ,
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  version            INT NOT NULL DEFAULT 1
);
CREATE INDEX idx_saga_timeout
  ON marketplace.purchase_sagas (awaiting_payment_timeout_at)
  WHERE state='awaiting_payment';

CREATE TABLE marketplace.saga_step_history (
  saga_id          TEXT NOT NULL REFERENCES marketplace.purchase_sagas(id) ON DELETE CASCADE,
  seq              INT NOT NULL,
  step             TEXT NOT NULL,
  entered_at       TIMESTAMPTZ NOT NULL,
  exited_at        TIMESTAMPTZ,
  outcome          TEXT CHECK (outcome IN ('advanced','compensated','failed')),
  causation_event_id TEXT,
  error_code       TEXT,
  PRIMARY KEY (saga_id, seq)
);

3.9 provider_earnings & earnings_lines

CREATE TABLE marketplace.provider_earnings (
  provider_tenant_id TEXT NOT NULL,
  currency           CHAR(3) NOT NULL,
  period_month       CHAR(7) NOT NULL,             -- 'YYYY-MM'
  gross_revenue      BIGINT NOT NULL DEFAULT 0,
  platform_fee       BIGINT NOT NULL DEFAULT 0,
  refunds            BIGINT NOT NULL DEFAULT 0,
  taxes_withheld     BIGINT NOT NULL DEFAULT 0,
  net_payable        BIGINT NOT NULL DEFAULT 0,
  state              TEXT NOT NULL CHECK (state IN ('accruing','ready','paid')),
  payout_id          TEXT,
  version            INT NOT NULL DEFAULT 1,
  PRIMARY KEY (provider_tenant_id, currency, period_month)
);

CREATE TABLE marketplace.earnings_lines (
  id                 BIGSERIAL PRIMARY KEY,
  provider_tenant_id TEXT NOT NULL,
  currency           CHAR(3) NOT NULL,
  period_month       CHAR(7) NOT NULL,
  order_id           TEXT NOT NULL,
  kind               TEXT NOT NULL CHECK (kind IN ('accrual','reversal')),
  amount             BIGINT NOT NULL,
  recorded_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_earnings_lookup
  ON marketplace.earnings_lines (provider_tenant_id, period_month, currency);

3.10 outbox & processed_events

CREATE TABLE marketplace.outbox (
  id           BIGSERIAL PRIMARY KEY,
  event_id     TEXT NOT NULL UNIQUE,
  subject      TEXT NOT NULL,
  payload      JSONB NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ
);
CREATE INDEX idx_outbox_pending ON marketplace.outbox (id) WHERE published_at IS NULL;

CREATE TABLE marketplace.processed_events (
  event_id   TEXT PRIMARY KEY,
  subject    TEXT NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_processed_subject_time
  ON marketplace.processed_events (subject, processed_at DESC);

4. Row-Level Security

Tenant isolation via RLS. Example (applied to each tenant-scoped table):

ALTER TABLE marketplace.listings ENABLE ROW LEVEL SECURITY;

CREATE POLICY listings_tenant_isolation ON marketplace.listings
  USING (
    provider_tenant_id = current_setting('app.tenant_id', true)
    OR current_setting('app.role', true) = 'platform_admin'
  );

CREATE POLICY listings_public_read ON marketplace.listings
  FOR SELECT
  USING (state = 'live' AND visibility = 'public');

Tables with analogous policies: orders, order_lines, licenses, license_seat_allocations, coupons, coupon_redemptions, purchase_sagas, provider_earnings.

Connection setup (per-request):

SET LOCAL app.tenant_id = 'ten_...';
SET LOCAL app.role = 'user';
SET LOCAL app.user_id = 'usr_...';

5. Indices Strategy

Indices are built for the top read patterns:

1. Provider dashboard: idx_listings_provider, idx_earnings_lookup.
2. Buyer history: idx_orders_buyer.
3. Public browse: trigram GIN on marketing fields + B-tree on (state, created_at).
4. License lookup per tenant: idx_licenses_tenant, idx_licenses_course.
5. Saga scheduler: partial idx_saga_timeout.
6. License expiry sweep: partial idx_licenses_expiry.

6. Migrations

• Tool: node-pg-migrate.
• Directory: services/marketplace-service/migrations/.
• Naming: YYYYMMDDHHMMSS_<slug>.ts.
• Rollback always implemented for non-destructive steps.
• See MIGRATION_PLAN.md for rollout strategy.

7. Retention & Archival

Table	Retention	Archival
orders, order_lines	7 years (finance/tax)	Cold S3 parquet after 1y
licenses, license_seat_allocations	7 years	Cold S3 after 1y past validUntil
coupons	indefinite; redemptions 90d
purchase_sagas, saga_step_history	90 days	Drop after
outbox	Purged after publish + 7 days
processed_events	30 days	Scheduled delete

8. Read Models / Projections

Kept in same Postgres, refreshed by event handlers:

Projection	Source	Use
mv_course_shippable(course_version_id)	content.play_package.built.v1	Gate listing approval
mv_course_published(course_version_id)	catalog.course_version.published.v1	Gate listing approval
mv_kyc(tenant_id)	tenant.kyc_verified.v1	Gate listing live transition

These are plain tables updated via event handlers, not PG materialized views, to stay transactional with handler work.

9. Data Volume Estimates (M2/M4)

Table	M2 row count	M4 row count
listings	5k	200k
pricing_plans	15k	600k
orders	50k	10M
order_lines	100k	25M
licenses	80k	15M
outbox (steady state)	<10k	<50k

Sizing guides partitioning decisions: orders and licenses partitioned by RANGE (placed_at) / RANGE (created_at) monthly starting at M4.