Overview

Enterprise-only product

OpenDB is available exclusively to Cademi Enterprise customers. If you are interested in adopting it for your account, please reach out to our customer success team to discuss provisioning, pricing and access.

OpenDB is a PostgreSQL data warehouse organized into two schemas:

• lms — operational entities (lms.tenants, lms.users, lms.products, lms.lessons, …).
• metrics — pre-aggregated tables for executive consumption (metrics.daily_counters, metrics.user_activity_snapshots, lesson/track aggregates).

Modeling principles

1. Standardized keys. Every logical FK uses the id_ prefix (id_tenant, id_user, id_product).
2. id_tenant almost everywhere. Lets you partition every query by sub-platform without ambiguity.
3. Soft delete. Most tables carry deleted_at TIMESTAMPTZ NULL. Filter deleted_at IS NULL for active rows.
4. Boolean flags as is_*. Yes/no states use the is_ prefix and the SQL BOOLEAN type (is_free, is_draft, is_hidden).
5. Enums as TEXT. Fixed value sets are stored as TEXT literals (e.g. first_access, multiple_choice). Each enum column is documented on its own table page.
6. Timestamps in São Paulo time (UTC−3). All TIMESTAMPTZ columns carry an explicit America/Sao_Paulo offset, so comparisons and date_trunc calls behave consistently regardless of your session's TimeZone setting.

Refresh strategy

Load type	Frequency	Strategy
Full seed	On demand	Drops the table (DROP CASCADE) and reloads from scratch
Daily delta	04:00 UTC	Loads only rows changed in the last 24h
Derived	After each delta	Recomputes dependent tables (e.g. user_product_progress)

Per-table strategy is declared in manifest.yaml:

• merge — upsert by primary key. Default.
• append — insert-only (rows are immutable; PK used only to deduplicate retries).
• derived — fully recomputed from other tables in the destination.

When to use which family

• Ad-hoc analysis, drill-down, exports → lms.* tables.
• Dashboards, executive panels, day-over-day comparisons → metrics.* tables (already aggregated, much faster).
• Fine-grained event history (every click, every login) → append-only tables (lms.user_logs, lms.admin_logs, lms.scores, lms.user_lesson_progress).