data-architect

Validate Supabase schema changes for integrity, performance, and RLS policies.

3|Updated Mar 1, 2026

Search Tags:#security #database #migrations #rls #schema #review

One-click install

npx skills add https://github.com/YanCheng-go/danskprep --skill data-architect-yancheng-go

Or copy as Structured Prompt for Agent▼

Please help me install this Agent Skill.
Skill: data-architect
Source: https://github.com/YanCheng-go/danskprep/tree/main/.claude/skills/data-architect
Command: npx skills add https://github.com/YanCheng-go/danskprep --skill data-architect-yancheng-go

SYSTEM DOCUMENTATION & REQUIREMENTS

Data Architect

Design, review, and evolve the DanskPrep database schema. Acts as the Data Architect role: ensures schema integrity, migration safety, query performance, and RLS correctness.

Reference: Read .claude/references/supabase-patterns.md first — it covers client usage, RLS patterns, query conventions, and migration rules.

Instructions

Use this skill when:

Adding a new table or column
Designing a complex query or index strategy
Reviewing a migration before it runs in production
Deciding how to store a new data type (e.g. new inflection pattern, new exercise type)

Step 1 — Review Existing Schema

cat supabase/migrations/001_initial_schema.sql
ls supabase/migrations/

Key tables: | Table | Purpose | RLS | |-------|---------|-----| | `words` | Vocabulary, inflections as JSONB | Public read | | `grammar_topics` | Grammar explanations by module | Public read | | `exercises` | Quiz questions, all types | Public read | | `sentences` | Danish/English pairs | Public read | | `user_cards` | FSRS state per user per item | Private (user_id) | | `review_logs` | Review history for analytics | Private (user_id) |

Step 2 — Schema Design Checklist

For any new table, verify:

-- 1. Primary key
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
-- 2. Timestamps
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
-- 3. User ownership (if user-specific data)
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
-- 4. Foreign key integrity
topic_id UUID REFERENCES grammar_topics(id) ON DELETE SET NULL,
-- 5. Constraints
CHECK (module_level BETWEEN 1 AND 5),
CHECK (exercise_type IN ('type_answer','cloze','multiple_choice','word_order','error_correction','matching','conjugation')),
-- 6. Indexes
CREATE INDEX idx_user_cards_user_id ON user_cards(user_id);
CREATE INDEX idx_user_cards_due ON user_cards(due) WHERE state > 0;

Step 3 — RLS Policy Template

-- Enable RLS
ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;
-- Public read (content tables)
CREATE POLICY "public_read" ON new_table
  FOR SELECT USING (true);
-- User-scoped (data tables)
CREATE POLICY "user_select" ON new_table
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "user_insert" ON new_table
  FOR INSERT WITH CHECK (auth.uid() = user_id);
-- IMPORTANT: UPDATE needs both USING and WITH CHECK
-- USING  = which rows can be accessed (before update)
-- WITH CHECK = what values are allowed (after update)
-- Without WITH CHECK, a user could change user_id to hijack the row
CREATE POLICY "user_update" ON new_table
  FOR UPDATE USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
CREATE POLICY "user_delete" ON new_table
  FOR DELETE USING (auth.uid() = user_id);

UPDATE `WITH CHECK` rule: Every UPDATE policy on user-owned tables MUST include `WITH CHECK` matching the `USING` clause. Without it, a user can match their own row via `USING` but then set `user_id` to another value, effectively stealing the row. This applies to guest rows too — a guest could set `is_guest = false` or assign a `user_id` without `WITH CHECK` enforcement.

Step 4 — JSONB Inflection Patterns

Inflections are stored as JSONB. Validate new POS patterns against existing conventions:

// Noun: { definite, plural_indef, plural_def }
// Verb: { present, past, perfect, imperative }
// Adjective: { t_form, e_form, comparative, superlative }
// Pronoun: { subject, object, possessive: string[] }

When adding a new POS, document the JSONB shape in CLAUDE.md.

Step 5 — Migration Safety Review

Before approving a migration, check:

[ ] Non-destructive: No DROP TABLE, DROP COLUMN, TRUNCATE without explicit user confirmation
[ ] Backwards-compatible: New columns have defaults or are nullable
[ ] Idempotent: Migration can be re-run safely (use IF NOT EXISTS, IF EXISTS)
[ ] Index naming: Follow pattern idx_{table}_{column(s)}
[ ] File naming: 00N_description.sql — never modify existing migration files
[ ] Test locally: supabase db reset before pushing

Step 6 — Query Performance Review

For any new query in hooks, evaluate:

Does it use an indexed column in WHERE / ORDER BY?
Does it select only needed columns (avoid SELECT *)?
Does it paginate if result set could be large?
Does it use .single() only when exactly one row is guaranteed?

Output Format

## Data Architecture Review — <Feature>
### Schema changes
(tables / columns / indexes being added or modified)
### Migration SQL
```sql
-- Proposed migration: 00N_description.sql

RLS policies

(list all policies for new/modified tables)

Query patterns

(new queries added to hooks, with performance notes)

Concerns

[CONCERN] Description → resolution

Decision

✓ Approved / ⚠ Approved with changes / ✗ Needs redesign

is_toxic: false

Frequently Asked Questions about data-architect

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I design a Supabase database schema with proper primary keys and foreign keys?▼

Designing a Supabase database schema requires specifying UUID primary keys, TIMESTAMPTZ columns, and foreign key constraints with ON DELETE rules. You must verify user ownership with user_id references and add CHECK constraints to validate data integrity before applying changes.

How do I write safe database migrations that won't break production?▼

Safe database migrations must be non-destructive, backwards-compatible, and idempotent. You should use IF NOT EXISTS clauses, avoid DROP statements without confirmation, ensure new columns are nullable or have defaults, and test locally with supabase db reset before pushing.

Why do Supabase RLS UPDATE policies need a WITH CHECK clause?▼

Supabase RLS UPDATE policies need a WITH CHECK clause to prevent row hijacking. Without it, users matching their own row via USING could change user_id to another value, effectively stealing the row, so WITH CHECK must enforce that auth.uid() still matches after the update.

What is the best way to store JSONB inflection patterns in a PostgreSQL database?▼

Storing JSONB inflection patterns requires validating new POS shapes against existing conventions, such as noun, verb, and adjective objects. You should document the JSONB structure for each part of speech to maintain consistency across vocabulary and grammar data.

How do I optimize PostgreSQL query performance for user-scoped data tables?▼

Optimizing PostgreSQL query performance requires indexing columns used in WHERE and ORDER BY clauses, selecting only needed columns instead of SELECT *, and paginating large result sets. You should also use partial indexes on filtered states and avoid .single() unless exactly one row is guaranteed.

Does this database schema review process work for adding new exercise types?▼

The database schema review process works for adding new exercise types by validating CHECK constraints against allowed values like cloze and multiple_choice. You can extend the exercises table while ensuring foreign key integrity to grammar topics and maintaining public read RLS policies.