products

Products / courses sold or offered by the platform.

• ETL strategy: merge
• PK: id

Columns

Column	Type	Description
id	BIGINT	PK.
id_tenant	BIGINT	FK → tenants.id.
id_origin	BIGINT	Parent product id when replicated across tenants. Joins with track_products / exam_results use COALESCE(id_origin, id) to resolve replicas.
id_showcase	BIGINT	FK → showcases.id. Showcase the product appears in.
is_free	BOOLEAN	Free access.
is_draft	BOOLEAN	Unpublished draft.
is_not_listed	BOOLEAN	Hidden from the public listing.
is_hidden	BOOLEAN	Fully hidden.
position	INTEGER	Display order.
title	TEXT	Product name.
meta	JSONB	Product configuration. Currently exposes the certificate group. See structure below.
meta_override	JSONB	Overrides on top of the parent product (when replicated). Same shape as meta, restricted to override-friendly keys. NULL if no override.
created_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ

meta structure

Domain-grouped JSONB. Any key may be absent — empty branches are stripped.

{
  "certificate": {
    "is_active",
    "is_unique_emission",
    "show_back",
    "show_dates",
    "show_product_name",
    "logo_theme",
    "file",
    "content",
    "taught_by",
    "workload",
    "availability": { "mode", "exam_id" | "progress_threshold" | "period" },
    "fields": {
      "name":   { "color", "font" },
      "date":   { "is_active", "color", "font" },
      "doc":    { "is_active", "color", "font" },
      "seq":    { "is_active", "color", "font", "source" },
      "qrcode": { "is_active", "color", "background", "position" }
    }
  }
}

meta.certificate

Path	Type	Meaning
is_active	boolean	Certificate enabled for the product.
is_unique_emission	boolean	Only one certificate per learner is allowed; reissues are blocked.
show_back	boolean	Render the certificate back side.
show_dates	boolean	Print start/end dates on the certificate.
show_product_name	boolean	Print the product name on the certificate.
logo_theme	string	Logo theme variant key.
file	string	Certificate template/file reference.
content	string	Certificate body text.
taught_by	string	Instructor / authoring entity printed on the certificate.
workload	string	Course workload printed on the certificate (e.g. 40h).
availability	object	When the certificate is unlocked. Polymorphic — see below.
fields	object	Per-element rendering settings (color, font, on/off). See below.

meta.certificate.availability (polymorphic)

Only one of these shapes will be present, identified by mode:

mode	Extra fields	Meaning
exam	exam_id: integer	Available after the learner passes a specific exam.
progress	progress_threshold: integer (0–100)	Available after the learner reaches a course-progress percentage.
period	period: string (e.g. +2 weeks, +30 days)	Available after a fixed delay from enrollment.
open	(none)	Available immediately.

meta.certificate.fields.<element>

Each rendering element shares the same shape, with subtle differences:

Element	is_active	color	font	Extras
name	— (always rendered)	✓	✓	—
date	✓	✓	✓	—
doc	✓	✓	✓	—
seq	✓	✓	✓	source: "tenant" | "product"
qrcode	✓	✓	—	background, position

meta_override

When a product is replicated across tenants, the child can override certain certificate keys. meta_override mirrors the same JSON shape as meta (restricted to override-friendly keys — no availability, no seq.source), so consumers can union the two without key rewrites:

-- Effective certificate config (override > parent)
SELECT id,
       title,
       COALESCE(meta_override->'certificate', meta->'certificate') AS effective_certificate
FROM lms.products
WHERE deleted_at IS NULL;

Relationships

• Parent: tenants, showcases.
• Children: modules, lessons, exams, exam_results, user_certificates, user_product_progress, lesson_ratings.
• M:N with tracks: via track_products.

"Origin product" pattern

When a product is replicated across tenants, id_origin points back to the original. For queries that span tenants or need to consolidate by course (not by replica), use COALESCE(id_origin, id) as the canonical key:

SELECT COALESCE(id_origin, id) AS product_key, count(*)
FROM lms.products
WHERE deleted_at IS NULL
GROUP BY product_key;