thefittclub-private-catalog-data

Private catalog data and intellectual property (V2)

Architectural reality (not “TS-only” vs “DB-only”)

The product “brain” lives primarily in Cloudflare D1 and is modeled with Drizzle under app/db/schema/ (split modules + index.ts — not a single schema.ts).

There is still one intentional hybrid:

Layer	What it is	How it is maintained
Session canvas (Ticket 2)	Relational editorial graph: phases, schedule, training_sessions, session_items, session_blocks, session_exercises, techniques junctions, etc.	Back office / migrations / exports. See npm run db:dump:catalog and npm run db:dump:catalog:snapshot.
Legacy routines table	Denormalized rows (Ritual Core Phase 1 express/base/progressive) used while the canvas remains partially dual-stack.	TypeScript source: app/lib/catalog/data/ritual-core/phase-1, compiled into SQL via scripts/lib/catalog-sync-rows.ts. Sync with npm run db:sync:catalog (local SQLite), npm run db:sync:catalog:preview, npm run db:sync:catalog:prod.

So: hardcoded TS matrices are not fully gone — they still feed routines until that table is retired (see comment on routines in app/db/schema/catalog.ts).

Why D1 for the catalog

• Loaders (React Router v7) can join locale tables (exercise_translations, material_translations, session_translations, …) at the edge.
• Evaluations and prep mode query relational tables such as phase_evaluation_steps and user_active_prep_mode.
• Editorial IP (full routine graphs, premium content) can be kept out of git by using private SQL snapshots and remote D1; schema definitions stay in the repo.

What counts as “private catalog”

Row-level proprietary content (not the DDL) for things like:

1. Exercise library and materials: exercises, exercise_translations, materials, material_translations, exercise_materials.
2. Session canvas: products, product_phases, product_modalities, modalities, phase_schedule, training_sessions (SQL table name; not to be confused with Better Auth sessions), session_items, session_blocks, session_exercises, session_techniques, related translation tables.
3. Evaluations / unlocks: phase_unlock_conditions, phase_evaluation_steps.
4. Categories: category_definitions and category_translations (as used in dumps).
5. Onboarding (EAV): onboarding_questions, product_onboarding, user_onboarding_answers (app/db/schema/onboarding.ts).

Clinical / profile flags for members are not a pathologies catalog table in this schema: they are modeled as physical_profiles plus member_conditions (condition_key values such as diastasis, chronic lumbar, prolapse).

Drizzle table definitions for the above are public in git; the data can be private.

Seed and export layout (actual repo)

drizzle/seeds/
├── catalog.sql                    # Optional: exported catalog for local `db:seed:catalog` (see package.json)
├── public/                        # Committed: dev + E2E overlays, onboarding seed
│   ├── dev-preview.sql
│   ├── e2e-*.sql, e2e-base.sql
│   └── onboarding-seed.sql
├── local/                         # Gitignored — snapshots only (see root .gitignore)
└── private/                       # Gitignored via drizzle/seeds/.gitignore
    ├── raw-data/                  # Optional inputs from product (e.g. JSON) — not in git
    └── generated-catalog.sql      # If present, applied by db:seed:private*

Unified public seed flow: npm run db:seed runs scripts/seeds/unified-generator.ts, writes drizzle/seeds/public/unified-seed.sql (gitignored at that path), then wrangler d1 execute … plus onboarding-seed.sql (scripts/seeds/apply-unified-seed.ts).

Private SQL: scripts/seeds/seed-private-if-present.ts runs drizzle/seeds/private/generated-catalog.sql when the file exists. The log message mentions npm run db:seed:generate; that script is not defined in package.json today — either add a generator script or place generated-catalog.sql (or equivalent) via your internal pipeline. For a fuller script matrix see _docs/99-utils/npm-scripts.md (some rows there may be ahead of package.json).

Local snapshot (BO work): npm run db:dump:catalog:snapshot → drizzle/seeds/local/catalog-snapshot.sql; restore with npm run db:restore:local. This is documented in scripts/catalog/dump-catalog-content.ts (which tables are included vs intentionally excluded).

Wrangler export of catalog tables: npm run db:dump:catalog exports a fixed set of tables to drizzle/seeds/catalog.sql for optional reuse.

Commands (quick reference)

Goal	Command
Upsert legacy routines from TS (local D1 file)	npm run db:sync:catalog
Push legacy routines SQL to remote preview / prod	npm run db:sync:catalog:preview / db:sync:catalog:prod
Export catalog slice to drizzle/seeds/catalog.sql	npm run db:dump:catalog
Snapshot canvas slice to gitignored drizzle/seeds/local/	npm run db:dump:catalog:snapshot
Apply optional catalog.sql to local D1	npm run db:seed:catalog
Public dev + E2E seed	npm run db:seed:dev (and db:seed:e2e:* variants)
Private SQL if file present	npm run db:seed:private (or :preview / :prod)

---

Last reviewed against the codebase: 2026-04-26.