reading-plan-designer

Reading Plan Designer

Design Bible reading plans with proper data structure and daily reading assignments.

CRITICAL: bible_schema Usage

All reading plan tables and RPC functions reside in bible_schema, NOT public.

Supabase Client Queries

// WRONG - looks in public schema, will fail
const { data } = await supabase.rpc("get_user_reading_plans", { ... });

// CORRECT - explicitly specify bible_schema
const { data } = await (supabase as any)
  .schema("bible_schema")
  .rpc("get_user_reading_plans", { ... });

// WRONG - table query without schema
const { data } = await supabase.from("reading_plans").select("*");

// CORRECT - with schema prefix
const { data } = await (supabase as any)
  .schema("bible_schema")
  .from("reading_plans")
  .select("*");

SQL Migrations

Always prefix table names with bible_schema. in migrations:

-- WRONG
INSERT INTO reading_plans ...

-- CORRECT
INSERT INTO bible_schema.reading_plans ...

Context Files (Read First)

• Docs/context/db-schema-short.md - Database tables overview
• Docs/context/supabase-map.md - RPC functions for reading plans

Quick Start: Generate a Plan

Use the bundled script to auto-generate plans with SQL migrations:

# Single book plan
python scripts/fetch_reading_plan.py --generate --book John --days 21 --sql -o john.json

# New Testament in 90 days
python scripts/fetch_reading_plan.py --generate --sections NT --days 90 --sql -o nt90.json

# Full Bible in 365 days
python scripts/fetch_reading_plan.py --generate --sections OT+NT --days 365 --sql -o yearly.json

# Psalms in 30 days
python scripts/fetch_reading_plan.py --generate --book Psalms --days 30 --sql -o psalms30.json

The script outputs:

• JSON file with plan structure
• SQL migration file (with --sql flag)
• Summary with day count and reading blocks

Database Schema

reading_plans (bible_schema)

id UUID PRIMARY KEY
slug TEXT UNIQUE           -- URL-friendly identifier
name_fi TEXT NOT NULL      -- Finnish name
name_en TEXT               -- English name (optional)
description_fi TEXT        -- Finnish description
duration_days INTEGER      -- Total days in plan
is_active BOOLEAN          -- Show to users
sort_order INTEGER         -- Display order

reading_plan_days (bible_schema)

id UUID PRIMARY KEY
plan_id UUID REFERENCES reading_plans(id)
day_number INTEGER         -- Day 1, 2, 3...
title TEXT                 -- Optional day title
readings JSONB             -- Array of ReadingReference

ReadingReference Format (JSONB)

interface ReadingReference {
  book: string;           // Finnish abbreviation: "Matt", "1. Moos", "Ps"
  chapter_start: number;  // Starting chapter
  chapter_end?: number;   // For chapter ranges (optional)
  verse_start?: number;   // Starting verse (optional, full chapter if omitted)
  verse_end?: number;     // Ending verse (optional)
}

Manual Plan Creation

Step 1: Create Plan

INSERT INTO bible_schema.reading_plans (
  id, slug, name_fi, name_en, description_fi, duration_days, is_active, sort_order
) VALUES (
  gen_random_uuid(),
  'gospel-of-john',
  'Johanneksen evankeliumi',
  'Gospel of John',
  'Lue Johanneksen evankeliumi 21 päivässä',
  21,
  true,
  10
);

Step 2: Add Daily Readings

WITH plan AS (
  SELECT id FROM bible_schema.reading_plans WHERE slug = 'gospel-of-john'
)
INSERT INTO bible_schema.reading_plan_days (id, plan_id, day_number, title, readings)
SELECT gen_random_uuid(), plan.id, day_num, title, readings::jsonb
FROM plan, (VALUES
  (1, 'Sana tuli lihaksi', '[{"book": "Joh", "chapter_start": 1}]'),
  (2, 'Kaanan häät', '[{"book": "Joh", "chapter_start": 2}]'),
  (3, 'Nikodemos', '[{"book": "Joh", "chapter_start": 3}]')
  -- Continue for all days...
) AS days(day_num, title, readings);

Reading Reference Examples

Book Abbreviations

Common Finnish abbreviations:

Query all: SELECT abbreviation_fi, name_fi FROM bible_schema.books

Reading Plan Patterns

Frontend Integration

Pages & Components

Key Features (Jan 2026)

• Verses shown by default - No need to click "Näytä teksti"
• Top navigation bar - [<] [Merkitse luetuksi] [>] for easy day navigation
• Verse badges - Click to open day view (stays in reading plan context)
• Quit button - X button on active plans
• Activity tracking - Events logged to user_activity_log

Hooks

Utility Files

RPC Functions

External Resources

For inspiration on reading plan structures:

• Bible Study Tools - Various plan types
• YouVersion - Popular reading plans
• Blue Letter Bible - Chronological plans

Validation Checklist

• Book abbreviations valid (exist in bible_schema.books)
• Day numbers sequential (1, 2, 3...)
• duration_days matches actual day count
• readings JSONB is valid array format
• Finnish name and description provided

Related Skills