Skip to content

plan-supabaseRedisDataLayer.prompt.md

Latest commit

 

History

History
227 lines (176 loc) · 7.06 KB

plan-supabaseRedisDataLayer.prompt.md

File metadata and controls

227 lines (176 loc) · 7.06 KB

Plan: Supabase + Redis Data Layer

Overview

Implement the two-tier storage architecture with:

  • Redis (ioredis) → Active game state (hot, 24hr TTL)
  • Supabase (supabase-js) → Users, history, content, invites (cold)

No ORM needed. Supabase-js provides typed query builder + type generation from schema.

Architecture

Backend Services (NestJS)
    ├── ioredis              → Active game state
    ├── @supabase/supabase-js → Users, history, content, invites
    └── Supabase Auth         → User authentication

PostgreSQL Tables (Supabase)

Table Purpose Key Fields
users Managed by Supabase Auth id, email, created_at
profiles Extended user data user_id, display_name, avatar_url
game_history Archived completed games id, user_ids[], final_state (JSONB), completed_at
game_variants Game content (variants) id, template_id, content (JSONB), is_published
stories Narrative content id, variant_id, content (JSONB)
story_translations Localized content story_id, locale, content (JSONB)
invite_links Shareable game links id, room_id, code, expires_at, max_uses, use_count

Redis Keys (ioredis)

Pattern Purpose TTL
game:{roomId} Active game state (JSON) 24h
game:{roomId}:players Player connection status 24h
session:{sessionId} User session data 7d

Steps

1. Set up Supabase project and generate types

  • Create Supabase project (or use existing)
  • Define tables via Supabase dashboard or migrations
  • Generate TypeScript types: supabase gen types typescript
  • Add types to packages/shared/src/database.types.ts

2. Create Supabase service in backend

  • Install @supabase/supabase-js
  • Create SupabaseService in apps/backend/src/core/
  • Initialize client with service role key (server-side)
  • Expose typed query methods

3. Create Redis service in backend

  • Create RedisService in apps/backend/src/core/
  • Replace in-memory Map<string, GameState> in GameStateManager
  • Implement key patterns with TTL
  • Add connection pooling and error handling

4. Implement auth integration

  • Add Supabase Auth middleware for tRPC context
  • Extract user from JWT in request headers
  • Create profiles table for extended user data
  • Wire auth to game.join to track participation

5. Implement invite link system

  • Create invite_links table
  • Add game.createInvite / game.joinByCode procedures
  • Generate short codes (e.g., 6 chars alphanumeric)
  • Support expiration and usage limits

6. Implement game history archival

  • On GAME_OVER event:
    1. Read final state from Redis
    2. Insert into game_history table
    3. Delete Redis key
  • Link to user IDs for match history queries

7. Implement content repository abstraction layer

Create a repository pattern to abstract content storage:

Interface (apps/backend/src/core/content-loader/):

export interface GameContentRepository {
  getTemplate(templateId: string): Promise<GameTemplate | null>;
  getVariant(variantId: string): Promise<GameVariant | null>;
  getStory(storyId: string, locale?: string): Promise<StoryDefinition | null>;
  listVariants(options?: { templateId?: string; tags?: string[]; published?: boolean }): Promise<GameVariant[]>;
}

Implementations:

  • FileSystemContentRepository - Reads from /game-content/ (development)
  • DatabaseContentRepository - Queries Supabase tables (production)

Environment toggle:

CONTENT_SOURCE=filesystem   # dev default
CONTENT_SOURCE=database     # production

Caching layer (sits in front of repository):

Request → Redis Cache (30min TTL) → Repository (FS or DB) → Response
              ↓
    Cache miss: fetch, store, return
    Cache hit: return cached

Content seeding script:

pnpm content:seed   # Migrate JSON files → Supabase tables
pnpm content:export # Export DB content → JSON files (backup)

Migration steps:

  1. Create GameContentRepository interface
  2. Implement FileSystemContentRepository (extract from current ContentLoaderService)
  3. Implement DatabaseContentRepository (query Supabase)
  4. Add CachedContentRepository wrapper (Redis caching)
  5. Wire via NestJS DI based on CONTENT_SOURCE env var
  6. Create content:seed script using Zod validation

Dependencies to Install

# Backend
cd apps/backend
pnpm add @supabase/supabase-js ioredis

# Dev tools
pnpm add -D supabase

Environment Variables

# Supabase
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_KEY=eyJ...  # Server-side only

# Redis
REDIS_URL=redis://localhost:6379
# Or separate:
REDIS_HOST=localhost
REDIS_PORT=6379
REDIS_PASSWORD=

Type Generation Workflow

# Generate types from Supabase schema
npx supabase gen types typescript --project-id YOUR_PROJECT_ID > packages/shared/src/database.types.ts

# Re-run after schema changes

Parallel Development Tracks

These work streams can be developed simultaneously by different developers or in parallel branches:

Track Steps Dependencies Can Start After
A: Infrastructure 1, 2, 3 None Immediately
B: Auth 4 Step 2 (SupabaseService) Track A basics
C: Invite Links 5 Step 2, 4 Track A + B
D: Game History 6 Step 2, 3 Track A
E: Content Repository 7 Step 2, 3 Track A

Parallelization Strategy

Week 1:
├── Dev 1: Steps 1-3 (Supabase setup, Redis service)
└── Dev 2: Frontend work / other features

Week 2 (after Track A):
├── Dev 1: Step 4 (Auth integration)
├── Dev 2: Step 7 (Content repository)
└── Both can work in parallel

Week 3:
├── Dev 1: Step 5 (Invite links - needs auth)
└── Dev 2: Step 6 (Game history archival)

Independent Pieces (No Dependencies)

These can be built anytime:

  • FileSystemContentRepository implementation (refactor existing code)
  • Content seeding script structure (add DB calls later)
  • Redis key pattern utilities
  • Type definitions in packages/shared

Integration Points (Require Coordination)

  • Auth middleware + tRPC context (Step 4)
  • GameStateManager + RedisService swap (Step 3)
  • ContentLoaderService + Repository pattern (Step 7)

Further Considerations

  1. Guest vs registered users?

    • Support both: guests with localStorage ID, accounts for history
    • Guest sessions stored in Redis, convert to account on signup

  2. Row Level Security (RLS)?

    • Enable on all tables
    • Users can only read own profile/history
    • Game content readable by all authenticated users

  3. Realtime subscriptions?

    • Not needed - using Socket.IO for game updates
    • Could use for admin dashboard (content changes)

  4. Database migrations?

    • Use Supabase CLI: supabase db push / supabase migration
    • Or manage via dashboard for simplicity

  5. Local development?

    • Option A: Use Supabase hosted (free tier)
    • Option B: supabase start for local Docker instance