Data Hydration, Seeding, and Migration Strategy¶

Comprehensive reference for how database schema is created, how seed data is introduced, and how each environment is hydrated for local development, integration testing, and staging.

Table of Contents¶

Overview
Migration Strategy
Data Layers
Environment Profiles
Seeding Reference
Full Deno Task Reference
Workflows
Adding New Seed Data

Overview¶

The platform uses a three-layer data strategy:

┌─────────────────────────────────────────────────────────────┐
│  Layer 1 — MIGRATIONS                                        │
│  Supabase SQL files that define schema + minimal seed rows  │
│  Applied once per environment; run automatically on reset   │
└──────────────────────────────┬──────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────┐
│  Layer 2 — CONTENT SEED SCRIPTS                              │
│  Deno scripts that populate curriculum and catalog data      │
│  Environment-aware; idempotent (safe to re-run)             │
└──────────────────────────────┬──────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────┐
│  Layer 3 — TEST USER / AUTH SEED                             │
│  Deno scripts that create auth.users and subscriptions       │
│  Local only; never run in staging or production             │
└─────────────────────────────────────────────────────────────┘

Principle: Migrations own schema structure; seed scripts own data. This separation makes it safe to reset the database (replays migrations cleanly) and re-seed without worrying about conflicts — all seed scripts use upsert with ON CONFLICT.

Migration Strategy¶

Location¶

supabase/migrations/
├── 20241229000001_create_microlearning_schema.sql   # Core schema
├── 20241229000002_seed_agentic_ai_curriculum.sql    # Curriculum baseline
├── 20241230000001_add_snapshot_versioning.sql
├── 20250101000001_standardize_estimated_time_to_minutes.sql
├── 20250102000001_fix_concept_estimated_mins_calculation.sql
├── 20260101152744_remove_precomputed_fields.sql
├── 20260104000001_add_import_infrastructure.sql
├── 20260105000001_eliminate_null_snapshots.sql
├── 20260106000001_fix_stats_views_snapshot_filtering.sql
├── 20260108000001_add_domain_categories_and_outcomes.sql
├── 20260109000001_add_subscriptions_and_access_tiers.sql
├── 20260109000002_add_auth_hook_for_jwt_claims.sql
├── 20260115000001_add_fts_to_content_tables.sql
├── 20260115175736_fix_domain_stats_missing_columns.sql
├── 20260215000001_add_auth_sessions.sql
├── 20260215010353_remove_custom_sessions_use_supabase.sql
├── 20260222000001_fix_beacon_stats_snapshot_filtering.sql
└── 20260223000001_add_catalog_journey_tables.sql    # Catalog journeys

Naming Convention¶

YYYYMMDDHHMMSS_<description>.sql

Timestamp: UTC, ensures deterministic ordering across contributors.
Description: snake_case verb phrase (add_, fix_, remove_, create_).
All migrations are append-only — never edit a committed migration file.

What Goes in a Migration¶

Include	Exclude
`CREATE TABLE` / `ALTER TABLE`	Application data (use seed scripts)
`CREATE INDEX`	Test users / auth rows
RLS policies	Content that changes frequently
Trigger functions	Large JSONB blobs
RPC functions (stable, schema-level)
Minimal baseline rows (e.g., first published journey)

The one exception is 20241229000002_seed_agentic_ai_curriculum.sql, which seeds the full Agentic AI curriculum directly in the migration. This is acceptable because that curriculum is the canonical baseline and does not change; all subsequent content should use the seed scripts instead.

Creating a New Migration¶

# Generate a timestamped migration file
deno task db:new

# Or directly via Supabase CLI
supabase migration new <description>

Applying Migrations¶

# Apply pending migrations to local Supabase (automatic on `supabase start`)
deno task db:migrate

# Apply to production (requires supabase link)
deno task db:migrate:production

# Check current migration status
deno task db:status

# Reset local database and replay all migrations
deno task db:reset

# Generate a diff between local schema and remote
deno task db:diff

# Pull remote schema changes
deno task db:pull

Data Layers¶

Layer 1 — Migration Baseline Data¶

Some migrations include a minimal set of seed rows. These are always present after any database reset:

Migration	Baseline data seeded
`20241229000002_seed_agentic_ai_curriculum.sql`	1 domain, 3 trails, 12 concepts, 18 sparks, 18 beacons, prerequisite links, initial snapshot
`20260223000001_add_catalog_journey_tables.sql`	1 published catalog journey (`agentic-ai-mastery`)

Layer 2 — Content Seed Scripts¶

Deno scripts that enrich Layer 1 data or populate entire environments:

Script	Purpose	When to run
`tools/scripts/content/seed-curriculum.ts`	Seeds full Agentic AI curriculum (domains/trails/concepts/sparks/beacons)	Staging/production where migrations haven't seeded data
`tools/scripts/content/seed-catalog-journeys.ts`	Seeds catalog journey chapters, insights, enrollments, and ratings	All environments after migration

Layer 3 — Auth & Test User Seed¶

Auth seed creates auth.users rows and linked application data (subscriptions, journeys, events) needed for authenticated E2E tests. Never run in staging or production.

Script	Purpose
`tests/scripts/seed-test-users.ts`	Creates 4 test users (free, pro, premium, admin) with subscriptions, journeys, and learning events

Environment Profiles¶

Two named environments drive all seed and connection logic:

Profile	Supabase URL	Config file	When used
`local`	`http://127.0.0.1:54321`	`.env.local`	Local dev and testing
`staging`	`https://<project>.supabase.co`	`.env.staging`	Remote staging environment

Environment Variable Resolution¶

Each environment resolves connection credentials from a priority chain:

Local (.env.local):

URL  : SUPABASE_URL  →  default http://127.0.0.1:54321
KEY  : SUPABASE_SECRET_KEY  →  SUPABASE_SERVICE_ROLE_KEY  →  default local service_role JWT

Staging (.env.staging):

URL  : SUPABASE_URL  →  (required — no default)
KEY  : SUPABASE_SECRET_KEY  →  SUPABASE_SERVICE_ROLE_KEY  →  (required — no default)

Setup Files¶

Copy the corresponding example file and fill in values:

cp .env.local.example   .env.local    # local dev and testing
cp .env.staging.example .env.staging  # staging

.env.* files are git-ignored. Never commit credentials.

Seeding Reference¶

Catalog Journey Seed¶

Script: tools/scripts/content/seed-catalog-journeys.ts Targets: local, staging

Seeds the following tables for agentic-ai-mastery (UUID aaaaaaaa-0001-0001-0001-000000000001):

Table	Rows
`catalog_journeys`	1 (upsert — safe if migration already inserted it)
`catalog_chapters`	6
`catalog_journey_chapters`	6 links (ordered)
`catalog_insights`	3 (chapter 1 only)
`catalog_chapter_insights`	3 links
`catalog_journey_enrollments`	5 rows (local) · 10 rows (staging)
`catalog_journey_ratings`	5 rows (local) · 8 rows (staging)

Enrollments and ratings use synthetic UUIDs (bbbbbbbb-0001-…) — no FK constraint on user_id, so they provide realistic social-proof counts without requiring real auth users.

Resulting API values after seed¶

{
  "stats": {
    "chapterCount": 6,
    "insightCount": 0,
    "learnerCount": 5,
    "averageRating": 4.6,
    "ratingCount": 5,
    "totalTimeMinutes": 480
  }
}

Curriculum Seed¶

Script: tools/scripts/content/seed-curriculum.ts Targets: Staging / production (where the migration baseline may not have run)

Seeds domains, trails, concepts, sparks, beacons, and prerequisite links. The local environment gets this data from migration 20241229000002_seed_agentic_ai_curriculum.sql automatically.

Test User Seed¶

Script: tests/scripts/seed-test-users.ts Targets: Local only

User	Email	Role	Plan
Free	`test-free@example.com`	free	free
Pro	`test-pro@example.com`	pro	pro
Premium	`test-premium@example.com`	premium	premium
Admin	`test-admin@example.com`	admin	premium

Password for all test users: test123456

Each user is created with:

A subscription record in subscriptions
Sample journeys records (for free user)
Sample learning_events records (for free user)

Full Deno Task Reference¶

Database / Migration Tasks¶

Task	Description
`deno task db:migrate`	Apply pending migrations to local Supabase
`deno task db:migrate:production`	Apply pending migrations to production
`deno task db:reset`	Drop and recreate local database, replay all migrations
`deno task db:status`	Show applied/pending migration status
`deno task db:diff`	Generate SQL diff between local schema and remote
`deno task db:pull`	Pull remote schema changes into local migrations
`deno task db:new`	Create a new timestamped migration file

Content Seed Tasks¶

Task	Target env	Description
`deno task content:seed`	Staging/production	Seed full Agentic AI curriculum
`deno task content:seed:catalog`	Staging	Seed catalog journey (chapters, insights, enrollments, ratings)
`deno task content:seed:catalog:local`	Local	Seed catalog journey for local dev and testing
`deno task content:seed:catalog:staging`	Staging	Seed catalog journey for staging (explicit alias)

All content:seed:catalog:* tasks call the same unified script (tools/scripts/content/seed-catalog-journeys.ts) with the appropriate environment argument.

Dry-run¶

Preview what would be seeded without writing any data:

# Append -- --dry-run to any catalog seed task
deno task content:seed:catalog -- --dry-run
deno task content:seed:catalog:local -- --dry-run

Content Management Tasks¶

Task	Description
`deno task content:import`	Import a content bundle from the ETL pipeline
`deno task content:verify`	Verify snapshot integrity
`deno task content:promote`	Promote a draft snapshot to published
`deno task content:analyze`	Analyze content state and lineage
`deno task content:validate`	Validate content lineage rules

Local Environment Seed Tasks¶

Task	Description
`deno task local:seed`	Create test users in the local environment
`deno task local:seed:catalog`	Seed catalog journey data in the local environment
`deno task local:setup`	Full local setup: reset DB → seed users → seed catalog
`deno task local:reset`	Reset local database (replay migrations)
`deno task local:start`	Start the local Supabase instance
`deno task local:stop`	Stop the local Supabase instance

E2E Test Run Tasks¶

Task	Test suite
`deno task test:e2e:all`	All integration tests
`deno task test:e2e:graph`	Graph API (domains, trails, concepts, sparks, beacons)
`deno task test:e2e:content`	Content API (spark lessons)
`deno task test:e2e:catalog`	Catalog API (journey landing page)
`deno task test:e2e:user`	User API (`/me/*`)
`deno task test:e2e:snapshots`	Snapshots API
`deno task test:e2e:protection`	Auth and rate-limit protection
`deno task test:e2e:coverage`	All tests with LCOV coverage report
`deno task test:e2e:ci`	All tests with JUnit XML output (for CI)

Supabase Infrastructure Tasks¶

Task	Description
`deno task supabase:start`	Start local Supabase (Docker)
`deno task supabase:stop`	Stop local Supabase
`deno task supabase:restart`	Restart local Supabase
`deno task supabase:status`	Show running services and connection info
`deno task supabase:link`	Link project to a remote Supabase project
`deno task supabase:secrets`	Sync secrets to remote Supabase
`deno task supabase:logs`	Stream Supabase function logs

Smoke Test Tasks¶

Task	Description
`deno task smoke:local`	Smoke test against local Supabase
`deno task smoke:staging`	Smoke test against staging
`deno task smoke:production`	Smoke test against production

Workflows¶

First-Time Local Setup¶

# 1. Start Supabase (applies all migrations automatically)
supabase start

# 2. Seed catalog journey enrichment data (chapters, enrollments, ratings)
deno task content:seed:catalog:local

# 3. Start edge functions
deno task function:serve

# 4. Verify with smoke test
deno task smoke:local

Full Integration Test Setup¶

# 1. Start the local Supabase instance
deno task local:start

# 2. One-command setup: reset DB + seed users + seed catalog
deno task local:setup

# 3. Start edge functions in a separate terminal
deno task function:serve

# 4. Run catalog tests specifically
deno task test:e2e:catalog

# 5. Or run the full E2E suite
deno task test:e2e:all

Staging Hydration¶

Run after applying migrations to a fresh staging environment:

# 1. Ensure .env.staging is configured (copy from .env.staging.example)
cp .env.staging.example .env.staging
# Edit .env.staging with SUPABASE_URL and SUPABASE_SECRET_KEY

# 2. Apply migrations
deno task db:migrate:production

# 3. Seed curriculum (if not already seeded by migration)
deno task content:seed

# 4. Seed catalog journey data
deno task content:seed:catalog:staging

# 5. Verify with smoke test
deno task smoke:staging

Re-seeding After a Database Reset¶

# Replay all migrations (local)
deno task db:reset

# Re-seed catalog enrichment (migrations already re-seed baseline data)
deno task content:seed:catalog:local

# Re-seed test users (local env)
deno task local:setup

Adding New Seed Data¶

Adding to an Existing Seed Script¶

All seed scripts are idempotent. Add new rows to the data constants at the top of tools/scripts/content/seed-catalog-journeys.ts (for catalog data) or tests/scripts/seed-test-users.ts (for test users) and use upsert with onConflict.

Adding a New Seed Script¶

Follow the existing pattern:

Create the script in tools/scripts/content/ (for content/catalog data) or tests/scripts/ (for test setup data).
Export a run(env: SeedEnv, dryRun: boolean) function so the script is composable.
Use the environment profile pattern from seed-catalog-journeys.ts:
Load the appropriate .env.* file
Resolve URL and key via priority chain
Default to safe local values; require explicit values for staging

Add deno task entries in deno.json for each environment variant:

"content:seed:<name>":         ... staging
"content:seed:<name>:local":   ... local
"content:seed:<name>:staging": ... staging

Add the new task to local:setup if it must run before E2E tests.

Adding a Migration-Embedded Seed¶

Only embed seed rows in a migration when the data is:

Part of the schema contract (e.g., enum reference rows, function definitions)
Stable and unlikely to change after initial commit
Required for the application to start correctly

For all other data, prefer a standalone seed script.

File Reference¶

File	Role
`supabase/migrations/*.sql`	Schema changes and baseline data
`tools/scripts/content/seed-catalog-journeys.ts`	Unified multi-env catalog seed script
`tools/scripts/content/seed-catalog-journeys-staging.ts`	Staging shim (delegates to unified script)
`tools/scripts/content/seed-curriculum.ts`	Curriculum seed for staging/production
`tests/scripts/seed-catalog-journeys.ts`	Local shim (delegates to unified script)
`tests/scripts/seed-test-users.ts`	Local auth user seed
`.env.local.example`	Local dev and testing env template
`.env.staging.example`	Staging env template