Skip to content

Implementation & Migration Plan

Complete plan for migrating from the legacy graph model to the catalog content/discovery architecture.

Table of Contents

  1. Current State Inventory
  2. Migration Strategy
  3. Phase 0: Endpoint Contract Review
  4. Phase 1: Database Migration
  5. Phase 2: Shared Modules
  6. Phase 3: Topics Module + Edge Function
  7. Phase 4: Tags Module + Edge Function
  8. Phase 5: Insights Module + Edge Function
  9. Phase 6: Extend Journey Module
  10. Phase 7: Content Module Migration
  11. Phase 8: Explore + Search Migration
  12. Phase 9: Legacy Deprecation + Cleanup
  13. Phase 10: SDK Regeneration
  14. HTTP Test Files
  15. Reusable Components

Current State Inventory

Existing Edge Functions (20 total)

Function Path Module Tables Status After Migration
domains /domains/* src/modules/domains/ domains, domain_stats Deprecate → replaced by topics
trails via graph src/modules/trails/ trails, trail_stats Deprecate → replaced by journey-catalog
concepts /concepts/* src/modules/concepts/ concepts, concept_stats Deprecate → replaced by journey-catalog chapters
sparks-meta /sparks-meta/* src/modules/sparks/ sparks Deprecate → replaced by insights
beacons /beacons/* src/modules/beacons/ beacons, beacon_stats, spark_beacons Deprecate → replaced by tags
graph /graph/* src/routes/graph/ All legacy tables Deprecate → broken into individual functions
breadcrumb /breadcrumb/* src/routes/graph/breadcrumb.ts All legacy tables Deprecate → breadcrumbs not needed in catalog model
links /links/* src/routes/graph/links.ts spark_links Evaluate → may evolve to insight prerequisites
learning-path /path/* src/services/graph.ts sparks, spark_links Evolve → adapt to insight prerequisites
recommendations /recommendations/* src/modules/recommendations/ Various Evolve → adapt to catalog entities
journey-catalog /journeys/* src/modules/journeys/ catalog_* tables Extend → add list, topics, tags
journeys /journeys/* src/routes/me/journeys.ts journeys (user progress) Keep → user journey progress
content /content/* src/modules/content/ sparks, spark_versions Evolve → point to catalog_insights
explore /explore/* src/modules/explore/ Various legacy Evolve → use catalog + discovery tables
search /search/* src/services/search.ts Various legacy Evolve → search catalog entities
home /home/* src/modules/home/ domain_stats, etc. Evolve → use topics + catalog
user /me/* src/routes/me/ User tables Keep — no changes
auth /auth/* src/routes/auth/ Auth tables Keep — no changes
snapshots /snapshots/* src/routes/snapshots.ts snapshots Keep — no changes
metadata /metadata/* src/routes/metadata.ts Static Keep — no changes
health /health Built into createApp None Keep — no changes

Existing Shared Infrastructure (reuse as-is)

Component Location Purpose
createApp() functions/_shared/create-app.ts App factory with CORS, logging, service auth, health
cacheMiddleware() functions/_shared/cache.ts Cache-Control headers (STATIC/MODERATE/DYNAMIC)
optionalAuthMiddleware() functions/_shared/middleware.ts JWT extraction for optional auth
getSupabaseClient() functions/_shared/middleware.ts Supabase client from request context
getEnvBindingsSafe() functions/_shared/env-validation.ts Environment variable validation
serviceAuthMiddleware() src/middleware/service-auth.ts Service-to-service auth
errorResponse() src/routes/utils.ts Standardised error formatting
NotFoundError src/routes/utils.ts 404 exception class
PaginationQuerySchema src/schemas/openapi.ts Reusable pagination query params
PaginationMetaSchema src/schemas/openapi.ts Reusable pagination response meta
buildPaginationMeta() src/modules/shared/ Pagination meta builder
createPaginatedSchema() src/schemas/openapi.ts Factory for paginated response schemas

Migration Strategy

Guiding Principles

  1. All new endpoints are GET-only — optimised for CDN caching, stale-while-revalidate, and independent scalability.
  2. Each edge function is independently deployable — no shared runtime state between functions.
  3. Shared business logic lives in src/modules/ and src/modules/shared/ — reusable across any function that imports it.
  4. Legacy and new functions coexist during migration — no big bang cutover. Legacy functions get Deprecation headers before removal.
  5. Database migration is safe and locked — uses explicit transactions, advisory locks, and zero-downtime DDL (CREATE TABLE, CREATE INDEX CONCURRENTLY where possible).
  6. Every new endpoint contract is reviewed before implementation — Phase 0 produces .http files and schema definitions that stakeholders review.

Migration Order

Phase 0: Endpoint contract review (schemas + .http files only)
    │
Phase 1: Database migration (tables, indexes, RLS, seed data)
    │
Phase 2: Shared modules (reusable pagination, discovery helpers)
    │
Phase 3─5: New modules + edge functions (topics, tags, insights)  ←── can run in parallel
    │
Phase 6: Extend journey-catalog (list, topics, tags sub-routes)
    │
Phase 7: Content module migration (sparks → insights)
    │
Phase 8: Explore + Search migration (use catalog tables)
    │
Phase 9: Legacy deprecation + cleanup
    │
Phase 10: SDK regeneration

Phase 0: Endpoint Contract Review

Goal: Define every new endpoint's request/response contract BEFORE writing any service logic. Review via .http files with example payloads and Zod schema definitions.

0.1 — Topics Endpoint Contracts

Create schema definitions and HTTP test files for all 4 topics endpoints.

Files to create:

  • src/modules/topics/schema.ts — Zod schemas only (no service/routes yet)
  • http/topics.http — REST client test file with expected request/response shapes

Schemas to define:

TopicListQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)

TopicSlugParamSchema
  - slug: string (min 1)

TopicListItemSchema
  - id: uuid
  - slug: string
  - name: string
  - description: string | null
  - icon: string | null
  - color: string | null
  - heroImageUrl: string | null
  - displayOrder: number
  - journeyCount: number      ← computed from topic_journeys COUNT
  - insightCount: number      ← computed from topic_insights COUNT

TopicDetailSchema (extends TopicListItemSchema)
  - learningOutcomes: string[]

TopicJourneysQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)
  - featured_only: boolean (default false)

TopicJourneyItemSchema
  - id: uuid
  - slug: string
  - title: string
  - subtitle: string | null
  - difficulty: enum
  - totalDurationMinutes: number
  - isFeatured: boolean
  - displayOrder: number

TopicInsightsQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)
  - featured_only: boolean (default false)

TopicInsightItemSchema
  - id: uuid
  - slug: string
  - title: string
  - description: string | null
  - difficulty: enum | null
  - durationMinutes: number | null
  - isFeatured: boolean
  - displayOrder: number

HTTP test file (http/topics.http):

### List all published topics
GET {{baseUrl}}/topics/topics
Accept: application/json

### Expected response shape:
# {
#   "data": [
#     {
#       "id": "uuid",
#       "slug": "ai-fundamentals",
#       "name": "AI Fundamentals",
#       "description": "...",
#       "icon": "brain",
#       "color": "#6366F1",
#       "heroImageUrl": null,
#       "displayOrder": 0,
#       "journeyCount": 3,
#       "insightCount": 12
#     }
#   ],
#   "meta": { "page": 1, "per_page": 20, "total": 5, ... }
# }

###

### Get topic details
GET {{baseUrl}}/topics/topics/ai-fundamentals
Accept: application/json

###

### Get journeys for a topic
GET {{baseUrl}}/topics/topics/ai-fundamentals/journeys?per_page=10
Accept: application/json

###

### Get journeys for a topic (featured only)
GET {{baseUrl}}/topics/topics/ai-fundamentals/journeys?featured_only=true
Accept: application/json

###

### Get insights for a topic
GET {{baseUrl}}/topics/topics/ai-fundamentals/insights?per_page=10
Accept: application/json

###

### Get insights for a topic (filter by difficulty)
GET {{baseUrl}}/topics/topics/ai-fundamentals/insights?difficulty=beginner
Accept: application/json

0.2 — Tags Endpoint Contracts

Files to create:

  • src/modules/tags/schema.ts
  • http/tags.http

Schemas to define:

TagListQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - category: enum (optional filter: technology | concept | application | level | format | general)
  - search: string (optional — ILIKE on name)

TagSlugParamSchema
  - slug: string (min 1)

TagListItemSchema
  - id: uuid
  - slug: string
  - name: string
  - description: string | null
  - category: string
  - journeyCount: number      ← computed from tag_journeys COUNT
  - insightCount: number      ← computed from tag_insights COUNT

TagDetailSchema (same as TagListItemSchema)

TagJourneysQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)

TagJourneyItemSchema
  - id: uuid
  - slug: string
  - title: string
  - subtitle: string | null
  - difficulty: enum
  - totalDurationMinutes: number

TagInsightsQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)

TagInsightItemSchema
  - id: uuid
  - slug: string
  - title: string
  - description: string | null
  - difficulty: enum | null
  - durationMinutes: number | null

HTTP test file (http/tags.http):

### List all tags
GET {{baseUrl}}/tags/tags
Accept: application/json

###

### List tags filtered by category
GET {{baseUrl}}/tags/tags?category=technology
Accept: application/json

###

### Search tags by name
GET {{baseUrl}}/tags/tags?search=python
Accept: application/json

###

### Get tag details
GET {{baseUrl}}/tags/tags/machine-learning
Accept: application/json

###

### Get journeys with this tag
GET {{baseUrl}}/tags/tags/machine-learning/journeys
Accept: application/json

###

### Get insights with this tag
GET {{baseUrl}}/tags/tags/machine-learning/insights
Accept: application/json

0.3 — Insights Endpoint Contracts

Files to create:

  • src/modules/insights/schema.ts
  • http/insights.http

Schemas to define:

InsightListQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)
  - topic: string (optional — filter by topic slug via topic_insights)
  - tag: string (optional — filter by tag slug via tag_insights)

InsightSlugParamSchema
  - slug: string (min 1)

InsightListItemSchema
  - id: uuid
  - slug: string
  - title: string
  - description: string | null
  - difficulty: enum | null
  - durationMinutes: number | null
  - wordCount: number | null
  - isPremium: boolean

InsightDetailSchema (extends InsightListItemSchema)
  - topicCount: number        ← computed from topic_insights
  - tagCount: number          ← computed from tag_insights
  - chapterCount: number      ← computed from catalog_chapter_insights (how many chapters contain this)

InsightTopicsResponseSchema
  - topics: array of { id, slug, name, icon, color }

InsightTagsResponseSchema
  - tags: array of { id, slug, name, category }

HTTP test file (http/insights.http):

### List all published insights
GET {{baseUrl}}/insights/insights
Accept: application/json

###

### List insights filtered by difficulty
GET {{baseUrl}}/insights/insights?difficulty=beginner
Accept: application/json

###

### List insights filtered by topic
GET {{baseUrl}}/insights/insights?topic=ai-fundamentals
Accept: application/json

###

### List insights filtered by tag
GET {{baseUrl}}/insights/insights?tag=python
Accept: application/json

###

### Get insight metadata
GET {{baseUrl}}/insights/insights/what-is-an-ai-agent
Accept: application/json

###

### Get topics for an insight
GET {{baseUrl}}/insights/insights/what-is-an-ai-agent/topics
Accept: application/json

###

### Get tags for an insight
GET {{baseUrl}}/insights/insights/what-is-an-ai-agent/tags
Accept: application/json

0.4 — Journey Extension Contracts

Files to extend:

  • src/modules/journeys/schema.ts — Add list and reverse-lookup schemas
  • http/journeys.http — Extend with new endpoints

New schemas to define:

JourneyListQuerySchema
  - page: number (default 1)
  - per_page: number (default 20, max 100)
  - difficulty: enum (optional filter)
  - topic: string (optional — filter by topic slug via topic_journeys)
  - tag: string (optional — filter by tag slug via tag_journeys)
  - sort: enum (optional — recent | popular | alphabetical)

JourneyListItemSchema
  - id: uuid
  - slug: string
  - title: string
  - subtitle: string | null
  - difficulty: enum
  - totalDurationMinutes: number
  - isPremium: boolean
  - chapterCount: number
  - insightCount: number

JourneyTopicsResponseSchema
  - topics: array of { id, slug, name, icon, color }

JourneyTagsResponseSchema
  - tags: array of { id, slug, name, category }

HTTP test additions (http/journeys.http):

### List all published journeys
GET {{baseUrl}}/journey-catalog/journeys
Accept: application/json

###

### List journeys filtered by difficulty
GET {{baseUrl}}/journey-catalog/journeys?difficulty=intermediate
Accept: application/json

###

### List journeys filtered by topic
GET {{baseUrl}}/journey-catalog/journeys?topic=ai-fundamentals
Accept: application/json

###

### List journeys filtered by tag
GET {{baseUrl}}/journey-catalog/journeys?tag=python
Accept: application/json

###

### Get topics for a journey (reverse lookup)
GET {{baseUrl}}/journey-catalog/journeys/agentic-ai-mastery/topics
Accept: application/json

###

### Get tags for a journey (reverse lookup)
GET {{baseUrl}}/journey-catalog/journeys/agentic-ai-mastery/tags
Accept: application/json

0.5 — Review Checklist

Before proceeding to Phase 1, review each contract against these criteria:

  • Every endpoint is GET-only
  • Every response follows { data, meta } (lists) or { data } (details) convention
  • Every list endpoint supports page and per_page pagination
  • Every filterable field uses query parameters (not path segments)
  • All slugs follow ^[a-z0-9]+(-[a-z0-9]+)*$ format
  • All response field names use camelCase (not snake_case)
  • No endpoint requires authentication (all public or optional auth)
  • Cache policy is assigned (STATIC for rarely-changing, MODERATE for content, DYNAMIC for filtered)
  • Error responses follow { error: { code, message } } convention
  • No endpoint returns content body (that's the content function's job)
  • Reverse-lookup responses are simple arrays (not paginated — small cardinality)

Phase 1: Database Migration

Goal: Create all discovery tables with proper locking, indexes, RLS, and seed data.

1.1 — Migration File

File: supabase/migrations/YYYYMMDD_add_discovery_layer.sql

The migration uses an explicit transaction with an advisory lock to ensure no concurrent migrations can run and all changes are atomic.

-- ============================================
-- DISCOVERY LAYER MIGRATION
-- ============================================
-- Adds Topics and Tags as discovery mechanisms for catalog content.
-- Uses advisory lock to prevent concurrent migration execution.
--
-- Tables created:
--   topics, tags (discovery entities)
--   topic_journeys, topic_insights (topic associations)
--   tag_journeys, tag_insights (tag associations)
--
-- All operations are CREATE (additive only) — no existing tables modified.
-- ============================================

BEGIN;

-- Advisory lock to prevent concurrent migrations
-- Uses a fixed lock ID derived from the migration name
SELECT pg_advisory_xact_lock(hashtext('add_discovery_layer'));

-- ============================================
-- 1. TOPICS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS topics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7),
    hero_image_url TEXT,
    display_order INTEGER NOT NULL DEFAULT 0,
    learning_outcomes JSONB DEFAULT '[]'::jsonb,
    status TEXT NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft', 'published', 'archived')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT topics_slug_format CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
    CONSTRAINT topics_color_format CHECK (color IS NULL OR color ~ '^#[0-9A-Fa-f]{6}$')
);

CREATE INDEX IF NOT EXISTS idx_topics_slug ON topics(slug);
CREATE INDEX IF NOT EXISTS idx_topics_status ON topics(status) WHERE status = 'published';
CREATE INDEX IF NOT EXISTS idx_topics_display_order ON topics(display_order)
    WHERE status = 'published';

COMMENT ON TABLE topics IS
    'Curated, user-facing discovery categories for browsing content by subject area';

-- ============================================
-- 2. TAGS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(200),
    category VARCHAR(50) NOT NULL DEFAULT 'general'
        CHECK (category IN (
            'technology', 'concept', 'application',
            'level', 'format', 'general'
        )),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT tags_slug_format CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$')
);

CREATE INDEX IF NOT EXISTS idx_tags_slug ON tags(slug);
CREATE INDEX IF NOT EXISTS idx_tags_category ON tags(category);
CREATE INDEX IF NOT EXISTS idx_tags_name_trgm ON tags USING GIN(name gin_trgm_ops);

COMMENT ON TABLE tags IS
    'System-facing classification keywords for programmatic discovery and faceted search';

-- ============================================
-- 3. TOPIC_JOURNEYS JUNCTION
-- ============================================

CREATE TABLE IF NOT EXISTS topic_journeys (
    topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    journey_id UUID NOT NULL REFERENCES catalog_journeys(id) ON DELETE CASCADE,
    display_order INTEGER NOT NULL DEFAULT 0,
    is_featured BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (topic_id, journey_id)
);

CREATE INDEX IF NOT EXISTS idx_topic_journeys_journey
    ON topic_journeys(journey_id);
CREATE INDEX IF NOT EXISTS idx_topic_journeys_order
    ON topic_journeys(topic_id, display_order);

COMMENT ON TABLE topic_journeys IS
    'Many-to-many: which journeys appear under which topics';

-- ============================================
-- 4. TOPIC_INSIGHTS JUNCTION
-- ============================================

CREATE TABLE IF NOT EXISTS topic_insights (
    topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    insight_id UUID NOT NULL REFERENCES catalog_insights(id) ON DELETE CASCADE,
    display_order INTEGER NOT NULL DEFAULT 0,
    is_featured BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (topic_id, insight_id)
);

CREATE INDEX IF NOT EXISTS idx_topic_insights_insight
    ON topic_insights(insight_id);
CREATE INDEX IF NOT EXISTS idx_topic_insights_order
    ON topic_insights(topic_id, display_order);

COMMENT ON TABLE topic_insights IS
    'Many-to-many: which standalone insights appear under which topics';

-- ============================================
-- 5. TAG_JOURNEYS JUNCTION
-- ============================================

CREATE TABLE IF NOT EXISTS tag_journeys (
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    journey_id UUID NOT NULL REFERENCES catalog_journeys(id) ON DELETE CASCADE,
    PRIMARY KEY (tag_id, journey_id)
);

CREATE INDEX IF NOT EXISTS idx_tag_journeys_journey
    ON tag_journeys(journey_id);

COMMENT ON TABLE tag_journeys IS
    'Many-to-many: which tags classify which journeys';

-- ============================================
-- 6. TAG_INSIGHTS JUNCTION
-- ============================================

CREATE TABLE IF NOT EXISTS tag_insights (
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    insight_id UUID NOT NULL REFERENCES catalog_insights(id) ON DELETE CASCADE,
    PRIMARY KEY (tag_id, insight_id)
);

CREATE INDEX IF NOT EXISTS idx_tag_insights_insight
    ON tag_insights(insight_id);

COMMENT ON TABLE tag_insights IS
    'Many-to-many: which tags classify which insights';

-- ============================================
-- 7. ROW LEVEL SECURITY
-- ============================================

ALTER TABLE topics ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view published topics"
    ON topics FOR SELECT USING (status = 'published');
CREATE POLICY "Service role has full access to topics"
    ON topics FOR ALL TO service_role USING (true) WITH CHECK (true);

ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view tags"
    ON tags FOR SELECT USING (true);
CREATE POLICY "Service role has full access to tags"
    ON tags FOR ALL TO service_role USING (true) WITH CHECK (true);

ALTER TABLE topic_journeys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view topic_journeys"
    ON topic_journeys FOR SELECT USING (true);
CREATE POLICY "Service role manages topic_journeys"
    ON topic_journeys FOR ALL TO service_role
    USING (true) WITH CHECK (true);

ALTER TABLE topic_insights ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view topic_insights"
    ON topic_insights FOR SELECT USING (true);
CREATE POLICY "Service role manages topic_insights"
    ON topic_insights FOR ALL TO service_role
    USING (true) WITH CHECK (true);

ALTER TABLE tag_journeys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view tag_journeys"
    ON tag_journeys FOR SELECT USING (true);
CREATE POLICY "Service role manages tag_journeys"
    ON tag_journeys FOR ALL TO service_role
    USING (true) WITH CHECK (true);

ALTER TABLE tag_insights ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view tag_insights"
    ON tag_insights FOR SELECT USING (true);
CREATE POLICY "Service role manages tag_insights"
    ON tag_insights FOR ALL TO service_role
    USING (true) WITH CHECK (true);

-- ============================================
-- 8. UPDATE TRIGGERS
-- ============================================

CREATE TRIGGER update_topics_updated_at
    BEFORE UPDATE ON topics
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_tags_updated_at
    BEFORE UPDATE ON tags
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- 9. TOPIC STATS VIEW
-- ============================================
-- Provides pre-aggregated counts for topic listing.
-- Uses LEFT JOIN to include topics with zero associations.

CREATE OR REPLACE VIEW topic_stats AS
SELECT
    t.id,
    t.slug,
    t.name,
    t.description,
    t.icon,
    t.color,
    t.hero_image_url,
    t.display_order,
    t.learning_outcomes,
    t.status,
    t.created_at,
    t.updated_at,
    COUNT(DISTINCT tj.journey_id) AS journey_count,
    COUNT(DISTINCT ti.insight_id) AS insight_count
FROM topics t
LEFT JOIN topic_journeys tj ON t.id = tj.topic_id
LEFT JOIN topic_insights ti ON t.id = ti.topic_id
GROUP BY t.id;

-- ============================================
-- 10. TAG STATS VIEW
-- ============================================

CREATE OR REPLACE VIEW tag_stats AS
SELECT
    tg.id,
    tg.slug,
    tg.name,
    tg.description,
    tg.category,
    tg.created_at,
    tg.updated_at,
    COUNT(DISTINCT tj.journey_id) AS journey_count,
    COUNT(DISTINCT ti.insight_id) AS insight_count
FROM tags tg
LEFT JOIN tag_journeys tj ON tg.id = tj.tag_id
LEFT JOIN tag_insights ti ON tg.id = ti.tag_id
GROUP BY tg.id;

COMMIT;

1.2 — Data Seed Migration

File: supabase/migrations/YYYYMMDD_seed_discovery_data.sql

Seeds initial topics from existing domains data and tags from existing beacons data. This runs in a separate migration so the schema migration is clean.

BEGIN;

SELECT pg_advisory_xact_lock(hashtext('seed_discovery_data'));

-- ============================================
-- SEED TOPICS FROM DOMAINS
-- ============================================
-- Copies published domains into topics, preserving slug/name/icon/color.
-- Only runs if topics table is empty (idempotent).

INSERT INTO topics (slug, name, description, icon, color, display_order, learning_outcomes, status)
SELECT
    d.slug,
    d.name,
    d.description,
    d.icon,
    d.color,
    ROW_NUMBER() OVER (ORDER BY ds.spark_count DESC) - 1 AS display_order,
    COALESCE(d.learning_outcomes, '[]'::jsonb),
    'published'
FROM domains d
JOIN domain_stats ds ON d.id = ds.id
WHERE d.is_published = true
  AND d.snapshot_id = (SELECT snapshot_id FROM current_snapshot WHERE id = 1)
  AND NOT EXISTS (SELECT 1 FROM topics LIMIT 1)
ORDER BY ds.spark_count DESC;

-- ============================================
-- SEED TAGS FROM BEACONS
-- ============================================

INSERT INTO tags (slug, name, description, category)
SELECT
    b.slug,
    b.name,
    b.description,
    b.category::text
FROM beacons b
WHERE b.snapshot_id = (SELECT snapshot_id FROM current_snapshot WHERE id = 1)
  AND NOT EXISTS (SELECT 1 FROM tags LIMIT 1);

COMMIT;

1.3 — Database Lock Strategy

The migration uses these safety mechanisms:

Mechanism Purpose
BEGIN / COMMIT All DDL is atomic — either all tables are created or none
pg_advisory_xact_lock() Prevents concurrent migration execution
CREATE TABLE IF NOT EXISTS Idempotent — safe to re-run
CREATE INDEX IF NOT EXISTS Idempotent index creation
No ALTER TABLE on existing tables Zero risk to existing data
No DROP statements Nothing is removed during creation
Separate seed migration Schema changes are isolated from data operations

What is NOT locked:

  • Existing tables are not touched (no ALTER TABLE, no DROP COLUMN)
  • No existing indexes are modified
  • No existing RLS policies are changed
  • No existing views are modified

Phase 2: Shared Modules

Goal: Extract reusable patterns into shared modules before building entity modules.

2.1 — Discovery Helpers

File: src/modules/shared/discovery.ts

Reusable functions for querying junction tables that follow the same pattern across all discovery endpoints.

/**
 * Shared helpers for discovery context queries.
 *
 * These functions standardise how junction tables are queried
 * for both forward lookups (topic → journeys) and reverse lookups
 * (journey → topics). All queries are GET-optimised read-only
 * operations designed for CDN caching.
 */

// getJourneysForDiscoveryEntity(supabase, junctionTable, entityIdColumn, entityId, filters)
// getInsightsForDiscoveryEntity(supabase, junctionTable, entityIdColumn, entityId, filters)
// getTopicsForEntity(supabase, entityTable, entityIdColumn, entityId)
// getTagsForEntity(supabase, entityTable, entityIdColumn, entityId)

2.2 — Reusable Response Schemas

File: src/modules/shared/schemas.ts

/**
 * Shared schemas for discovery and reverse-lookup responses.
 * Used across topics, tags, insights, and journey modules.
 */

// TopicRefSchema — { id, slug, name, icon, color }
// TagRefSchema — { id, slug, name, category }
// JourneyRefSchema — { id, slug, title, subtitle, difficulty, totalDurationMinutes }
// InsightRefSchema — { id, slug, title, description, difficulty, durationMinutes }

Phase 3: Topics Module + Edge Function

Goal: Build the complete topics module following established patterns.

3.1 — Module Files

File Purpose Key Exports
src/modules/topics/schema.ts Zod schemas (from Phase 0) All Topic*Schema
src/modules/topics/types.ts Service interface ITopicService
src/modules/topics/service.ts Business logic createTopicService()
src/modules/topics/routes.ts OpenAPI route definitions Default Hono app
src/modules/topics/index.ts Module barrel export Re-exports routes + service

3.2 — Service Methods

interface ITopicService {
  /** GET /topics — List published topics with journey/insight counts */
  list(query: TopicListQuery): Promise<PaginatedResponse<TopicListItem>>;

  /** GET /topics/:slug — Get topic details */
  getBySlug(slug: string): Promise<TopicDetail | null>;

  /** GET /topics/:slug/journeys — Journeys curated under this topic */
  getJourneys(
    slug: string,
    query: TopicJourneysQuery,
  ): Promise<PaginatedResponse<TopicJourneyItem>>;

  /** GET /topics/:slug/insights — Insights curated under this topic */
  getInsights(
    slug: string,
    query: TopicInsightsQuery,
  ): Promise<PaginatedResponse<TopicInsightItem>>;
}

3.3 — Edge Function

File: functions/topics/index.ts

import { createApp } from "@shared/create-app.ts";
import { getEnvBindingsSafe } from "@shared/env-validation.ts";
import {
  CACHE_MODERATE,
  CACHE_STATIC,
  cacheMiddleware,
} from "@shared/cache.ts";
import topicsRoutes from "@domains/graph/topics/index.ts";

const app = createApp({
  functionName: "topics",
  includeHealthCheck: true,
});

// Topic listing and details rarely change
app.use("/topics", cacheMiddleware(CACHE_STATIC));
app.use("/topics/:slug", cacheMiddleware(CACHE_STATIC));

// Sub-resources change more frequently
app.use("/topics/:slug/journeys", cacheMiddleware(CACHE_MODERATE));
app.use("/topics/:slug/insights", cacheMiddleware(CACHE_MODERATE));

app.route("/topics", topicsRoutes);

const bindings = getEnvBindingsSafe();
Deno.serve((req) => app.fetch(req, bindings));

3.4 — Route Definitions (4 GET endpoints)

Route operationId Cache Query Params
GET / v1_list_topics STATIC page, per_page
GET /:slug v1_get_topic STATIC —
GET /:slug/journeys v1_get_topic_journeys MODERATE page, per_page, difficulty, featured_only
GET /:slug/insights v1_get_topic_insights MODERATE page, per_page, difficulty, featured_only

Phase 4: Tags Module + Edge Function

Goal: Build the complete tags module. Same pattern as topics.

4.1 — Module Files

Same structure as topics: src/modules/tags/{schema,types,service,routes,index}.ts

4.2 — Service Methods

interface ITagService {
  /** GET /tags — List tags with usage counts */
  list(query: TagListQuery): Promise<PaginatedResponse<TagListItem>>;

  /** GET /tags/:slug — Get tag details */
  getBySlug(slug: string): Promise<TagDetail | null>;

  /** GET /tags/:slug/journeys — Journeys classified with this tag */
  getJourneys(
    slug: string,
    query: TagJourneysQuery,
  ): Promise<PaginatedResponse<TagJourneyItem>>;

  /** GET /tags/:slug/insights — Insights classified with this tag */
  getInsights(
    slug: string,
    query: TagInsightsQuery,
  ): Promise<PaginatedResponse<TagInsightItem>>;
}

4.3 — Edge Function

File: functions/tags/index.ts

Same pattern as topics. Uses CACHE_STATIC for list/detail, CACHE_MODERATE for sub-resources.

4.4 — Route Definitions (4 GET endpoints)

Route operationId Cache Query Params
GET / v1_list_tags STATIC page, per_page, category, search
GET /:slug v1_get_tag STATIC —
GET /:slug/journeys v1_get_tag_journeys MODERATE page, per_page, difficulty
GET /:slug/insights v1_get_tag_insights MODERATE page, per_page, difficulty

Phase 5: Insights Module + Edge Function

Goal: Build standalone insight metadata endpoints (distinct from content delivery).

5.1 — Module Files

src/modules/insights/{schema,types,service,routes,index}.ts

5.2 — Service Methods

interface IInsightService {
  /** GET /insights — List published insights with filters */
  list(query: InsightListQuery): Promise<PaginatedResponse<InsightListItem>>;

  /** GET /insights/:slug — Get insight metadata */
  getBySlug(slug: string): Promise<InsightDetail | null>;

  /** GET /insights/:slug/topics — Topics this insight belongs to */
  getTopics(slug: string): Promise<TopicRef[]>;

  /** GET /insights/:slug/tags — Tags on this insight */
  getTags(slug: string): Promise<TagRef[]>;
}

5.3 — Edge Function

File: functions/insights/index.ts

Uses CACHE_MODERATE for all endpoints since insight metadata changes more frequently than topics/tags.

5.4 — Route Definitions (4 GET endpoints)

Route operationId Cache Query Params
GET / v1_list_insights MODERATE page, per_page, difficulty, topic, tag
GET /:slug v1_get_insight MODERATE —
GET /:slug/topics v1_get_insight_topics MODERATE —
GET /:slug/tags v1_get_insight_tags MODERATE —

Design note on reverse lookups: The /topics and /tags sub-routes return simple arrays (not paginated) because the cardinality is small — an insight typically belongs to 1-5 topics and has 3-10 tags.

Phase 6: Extend Journey Module

Goal: Add list endpoint and reverse-lookup sub-routes to the existing journey-catalog function.

6.1 — New Service Methods

Add to the existing JourneyService:

// Add to IJourneyService
list(query: JourneyListQuery): Promise<PaginatedResponse<JourneyListItem>>;
getTopics(journeyId: string): Promise<TopicRef[]>;
getTags(journeyId: string): Promise<TagRef[]>;

6.2 — New Routes (3 GET endpoints)

Add to src/modules/journeys/routes.ts:

Route operationId Cache Query Params
GET / v1_list_journeys MODERATE page, per_page, difficulty, topic, tag, sort
GET /:id/topics v1_get_journey_topics MODERATE —
GET /:id/tags v1_get_journey_tags MODERATE —

6.3 — Edge Function Update

Update functions/journey-catalog/index.ts to mount the extended routes. The existing GET /:id and GET /:id/chapters routes remain unchanged.

Phase 7: Content Module Migration

Goal: Migrate the content delivery module from sparks to catalog_insights.

7.1 — Content Module Changes

The existing content module (src/modules/content/) currently queries the sparks table. It needs to:

  1. Add new routes at /insights/{slug} that query catalog_insights
  2. Keep existing /sparks/{slug} routes functional (backward compatibility)
  3. Map the catalog_insights content fields to the same response format

7.2 — Edge Function

The existing functions/content/index.ts mounts content routes. Add the insights path:

// Existing: app.route("/sparks", contentRoutes);
// New:      app.route("/insights", insightContentRoutes);

7.3 — Service Changes

The content service needs a parallel path for catalog_insights. Since catalog_insights may need content fields (content_md, content_html) added, evaluate whether to:

  1. Add content columns to catalog_insights directly
  2. Create a separate catalog_insight_content table
  3. Bridge through existing sparks table via a mapping table

Decision deferred to implementation — depends on content ingestion pipeline.

Phase 8: Explore + Search Migration

Goal: Update explore and search to use catalog entities + discovery tables.

8.1 — Explore Module Changes

The explore module currently queries domain_stats, trail_stats, concept_stats, sparks. Migration:

Current Query New Query
domain_stats topic_stats
trail_stats catalog_journeys + counts
concept_stats Not applicable (chapters not discoverable)
sparks catalog_insights

Facet additions:

  • Add topic filter (via topic_journeys / topic_insights)
  • Add tag filter (via tag_journeys / tag_insights)
  • Remove domain filter (replaced by topic)
  • Remove trail filter (use topic instead)

8.2 — Search Module Changes

The search service currently uses search_vector on legacy tables. Migration:

  1. Add full-text search columns to catalog_journeys and catalog_insights
  2. Update search service to query catalog tables
  3. Add topic/tag filtering to search results
  4. Keep legacy search functional during transition

New search targets:

  • catalog_journeys.search_vector (title, description)
  • catalog_insights.search_vector (title, description)
  • topics.name (for topic suggestions in autocomplete)
-- Add search vectors to catalog tables
ALTER TABLE catalog_journeys ADD COLUMN IF NOT EXISTS
    search_vector tsvector;
ALTER TABLE catalog_insights ADD COLUMN IF NOT EXISTS
    search_vector tsvector;

-- Create GIN indexes
CREATE INDEX IF NOT EXISTS idx_catalog_journeys_search
    ON catalog_journeys USING GIN(search_vector);
CREATE INDEX IF NOT EXISTS idx_catalog_insights_search
    ON catalog_insights USING GIN(search_vector);

-- Auto-update triggers
CREATE OR REPLACE FUNCTION update_catalog_journey_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER catalog_journeys_search_update
    BEFORE INSERT OR UPDATE OF title, description
    ON catalog_journeys
    FOR EACH ROW EXECUTE FUNCTION update_catalog_journey_search_vector();

Phase 9: Legacy Deprecation + Cleanup

Goal: Mark legacy endpoints as deprecated, then remove after client migration.

9.1 — Deprecation Headers

Add to all legacy edge functions:

// In each legacy function (domains, trails, concepts, sparks-meta, beacons, graph)
app.use("/*", async (c, next) => {
  await next();
  c.header("Deprecation", "true");
  c.header("Sunset", "2026-06-01"); // 3 months notice
  c.header("Link", '</topics>; rel="successor-version"');
});

9.2 — Legacy Function → Replacement Mapping

Legacy Function Replacement Migration Path
domains topics Update SDK clients to use /topics
trails → list journey-catalog → GET /journeys Update SDK
trails → detail journey-catalog → GET /journeys/:id Update SDK
concepts → list journey-catalog → GET /journeys/:id/chapters Chapters via journey
sparks-meta → list insights → GET /insights Update SDK
sparks-meta → detail insights → GET /insights/:slug Update SDK
beacons tags Update SDK clients to use /tags
graph Individual functions Already decomposed
breadcrumb Not needed Breadcrumbs computed client-side
links Future insight prerequisites Evolve separately

9.3 — Cleanup (after sunset date)

  1. Remove legacy edge function directories
  2. Remove legacy module directories (src/modules/legacy-*)
  3. Remove legacy route files
  4. Drop legacy database views (domain_stats, trail_stats, concept_stats, beacon_stats)
  5. Evaluate whether to drop legacy tables or keep for historical reference

Do NOT drop legacy tables in the same migration as the cleanup. Keep them for at least one more release cycle in case rollback is needed.

Phase 10: SDK Regeneration

Goal: Regenerate the OpenAPI spec and TypeScript SDK with new endpoints.

10.1 — Steps

  1. Run deno task openapi:offline to regenerate mcp/openapi.json
  2. Verify all new endpoints appear with correct operationIds
  3. Run deno task sdk:build to regenerate SDK
  4. Verify SDK exports new endpoint classes: Topics, Tags, Insights
  5. Verify SDK preserves existing endpoint classes with deprecation markers
  6. Update sdk/SDK_REFERENCE.md with new endpoint documentation

10.2 — SDK Client Usage (target state)

import { createClient } from "@musingly-ai/core";

const client = createClient({ baseUrl: "https://api.musingly.ai" });

// Discovery
const { data: topics } = await client.topics.listTopics();
const { data: journeys } = await client.topics.getTopicJourneys(
  "ai-fundamentals",
);

// Content browsing
const { data: insight } = await client.insights.getInsight(
  "what-is-an-ai-agent",
);
const { data: tags } = await client.insights.getInsightTags(
  "what-is-an-ai-agent",
);

// Journey deep-dive
const { data: chapters } = await client.journeys.getJourneyChapters(
  "agentic-ai-mastery",
);

HTTP Test Files

Complete set of HTTP test files for all new and modified endpoints:

File Endpoints Tested Phase
http/topics.http 4 GET endpoints Phase 0 (contract) → Phase 3 (validation)
http/tags.http 4 GET endpoints Phase 0 (contract) → Phase 4 (validation)
http/insights.http 4 GET endpoints Phase 0 (contract) → Phase 5 (validation)
http/journeys.http (extend) 3 new GET endpoints Phase 0 (contract) → Phase 6 (validation)

Reusable Components

Shared Between All New Modules

Component Location Used By
createApp() functions/_shared/create-app.ts All edge functions
cacheMiddleware() functions/_shared/cache.ts All edge functions
getEnvBindingsSafe() functions/_shared/env-validation.ts All edge functions
PaginationQuerySchema src/schemas/openapi.ts All list endpoints
buildPaginationMeta() src/modules/shared/ All list endpoints
errorResponse() src/routes/utils.ts All route handlers
NotFoundError src/routes/utils.ts All detail endpoints
getSupabaseClient() src/routes/utils.ts All service instantiation

New Shared Components (Phase 2)

Component Location Used By
TopicRefSchema src/modules/shared/schemas.ts insights, journeys (reverse lookups)
TagRefSchema src/modules/shared/schemas.ts insights, journeys (reverse lookups)
JourneyRefSchema src/modules/shared/schemas.ts topics, tags (forward lookups)
InsightRefSchema src/modules/shared/schemas.ts topics, tags (forward lookups)
getTopicsForEntity() src/modules/shared/discovery.ts insights, journeys
getTagsForEntity() src/modules/shared/discovery.ts insights, journeys
getJourneysForDiscovery() src/modules/shared/discovery.ts topics, tags
getInsightsForDiscovery() src/modules/shared/discovery.ts topics, tags

Independence Guarantee

Each edge function is independently deployable because:

  1. No shared runtime state — each function creates its own Hono app via createApp()
  2. No shared database connections — each request creates a fresh Supabase client
  3. Modules are imported at build time — Deno bundles only the code each function needs
  4. Cache policies are per-function — each function chooses its own cache strategy
  5. Health checks are per-function — each function has GET /{name}/health
  6. Business logic is in modules — functions are thin wrappers around modules

What IS shared (at build time, not runtime):

  • TypeScript types and Zod schemas (compile-time only)
  • Service classes (instantiated per-request, not per-function)
  • Utility functions (pure functions, no state)
  • Middleware factories (return new middleware per invocation)

Summary: Total Work Items

Phase Files Created Files Modified Endpoints Tables
Phase 0 4 schema files, 4 .http files — — (contracts only) —
Phase 1 2 migration files — — 6 tables, 2 views
Phase 2 2 shared modules — — —
Phase 3 5 module files, 1 function file — 4 GET —
Phase 4 5 module files, 1 function file — 4 GET —
Phase 5 5 module files, 1 function file — 4 GET —
Phase 6 — 3 module files, 1 function file 3 GET —
Phase 7 1 module file 2 module files, 1 function file 1 GET (new path) —
Phase 8 1 migration file 2 service files — (modified) 2 columns, 2 indexes
Phase 9 — ~8 legacy functions — (deprecated) —
Phase 10 — SDK regeneration — —
Total ~30 new files ~20 modified files 16 new GET endpoints 6 new tables