Admin Portal + Supabase Content Catalog — Implementation Plan
Status: Draft (2026-06-08) Scope: v0.2 — introduce Next.js admin portal, migrate content catalog to Supabase, add chapters + i18n + pathway metadata, formalize SDLC across local / staging / production.
This document is the single source of truth for the implementation. Each phase lists the file changes, SQL, and acceptance criteria so any team member (or future agent session) can execute it.
Table of contents
- Goals & non-goals
- System architecture
- Content data model
- Auth & RLS design
- Admin portal architecture
- Mobile app changes
- SDLC & team workflow
- Implementation phases
- Open questions / future work
1. Goals & non-goals
Goals
- Admin portal (Next.js) for the team to create / edit / publish content (modules, chapters, cards, pathways, featured items).
- Supabase becomes the single source of truth for content (modules / chapters / cards / pathways).
- Three-level content hierarchy: Module → Chapter → Card.
- Multilingual content (zh-TW required, en optional) via dedicated translation tables.
- Pathway metadata: estimated days, prerequisites, difficulty, subject tags.
- RLS-based RBAC: anon (mobile read), reviewer (translations), editor (content CRUD), admin (everything + user mgmt).
- Three environments: local (Docker Supabase), staging (cloud), production (cloud).
- Cross-platform team workflow (macOS + Windows) via Supabase CLI + npm scripts.
- SDLC: feature/* → staging → main, auto-deploy on push, EAS APK for device testing.
Non-goals (deferred)
- Content packs for offline use — new content is online-only for now.
official-v1.jsonstays as offline fallback seed for the legacy modules. - RevenueCat integration — mock subscription stays.
- Mobile auth / user accounts — anon read only for v0.2. Admin portal users are separate.
- Push notifications, PostHog analytics, Sentry — stay in
TODO.md. - Multimedia in cards (images / audio) — text only for v0.2.
2. System architecture
┌──────────────────────────────────────────────────────────┐
│ Admin Portal (Next.js 16.x, apps/admin/) │
│ Pages: Dashboard, Modules, Chapters, Cards, Pathways, │
│ Featured, Analytics, System │
│ Auth: Supabase Auth (magic link) + RLS roles │
│ Hosting: Vercel (preview on staging, prod on main) │
└──────────────────┬───────────────────────────────────────┘
│ writes (user JWT — RLS enforced)
▼
┌──────────────────────────────────────────────────────────┐
│ Supabase Postgres (local / staging / production) │
│ │
│ Content tables Translation tables │
│ ├── modules ├── module_translations │
│ ├── chapters ├── chapter_translations │
│ ├── cards ├── card_translations │
│ ├── pathways └── pathway_translations │
│ │
│ Existing Audit │
│ ├── featured_items └── content_revisions │
│ ├── featured_events │
│ └── featured_scores_global │
│ │
│ Edge Functions: featured-feed │
└──────────────────┬───────────────────────────────────────┘
│ reads (anon key, RLS: is_active=true)
▼
┌──────────────────────────────────────────────────────────┐
│ Mobile App (Expo, apps/mobile/) │
│ - Online: pull content from Supabase by locale │
│ - Cache: SQLite mirror for offline reads │
│ - Fallback seed: content/packs/official-v1.json │
│ - Manual language selection (zh-TW / en) in Users tab │
└──────────────────────────────────────────────────────────┘
Environment matrix
| Component | Local | Staging | Production |
|---|---|---|---|
| Supabase | supabase start (Docker, :54321) |
umikora-staging.supabase.co |
umikora-production.supabase.co |
| Admin portal | localhost:3000 |
Vercel preview (auto on staging) |
Vercel production (auto on main) |
| Mobile (Expo Go) | Points to local Supabase | Points to staging | Points to production |
| Mobile (EAS) | — | com.umikora.app.staging APK |
com.umikora.app APK |
3. Content data model
3.1 Hierarchy
Module (學科 / 書 / 考試)
└── Chapter (篇章 / 單元)
└── Card (閃卡: MCQ / Cloze)
Pathway (學習路徑)
└── module_slugs[] (ordered list of module slugs)
3.2 i18n strategy
- Base tables hold non-translatable fields only (slugs, category, tier, difficulty, sort_order, flags, FKs).
- Translation tables hold all human-readable text, one row per
(entity, locale). - Supported locales:
zh-TW(required),en(optional). Adding a locale = update CHECK constraint + UI. - App layer enforces “zh-TW must exist” on create (Supabase RLS does not enforce this — keep at app layer for usability).
3.3 Schema (full SQL)
File: supabase/migrations/20260608000001_content_catalog.sql
-- ============================================================
-- CONTENT CATALOG — modules, chapters, cards, pathways
-- ============================================================
CREATE TABLE modules (
slug TEXT PRIMARY KEY,
category TEXT NOT NULL CHECK (category IN ('book','subject','exam')),
subject_tags TEXT[] NOT NULL DEFAULT '{}',
difficulty TEXT NOT NULL CHECK (difficulty IN ('beginner','intermediate','advanced')),
estimated_minutes INT NOT NULL DEFAULT 30,
tier TEXT NOT NULL CHECK (tier IN ('free','pro')),
icon TEXT NOT NULL DEFAULT 'book',
sort_order INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE chapters (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
module_slug TEXT NOT NULL REFERENCES modules(slug) ON DELETE CASCADE,
slug TEXT NOT NULL,
sort_order INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (module_slug, slug)
);
CREATE TABLE cards (
id TEXT PRIMARY KEY,
chapter_id UUID NOT NULL REFERENCES chapters(id) ON DELETE CASCADE,
module_slug TEXT NOT NULL REFERENCES modules(slug) ON DELETE CASCADE,
type TEXT NOT NULL CHECK (type IN ('mcq','cloze')),
front_json JSONB NOT NULL,
back_json JSONB NOT NULL,
explanation TEXT NOT NULL DEFAULT '',
source_ref TEXT NOT NULL DEFAULT '',
tags TEXT[] NOT NULL DEFAULT '{}',
ordinal INT,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE pathways (
slug TEXT PRIMARY KEY,
tier TEXT NOT NULL CHECK (tier IN ('free','pro')),
module_slugs TEXT[] NOT NULL,
difficulty TEXT NOT NULL CHECK (difficulty IN ('beginner','intermediate','advanced')),
estimated_days INT,
prerequisite_slugs TEXT[] NOT NULL DEFAULT '{}',
subject_tags TEXT[] NOT NULL DEFAULT '{}',
sort_order INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- ============================================================
-- TRANSLATIONS — one row per (entity, locale)
-- ============================================================
CREATE TABLE module_translations (
module_slug TEXT NOT NULL REFERENCES modules(slug) ON DELETE CASCADE,
locale TEXT NOT NULL CHECK (locale IN ('zh-TW','en')),
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
PRIMARY KEY (module_slug, locale)
);
CREATE TABLE chapter_translations (
chapter_id UUID NOT NULL REFERENCES chapters(id) ON DELETE CASCADE,
locale TEXT NOT NULL CHECK (locale IN ('zh-TW','en')),
title TEXT NOT NULL,
PRIMARY KEY (chapter_id, locale)
);
CREATE TABLE card_translations (
card_id TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
locale TEXT NOT NULL CHECK (locale IN ('zh-TW','en')),
front_json JSONB NOT NULL,
back_json JSONB NOT NULL,
explanation TEXT NOT NULL DEFAULT '',
PRIMARY KEY (card_id, locale)
);
CREATE TABLE pathway_translations (
pathway_slug TEXT NOT NULL REFERENCES pathways(slug) ON DELETE CASCADE,
locale TEXT NOT NULL CHECK (locale IN ('zh-TW','en')),
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
PRIMARY KEY (pathway_slug, locale)
);
-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_chapters_module ON chapters (module_slug, sort_order);
CREATE INDEX idx_cards_chapter ON cards (chapter_id, ordinal);
CREATE INDEX idx_cards_module ON cards (module_slug);
CREATE INDEX idx_modules_active ON modules (is_active, sort_order);
CREATE INDEX idx_pathways_active ON pathways (is_active, sort_order);
CREATE INDEX idx_module_translations_loc ON module_translations (locale);
CREATE INDEX idx_chapter_translations_loc ON chapter_translations (locale);
CREATE INDEX idx_card_translations_loc ON card_translations (locale);
CREATE INDEX idx_pathway_translations_loc ON pathway_translations (locale);
-- ============================================================
-- AUDIT TRAIL
-- ============================================================
CREATE TABLE content_revisions (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
record_id TEXT NOT NULL,
action TEXT NOT NULL CHECK (action IN ('insert','update','delete')),
old_data JSONB,
new_data JSONB,
changed_by UUID REFERENCES auth.users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_content_revisions_table_record
ON content_revisions (table_name, record_id, changed_at DESC);
3.4 Pre-existing tables (unchanged)
| Table | Source migration |
|---|---|
featured_items, featured_events, featured_scores_global |
20260531000001_featured_schema.sql |
These keep their existing RLS policies. New admin role policies are layered in via the new migration.
4. Auth & RLS design
4.1 Role model
| Role | Source of claim | Capabilities |
|---|---|---|
anon |
No JWT (mobile app uses anon key) | SELECT on is_active=true content + all translations |
reviewer |
auth.users.app_metadata.role = 'reviewer' |
+ INSERT/UPDATE on translation tables |
editor |
auth.users.app_metadata.role = 'editor' |
+ Full CRUD on content tables + translations |
admin |
auth.users.app_metadata.role = 'admin' |
+ user management (set roles for others) + read audit trail |
Roles live in auth.users.app_metadata (not user_metadata) so users cannot modify their own role. An admin sets roles via Supabase Studio or a custom admin RPC.
4.2 RLS helper
CREATE OR REPLACE FUNCTION auth_role() RETURNS TEXT AS $$
SELECT COALESCE(
(auth.jwt() -> 'app_metadata' ->> 'role'),
'anon'
);
$$ LANGUAGE SQL STABLE;
4.3 RLS policies (full SQL, appended to migration 20260608000001)
ALTER TABLE modules ENABLE ROW LEVEL SECURITY;
ALTER TABLE chapters ENABLE ROW LEVEL SECURITY;
ALTER TABLE cards ENABLE ROW LEVEL SECURITY;
ALTER TABLE pathways ENABLE ROW LEVEL SECURITY;
ALTER TABLE module_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE chapter_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE card_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE pathway_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE content_revisions ENABLE ROW LEVEL SECURITY;
-- Public read (mobile + admin browse)
CREATE POLICY modules_read ON modules FOR SELECT USING (is_active = true OR auth_role() IN ('editor','admin','reviewer'));
CREATE POLICY chapters_read ON chapters FOR SELECT USING (is_active = true OR auth_role() IN ('editor','admin','reviewer'));
CREATE POLICY cards_read ON cards FOR SELECT USING (is_active = true OR auth_role() IN ('editor','admin','reviewer'));
CREATE POLICY pathways_read ON pathways FOR SELECT USING (is_active = true OR auth_role() IN ('editor','admin','reviewer'));
CREATE POLICY module_translations_read ON module_translations FOR SELECT USING (true);
CREATE POLICY chapter_translations_read ON chapter_translations FOR SELECT USING (true);
CREATE POLICY card_translations_read ON card_translations FOR SELECT USING (true);
CREATE POLICY pathway_translations_read ON pathway_translations FOR SELECT USING (true);
-- Editor + admin: full CRUD on content
CREATE POLICY modules_write ON modules FOR ALL USING (auth_role() IN ('editor','admin')) WITH CHECK (auth_role() IN ('editor','admin'));
CREATE POLICY chapters_write ON chapters FOR ALL USING (auth_role() IN ('editor','admin')) WITH CHECK (auth_role() IN ('editor','admin'));
CREATE POLICY cards_write ON cards FOR ALL USING (auth_role() IN ('editor','admin')) WITH CHECK (auth_role() IN ('editor','admin'));
CREATE POLICY pathways_write ON pathways FOR ALL USING (auth_role() IN ('editor','admin')) WITH CHECK (auth_role() IN ('editor','admin'));
-- Reviewer + editor + admin: translations
CREATE POLICY module_translations_write ON module_translations FOR ALL USING (auth_role() IN ('reviewer','editor','admin')) WITH CHECK (auth_role() IN ('reviewer','editor','admin'));
CREATE POLICY chapter_translations_write ON chapter_translations FOR ALL USING (auth_role() IN ('reviewer','editor','admin')) WITH CHECK (auth_role() IN ('reviewer','editor','admin'));
CREATE POLICY card_translations_write ON card_translations FOR ALL USING (auth_role() IN ('reviewer','editor','admin')) WITH CHECK (auth_role() IN ('reviewer','editor','admin'));
CREATE POLICY pathway_translations_write ON pathway_translations FOR ALL USING (auth_role() IN ('reviewer','editor','admin')) WITH CHECK (auth_role() IN ('reviewer','editor','admin'));
-- Audit: admin + reviewer can read; trigger writes (SECURITY DEFINER) bypass policy
CREATE POLICY content_revisions_read ON content_revisions FOR SELECT USING (auth_role() IN ('admin','reviewer'));
4.4 Audit trigger
CREATE OR REPLACE FUNCTION audit_content_change() RETURNS TRIGGER AS $$
DECLARE
rec_id TEXT;
BEGIN
IF TG_OP = 'DELETE' THEN
rec_id := COALESCE(OLD::jsonb->>'slug', OLD::jsonb->>'id');
ELSE
rec_id := COALESCE(NEW::jsonb->>'slug', NEW::jsonb->>'id');
END IF;
INSERT INTO content_revisions (table_name, record_id, action, old_data, new_data, changed_by)
VALUES (
TG_TABLE_NAME,
rec_id,
lower(TG_OP),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END,
auth.uid()
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER modules_audit AFTER INSERT OR UPDATE OR DELETE ON modules FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER chapters_audit AFTER INSERT OR UPDATE OR DELETE ON chapters FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER cards_audit AFTER INSERT OR UPDATE OR DELETE ON cards FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER pathways_audit AFTER INSERT OR UPDATE OR DELETE ON pathways FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER module_translations_audit AFTER INSERT OR UPDATE OR DELETE ON module_translations FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER chapter_translations_audit AFTER INSERT OR UPDATE OR DELETE ON chapter_translations FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER card_translations_audit AFTER INSERT OR UPDATE OR DELETE ON card_translations FOR EACH ROW EXECUTE FUNCTION audit_content_change();
CREATE TRIGGER pathway_translations_audit AFTER INSERT OR UPDATE OR DELETE ON pathway_translations FOR EACH ROW EXECUTE FUNCTION audit_content_change();
4.5 How the admin portal uses RLS
Critical rule: admin portal uses the user’s session JWT, never service_role. This way RLS enforces the same rules end-to-end.
// apps/admin/src/lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
export async function createSupabaseServerClient() {
const cookieStore = await cookies();
return createServerClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
{
cookies: {
getAll: () => cookieStore.getAll(),
setAll: (cookiesToSet) => {
cookiesToSet.forEach(({ name, value, options }) =>
cookieStore.set(name, value, options)
);
},
},
},
);
}
All Server Actions receive the user’s JWT via cookies, so every query is RLS-checked.
service_role key is only used by Edge Functions (e.g. featured-feed) where the function logic itself authorises the request.
5. Admin portal architecture
5.1 Tech stack
| Layer | Choice |
|---|---|
| Framework | Next.js 16.x (App Router, Server Actions) |
| UI library | shadcn/ui + Tailwind CSS |
| Tables | @tanstack/react-table (shadcn DataTable preset) |
| Charts | recharts (shadcn Chart preset) |
| Forms | react-hook-form + zod |
| Drag-and-drop | @dnd-kit/sortable (chapter reorder) |
| Supabase client | @supabase/supabase-js + @supabase/ssr |
| Auth | Supabase Auth — magic link |
| Icons | lucide-react |
| Hosting | Vercel — root apps/admin/ |
5.2 Directory layout
apps/admin/
├── package.json
├── next.config.ts
├── tailwind.config.ts
├── tsconfig.json
├── postcss.config.mjs
├── components.json # shadcn config
├── middleware.ts # auth guard
├── .env.local.example
├── .env.local # written by scripts/setup-local.mjs (gitignored)
├── README.md
└── src/
├── app/
│ ├── layout.tsx # root: providers, theme, sidebar
│ ├── globals.css
│ ├── page.tsx # dashboard
│ ├── login/
│ │ └── page.tsx
│ ├── modules/
│ │ ├── page.tsx # list modules
│ │ ├── new/page.tsx
│ │ └── [slug]/
│ │ ├── page.tsx # edit module metadata + translations
│ │ └── chapters/
│ │ ├── page.tsx # list & reorder chapters
│ │ └── [chapterId]/
│ │ ├── page.tsx # edit chapter translations
│ │ └── cards/
│ │ ├── page.tsx # list cards in chapter
│ │ ├── new/page.tsx
│ │ └── [cardId]/page.tsx
│ ├── pathways/
│ │ ├── page.tsx
│ │ ├── new/page.tsx
│ │ └── [slug]/page.tsx
│ ├── featured/
│ │ ├── page.tsx
│ │ └── [id]/page.tsx
│ ├── analytics/
│ │ └── page.tsx
│ ├── system/
│ │ └── page.tsx
│ └── users/ # admin-only: set roles for team members
│ └── page.tsx
├── actions/ # Server Actions (mutations only)
│ ├── modules.ts
│ ├── chapters.ts
│ ├── cards.ts
│ ├── pathways.ts
│ ├── featured.ts
│ ├── users.ts # set role, invite (admin)
│ └── analytics.ts # aggregate queries
├── lib/
│ ├── supabase/
│ │ ├── server.ts
│ │ ├── browser.ts
│ │ └── middleware.ts # ssr middleware helper
│ ├── auth-guard.ts # requireRole(['editor','admin'])
│ ├── locales.ts # SUPPORTED_LOCALES, DEFAULT_LOCALE
│ └── validation.ts # zod schemas for forms
├── components/
│ ├── ui/ # shadcn primitives
│ ├── layout/
│ │ ├── Sidebar.tsx
│ │ ├── Topbar.tsx
│ │ └── PageHeader.tsx
│ ├── modules/
│ │ ├── ModuleForm.tsx
│ │ └── ModuleTable.tsx
│ ├── chapters/
│ │ ├── ChapterList.tsx # sortable
│ │ └── ChapterForm.tsx
│ ├── cards/
│ │ ├── McqEditor.tsx
│ │ ├── ClozeEditor.tsx
│ │ ├── CardPreview.tsx # mimics mobile rendering
│ │ └── CardTable.tsx
│ ├── pathways/
│ │ ├── PathwayForm.tsx
│ │ └── PathwayModulePicker.tsx
│ ├── translations/
│ │ ├── TranslationTabs.tsx # zh-TW* | en
│ │ └── LocaleBadge.tsx # "未翻譯" / "已翻譯"
│ ├── dashboard/
│ │ ├── StatCard.tsx
│ │ ├── RecentActivity.tsx
│ │ └── HealthPanel.tsx
│ └── analytics/
│ ├── ReviewTrendChart.tsx
│ ├── ModuleCompletionTable.tsx
│ └── TranslationCoverage.tsx
└── types/
├── content.ts # Module, Chapter, Card, Pathway view models
└── analytics.ts
5.3 Auth flow
- User opens admin portal →
middleware.tschecks Supabase session cookie. - No session → redirect to
/login. /loginrequests magic link viasupabase.auth.signInWithOtp({ email }).- User clicks link → callback sets cookies → redirected to
/. middleware.tschecksapp_metadata.role ∈ ['reviewer','editor','admin']→ otherwise show “Access denied” page.- Each Server Action calls
requireRole(['editor','admin'])for write paths.
5.4 Page-by-page features
/ — Dashboard
- Stat cards: # modules / # chapters / # cards / # pathways / # featured items / # reviews this week.
- Recent activity feed (last 20
content_revisions). - Translation coverage bar:
zh-TW: 100%/en: X%. - Supabase health panel (DB up, Edge Function latency).
/modules
- DataTable with columns: title (zh-TW), category, difficulty, tier, chapter count, card count, is_active, updated_at.
- Filter by category / tier / is_active. Search by slug / title.
- “New module” button →
/modules/new.
/modules/new and /modules/[slug]
- Form fields: slug (immutable on edit), category, difficulty, tier, icon, estimated_minutes, sort_order, subject_tags.
- TranslationTabs: zh-TW* (required at create), en (optional).
- Tabs: “Metadata” / “Chapters” / “Revisions”.
/modules/[slug]/chapters
- Sortable list (drag handle) of chapters with slug + zh-TW title.
- Inline add chapter (slug + zh-TW title).
- Click chapter row →
/modules/[slug]/chapters/[chapterId].
/modules/[slug]/chapters/[chapterId]
- Edit chapter slug + translations.
- DataTable of cards in this chapter (id, type, preview of front).
- “New card” →
/modules/[slug]/chapters/[chapterId]/cards/new.
/modules/[slug]/chapters/[chapterId]/cards/[cardId]
- Type picker: MCQ / Cloze (immutable on edit).
- McqEditor: question + 4 choices + correct index.
- ClozeEditor: text (with
___blanks) + answer. - Explanation, source_ref, tags.
- TranslationTabs for translatable fields.
- CardPreview panel on the right (live preview matching mobile rendering).
/pathways
- List with title, difficulty, tier, # modules, estimated_days, is_active.
/pathways/new and /pathways/[slug]
- Form: slug, tier, difficulty, estimated_days, prerequisite_slugs (multi-select), subject_tags.
- PathwayModulePicker: drag-and-drop ordered list of modules.
- TranslationTabs.
/featured
- CRUD for
featured_items(existing table).
/analytics
- Cards: lowest-correctness cards, modules with lowest completion rate.
- Line chart: daily reviews trend (requires future review_events from Supabase; placeholder for now).
- Translation coverage breakdown per locale.
/system
- Supabase health (
SELECT 1, edge function ping). - Migration status (
supabase migration listequivalent — read fromsupabase_migrations.schema_migrations). - Recent content revisions feed.
/users (admin only)
- List
auth.userswith current role. - Invite by email (Supabase Auth admin API via service_role — wrapped in an admin Edge Function so the key never leaves the server).
- Change role (calls Edge Function
set-user-role).
5.5 TranslationTabs UX contract
┌────────────────────────────────────────────────┐
│ [zh-TW *] [en] [+ Add locale] │ ← tabs
│────────────────────────────────────────────────│
│ Title* [____________________________] │
│ Description [____________________________] │
│ │
│ * required for zh-TW; en may be left blank. │
└────────────────────────────────────────────────┘
- Switching tabs preserves unsaved input via local state.
- Save button writes all dirty locales in one Server Action (uses
upsert). LocaleBadgeshows “未翻譯” on the tab when that locale has no row.
6. Mobile app changes
6.1 @umikora/shared
packages/shared/src/types.ts:
export type SupportedLocale = 'zh-TW' | 'en';
export const SUPPORTED_LOCALES: SupportedLocale[] = ['zh-TW', 'en'];
export const DEFAULT_LOCALE: SupportedLocale = 'zh-TW';
export interface ContentChapter {
id: string;
module_slug: string;
slug: string;
sort_order: number;
}
// ContentModule loses inline title/description — comes from translation table
export interface ContentModule {
slug: string;
category: ModuleCategory;
subject_tags: string[];
difficulty: 'beginner' | 'intermediate' | 'advanced';
estimated_minutes: number;
tier: ContentTier;
icon: string;
chapters: ContentChapter[];
cards: ContentCard[];
// Resolved at read time:
title: string;
description: string;
}
export interface ContentCard {
id: string;
chapter_id: string;
module_slug: string;
type: CardType;
front: McqFront | ClozeFront;
back: McqBack | ClozeBack;
explanation: string;
source_ref: string;
tags: string[];
}
export interface ContentPathway {
slug: string;
tier: ContentTier;
module_slugs: string[];
difficulty: 'beginner' | 'intermediate' | 'advanced';
estimated_days: number | null;
prerequisite_slugs: string[];
subject_tags: string[];
title: string; // resolved
description: string; // resolved
}
// Translation row shapes (for sync layer)
export interface ModuleTranslationRow { module_slug: string; locale: SupportedLocale; title: string; description: string; }
export interface ChapterTranslationRow { chapter_id: string; locale: SupportedLocale; title: string; }
export interface CardTranslationRow { card_id: string; locale: SupportedLocale; front_json: string; back_json: string; explanation: string; }
export interface PathwayTranslationRow { pathway_slug: string; locale: SupportedLocale; title: string; description: string; }
packages/shared/src/constants.ts:
SCHEMA_VERSION→5- Add
SUPPORTED_LOCALES,DEFAULT_LOCALE.
6.2 SQLite migration (SCHEMA_VERSION 5)
In apps/mobile/src/data/database.ts:
-- v5: chapters + translations + pathway metadata + card chapter_id
CREATE TABLE IF NOT EXISTS chapters (
id TEXT PRIMARY KEY NOT NULL,
module_slug TEXT NOT NULL,
slug TEXT NOT NULL,
sort_order INTEGER NOT NULL DEFAULT 0,
is_active INTEGER NOT NULL DEFAULT 1
);
ALTER TABLE cards ADD COLUMN chapter_id TEXT;
ALTER TABLE pathways ADD COLUMN difficulty TEXT NOT NULL DEFAULT 'beginner';
ALTER TABLE pathways ADD COLUMN estimated_days INTEGER;
ALTER TABLE pathways ADD COLUMN prerequisite_slugs TEXT NOT NULL DEFAULT '[]';
ALTER TABLE pathways ADD COLUMN subject_tags TEXT NOT NULL DEFAULT '[]';
CREATE TABLE IF NOT EXISTS module_translations (
module_slug TEXT NOT NULL,
locale TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
PRIMARY KEY (module_slug, locale)
);
CREATE TABLE IF NOT EXISTS chapter_translations (
chapter_id TEXT NOT NULL,
locale TEXT NOT NULL,
title TEXT NOT NULL,
PRIMARY KEY (chapter_id, locale)
);
CREATE TABLE IF NOT EXISTS card_translations (
card_id TEXT NOT NULL,
locale TEXT NOT NULL,
front_json TEXT NOT NULL,
back_json TEXT NOT NULL,
explanation TEXT NOT NULL DEFAULT '',
PRIMARY KEY (card_id, locale)
);
CREATE TABLE IF NOT EXISTS pathway_translations (
pathway_slug TEXT NOT NULL,
locale TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
PRIMARY KEY (pathway_slug, locale)
);
ALTER TABLE user_profile ADD COLUMN preferred_locale TEXT NOT NULL DEFAULT 'zh-TW';
6.3 Repository changes
apps/mobile/src/data/localRepository.ts:
- All read methods (
getModules,getModule,getCard,getPathway, etc.) accept alocalearg or read it fromuser_profile.preferred_locale. - JOIN translation tables; fallback to
DEFAULT_LOCALEif the requested locale row is missing. - New methods on
ContentRepository:getChapters(moduleSlug)getCardsByChapter(chapterId)syncFromSupabase(locale)
- Existing card list ordering: now ordered by
chapters.sort_orderthencards.ordinal.
apps/mobile/src/data/supabaseContentClient.ts (new):
- Wraps
@supabase/supabase-jscalls. pullModules(),pullChapters(),pullCards(),pullPathways(),pullTranslations().- Uses
EXPO_PUBLIC_SUPABASE_URL+EXPO_PUBLIC_SUPABASE_ANON_KEY.
6.4 AppContext / startup
apps/mobile/src/context/AppContext.tsx:
initializeDatabase()runs migrations (legacyofficial-v1.jsonseed still happens if tables empty — offline fallback).- After init, check network. If online →
syncFromSupabase(preferredLocale). - Sync upserts rows into SQLite mirror.
- Subsequent reads come from SQLite (sync is best-effort).
6.5 Language selection UI
apps/mobile/app/(tabs)/users.tsx:
- New section “語言 / Language” with
zh-TW/Englishsegmented control. - Selecting a locale calls
repositories.setPreferredLocale(locale)→refresh().
apps/mobile/src/i18n/:
- New
en.tsfile (UI strings — minimal for now; reuse zh-TW as fallback when keys missing). useTranslation()hook readspreferredLocalefromAppContext.
6.6 Environment variables
apps/mobile/.env.example (extend existing):
EXPO_PUBLIC_APP_ENV=local|staging|production
EXPO_PUBLIC_SUPABASE_URL=...
EXPO_PUBLIC_SUPABASE_ANON_KEY=...
Local Supabase URL on a physical device must be the host machine’s LAN IP, not localhost — scripts/setup-local.mjs detects this and prints both URLs.
7. SDLC & team workflow
7.1 Branches
main (production)
│
└── staging (auto-deploy to staging environments)
│
├── feature/admin-content-crud
├── feature/chapters-model
├── feature/i18n-mobile
└── fix/review-crash
feature/*branches offstaging.- PR target =
staging. Squash-merge. - Promote staging → main via PR
staging → main. No squash (preserve history). - Hotfixes: branch from
main, PR back tomain, then cherry-pick / merge intostaging.
7.2 Per-developer flow
1. git checkout staging && git pull
2. git checkout -b feature/<topic>
3. npm run supabase:start # local Docker Supabase
4. npm run setup:local # write .env.local files
5. cd apps/admin && npm run dev # admin portal at localhost:3000
6. cd apps/mobile && npx expo start # Expo Go pointing to local Supabase
7. npm test # shared unit tests
8. cd apps/admin && npm run typecheck && npm run lint
9. git push → PR to staging
7.3 CI workflows
| Workflow | Trigger | Action |
|---|---|---|
test.yml |
PR to staging/main; push to either | lint + typecheck + vitest (shared, admin) |
deploy-supabase-staging.yml |
push staging |
supabase db push + functions deploy against staging |
deploy-supabase-production.yml |
push main (+ manual) |
same against production |
| Vercel (auto) | push staging / main |
Build & deploy apps/admin |
| EAS staging build | manual / weekly | eas build --profile staging --platform android |
| EAS production build | manual on release | eas build --profile production --platform android |
7.4 Release checklist (staging → main)
- All PRs merged into
staging. - EAS staging APK installed on at least one device. Smoke test: review session, admin module create, mobile sees new module.
- Supabase staging audit log shows expected revisions.
staging → mainPR opened, CI green.- Merge → production Supabase migration deploys; Vercel deploys admin prod; mobile prod APK built via EAS.
- Tag release:
git tag v0.2.0 && git push --tags.
7.5 Environment variables — where they live
| Var | Local | Staging | Production |
|---|---|---|---|
NEXT_PUBLIC_SUPABASE_URL (admin) |
apps/admin/.env.local (auto-written) |
Vercel project env (staging) | Vercel project env (prod) |
NEXT_PUBLIC_SUPABASE_ANON_KEY |
same | same | same |
EXPO_PUBLIC_SUPABASE_URL (mobile) |
apps/mobile/.env.local |
EAS env (staging profile) | EAS env (production profile) |
EXPO_PUBLIC_SUPABASE_ANON_KEY |
same | same | same |
SUPABASE_ACCESS_TOKEN |
dev only, never committed | GitHub repo secret | GitHub repo secret |
SUPABASE_*_PROJECT_REF |
n/a | GitHub repo secret | GitHub repo secret |
service_role keys live only in Supabase project settings (used by Edge Functions). They are not stored in GitHub or .env files.
8. Implementation phases
Phase 0 — Local infrastructure (1 day)
Files (new):
scripts/setup-local.mjsdocs/SETUP.md(rewrite for current scope)
Files (modified):
package.json(root) — add scripts:{ "scripts": { "supabase:start": "supabase start", "supabase:stop": "supabase stop", "supabase:reset": "supabase db reset", "supabase:status": "supabase status", "setup:local": "node scripts/setup-local.mjs" } }.gitignore— ensureapps/admin/.env.localignored.
scripts/setup-local.mjs behaviour:
- Run
supabase status --output json(start if not running). - Parse
API URL,anon key,service_role key,DB URL. - Detect host LAN IP for mobile.
- Write
apps/admin/.env.local(anon + URL) andapps/mobile/.env.local(anon + URL, LAN IP variant). - Print Studio URL.
Acceptance:
- Fresh clone +
npm install && npm run supabase:start && npm run setup:local→ both.env.localfiles exist. apps/mobileboots in Expo Go against local Supabase from a phone on same Wi-Fi.
Phase 1 — Supabase content schema (1–2 days)
Files (new):
supabase/migrations/20260608000001_content_catalog.sql(sections 3.3 + 4.3 + 4.4 above)
Acceptance:
supabase db resetsucceeds locally.supabase db pushsucceeds against staging.- Studio shows all tables with RLS enabled.
Phase 2 — Seed migration (0.5 day)
Files (new):
supabase/migrations/20260608000002_content_seed.sql
Content of seed:
- For each module in
content/packs/official-v1.json:- Insert into
modules(slug, category, difficulty, tier, icon, estimated_minutes, subject_tags). - Insert one
defaultchapter (slug='main', sort_order=0). - Insert each card into
cards(chapter_id = the default chapter UUID). - Insert
module_translationsforzh-TW(use existing title/description as zh-TW source; English content is acceptable for zh-TW placeholder — the team will translate later). - Insert
card_translationsforzh-TW.
- Insert into
- For each pathway: insert into
pathways(default difficulty=‘beginner’, estimated_days=null, prerequisites=’{}’) +pathway_translationszh-TW.
(The seed migration is a Node script that emits SQL during Phase 0 setup, or hand-written SQL — choose whichever is easier; see Phase 2 notes in docs/SETUP.md.)
Acceptance:
- After reset,
SELECT count(*) FROM modulesmatches the JSON pack. SELECT count(*) FROM card_translations WHERE locale='zh-TW'matchescardscount.
Phase 3 — Admin portal scaffold (1 day)
Commands:
cd apps && npx create-next-app@latest admin \
--typescript --tailwind --eslint --app --src-dir \
--import-alias '@/*' --use-npm
cd admin && npx shadcn@latest init
npx shadcn@latest add button card input textarea select table tabs badge dialog dropdown-menu separator toast form sheet skeleton
npm install @supabase/supabase-js @supabase/ssr @tanstack/react-table recharts react-hook-form @hookform/resolvers zod @dnd-kit/core @dnd-kit/sortable lucide-react
Files (new): see directory in section 5.2. For Phase 3, deliver:
- Root layout with Sidebar + Topbar (placeholder nav).
middleware.tsenforcing auth./loginwith magic link form./dashboard placeholder (just stat cards with hard-coded zeros).lib/supabase/server.ts+browser.ts.lib/auth-guard.ts(requireRole).lib/locales.ts.
Acceptance:
npm run devopens portal at localhost:3000.- Login with a Supabase user gives access; user with no role sees “Access denied”.
npm run typecheckandnpm run lintclean.
Phase 4 — Admin CRUD: modules + chapters + cards (3–4 days)
Implement, in order:
- Modules list (
/modules) — DataTable + filters + search. - Module create / edit (
/modules/new,/modules/[slug]) — Form + TranslationTabs. - Server actions (
actions/modules.ts):createModule(input)— inserts module + zh-TW translation in one Server Action (transactionally via RPC if needed, else best-effort).updateModule(slug, input)upsertModuleTranslation(slug, locale, payload)toggleModuleActive(slug, active)
- Chapters list + reorder (
/modules/[slug]/chapters). - Chapter server actions (
actions/chapters.ts). - Cards list (
/modules/[slug]/chapters/[chapterId]/cards). - Card editor with MCQ + Cloze editors and live preview.
- Card server actions (
actions/cards.ts).
Acceptance:
- Admin can create a module → add a chapter → add an MCQ card → see it via Supabase Studio.
- Translation tab “en” can be left blank.
- All writes appear in
content_revisions.
Phase 5 — Admin CRUD: pathways + featured (2 days)
/pathwayslist + DataTable./pathways/new,/pathways/[slug]form with PathwayModulePicker (drag-drop)./featuredreuses existingfeatured_itemstable; CRUD with similar TranslationTabs (existing schema has no translations — leave as-is for v0.2, mark as todo for v0.3).
Phase 6 — Mobile app schema + sync (2–3 days)
- Update
@umikora/sharedtypes (section 6.1). - Add SCHEMA_VERSION 5 migration in
database.ts(section 6.2). - Add
supabaseContentClient.ts. - Update
localRepository.ts(locale-aware reads + sync method). AppContextruns sync on startup when online.- Add language selector in Users tab.
- Add
en.tsUI strings file (start with the keys actually rendered; mark untranslated keys with TODO).
Acceptance:
- App with local Supabase running shows newly-created module after restart.
- Switching locale in Users tab re-renders module list with the chosen locale’s title; falls back to zh-TW if en missing.
- Offline launch still works (legacy
official-v1.jsonseed if SQLite empty).
Phase 7 — Admin dashboard + analytics + system pages (3 days)
- Dashboard stat cards backed by live counts.
- RecentActivity from
content_revisions. - TranslationCoverage: per-locale percentages across modules / chapters / cards / pathways.
- Analytics page: placeholders + the queries that are possible today (lowest-correctness cards once mobile sends
review_events; otherwise mark as “requires v1.1”). - System page: Supabase health + migration list + edge function ping.
Phase 8 — CI/CD + Vercel + EAS (1 day)
- Update
.github/workflows/test.yml:- Add steps to typecheck + lint
apps/admin.
- Add steps to typecheck + lint
- Create
vercel.jsonin repo root (or configure via Vercel dashboard): project root =apps/admin. - Add Vercel env vars per environment (Preview = staging Supabase; Production = production Supabase).
- Confirm EAS staging / production profiles use the right Supabase env via
eas.json. - Update
docs/INFRA.md(see Phase 9 doc updates).
Acceptance:
- Pushing to
stagingdeploys admin preview + Supabase staging migrations. - Pushing to
maindeploys admin production + Supabase production migrations.
Phase 9 — Documentation updates (0.5 day)
Update:
docs/INFRA.md— admin portal env matrix + Vercel setup.docs/ARCHITECTURE.md— new layer diagram + chapters/translations.docs/MOBILE.md— new locale selector, sync behaviour.AGENTS.md— task→files for admin portal.TODO.md— strike completed items; add v0.3 backlog (translations completeness, multimedia, content packs).
Total estimated effort
| Phase | Estimate |
|---|---|
| 0 — local infra | 1 d |
| 1 — schema | 1–2 d |
| 2 — seed | 0.5 d |
| 3 — admin scaffold | 1 d |
| 4 — admin modules/chapters/cards | 3–4 d |
| 5 — admin pathways/featured | 2 d |
| 6 — mobile schema+sync | 2–3 d |
| 7 — dashboard/analytics/system | 3 d |
| 8 — CI/CD | 1 d |
| 9 — docs | 0.5 d |
| Total | 15–18 days |
Phases 4–7 are largely independent and can be parallelized by separate developers.
9. Open questions / future work
- Translations completeness gate: should we block publishing a module if en translation is missing? Currently no — UI only flags.
- Featured items i18n: not in scope for v0.2; will need its own migration when added.
- Mobile auth + user accounts: required before per-user features (favourites, cross-device progress).
- Content packs: revisit when offline strategy matters (e.g. school deployments without reliable Wi-Fi).
- Realtime updates: Supabase Realtime channel for content changes (so admin edits show in mobile without manual refresh).
- Image / audio cards: requires storage bucket + new card type schema.
- Bulk content import: CSV / JSON import in admin for power users.
References
- docs/ARCHITECTURE.md — current architecture (to be updated in Phase 9)
- docs/INFRA.md — current Supabase featured-feed infra
- docs/MOBILE.md — current screen map
- docs/PRD.md — v0.2 PRD (to be updated)
- AGENTS.md — agent guide
- TODO.md — v1.1 backlog