SimpleFinanceDash/.planning/phases/06-preset-data-first-run-detection-and-db-safety/06-01-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
06-preset-data-first-run-detection-and-db-safety	01	execute	1		supabase/migrations/006_uniqueness_constraints.sql supabase/migrations/007_setup_completed.sql src/lib/types.ts	true	AUTO-01 AUTO-03 SETUP-01	truths artifacts key_links Migration 006 adds a unique constraint on budgets(user_id, start_date) with safe deduplication Migration 006 adds a unique constraint on categories(user_id, name) with safe deduplication Migration 007 adds setup_completed boolean NOT NULL DEFAULT false to profiles Migration 007 backfills setup_completed = true for all users with existing categories Profile TypeScript interface includes setup_completed: boolean path provides supabase/migrations/006_uniqueness_constraints.sql Atomic deduplication + unique constraint DDL for budgets and categories path provides supabase/migrations/007_setup_completed.sql ALTER TABLE profiles ADD COLUMN setup_completed + backfill UPDATE path provides contains src/lib/types.ts Updated Profile interface with setup_completed field setup_completed: boolean from to via pattern supabase/migrations/007_setup_completed.sql profiles table ALTER TABLE profiles ADD COLUMN setup_completed boolean NOT NULL DEFAULT false from to via pattern src/lib/types.ts Profile interface TypeScript field setup_completed: boolean

Write two Supabase migration files and update the Profile TypeScript type.

Migration 006 makes duplicate budget/category creation impossible at the DB level by first deduplicating any existing rows then adding UNIQUE constraints. Migration 007 adds the setup_completed boolean column to profiles and backfills existing users who have categories to true. The TypeScript Profile interface in src/lib/types.ts is updated to include setup_completed: boolean.

Purpose: The DB layer must enforce uniqueness atomically (prevents race conditions) and must know which users have already completed setup (prevents existing v1.0 users from seeing the wizard on first v2.0 login). Output: 2 .sql migration files + 1 updated TypeScript type file.

<execution_context> @$HOME/.claude/get-shit-done/workflows/execute-plan.md @$HOME/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/phases/06-preset-data-first-run-detection-and-db-safety/06-CONTEXT.md @.planning/phases/06-preset-data-first-run-detection-and-db-safety/06-RESEARCH.md

From supabase/migrations/001_profiles.sql — profiles table columns: id, display_name, locale, currency, created_at, updated_at (NO setup_completed yet)

From supabase/migrations/002_categories.sql — categories table: id, user_id, name, type, icon, sort_order, created_at, updated_at Existing index: categories_user_id_idx (NO unique constraint on (user_id, name) yet)

From supabase/migrations/004_budgets.sql — budgets table: id, user_id, start_date, end_date, currency, carryover_amount, created_at, updated_at Existing index: budgets_user_id_idx (NO unique constraint on (user_id, start_date) yet)

From src/lib/types.ts — Profile interface (before this plan): export interface Profile { id: string display_name: string | null locale: string currency: string created_at: string updated_at: string }

Task 1: Write migration 006 — uniqueness constraints with safe deduplication supabase/migrations/006_uniqueness_constraints.sql - supabase/migrations/002_categories.sql - supabase/migrations/004_budgets.sql - .planning/phases/06-preset-data-first-run-detection-and-db-safety/06-RESEARCH.md (Pattern 1: Safe Deduplication) Create `supabase/migrations/006_uniqueness_constraints.sql` with this exact content — a single transaction that deduplicates then constrains:

-- Migration 006: Add uniqueness constraints to budgets and categories
-- Safe deduplication runs first inside the transaction before each constraint.

BEGIN;

-- Deduplicate budgets: keep the oldest row per (user_id, start_date)
DELETE FROM budgets
WHERE id NOT IN (
  SELECT DISTINCT ON (user_id, start_date) id
  FROM budgets
  ORDER BY user_id, start_date, created_at ASC
);

ALTER TABLE budgets
  ADD CONSTRAINT budgets_user_month_unique UNIQUE (user_id, start_date);

-- Deduplicate categories: keep the oldest row per (user_id, name)
DELETE FROM categories
WHERE id NOT IN (
  SELECT DISTINCT ON (user_id, name) id
  FROM categories
  ORDER BY user_id, name, created_at ASC
);

ALTER TABLE categories
  ADD CONSTRAINT categories_user_name_unique UNIQUE (user_id, name);

COMMIT;

Key: DISTINCT ON requires ORDER BY on the same leading columns — already satisfied above. Wrapping both operations in a single transaction means if constraint ADD fails, cleanup rolls back too. grep -c "ADD CONSTRAINT" supabase/migrations/006_uniqueness_constraints.sql && grep -c "BEGIN" supabase/migrations/006_uniqueness_constraints.sql && grep -c "COMMIT" supabase/migrations/006_uniqueness_constraints.sql File exists. Contains exactly 2 ADD CONSTRAINT statements, 1 BEGIN, 1 COMMIT. Both constraints named: budgets_user_month_unique and categories_user_name_unique.

Task 2: Write migration 007 — setup_completed column + backfill supabase/migrations/007_setup_completed.sql - supabase/migrations/001_profiles.sql - .planning/phases/06-preset-data-first-run-detection-and-db-safety/06-RESEARCH.md (Pattern 2: ADD COLUMN with Default + Backfill, Pitfall 3) Create `supabase/migrations/007_setup_completed.sql` with this exact content:

-- Migration 007: Add setup_completed to profiles
-- New signups default to false (not set up).
-- Existing users who have any categories are backfilled to true (already set up).
-- Wider backfill also includes users with template items to protect against
-- edge case where user created template items but skipped category creation.

ALTER TABLE profiles
  ADD COLUMN setup_completed boolean NOT NULL DEFAULT false;

-- Backfill: users with categories OR template items are considered set up
UPDATE profiles
SET setup_completed = true
WHERE id IN (
  SELECT DISTINCT user_id FROM categories
  UNION
  SELECT t.user_id FROM templates t
  INNER JOIN template_items ti ON ti.template_id = t.id
);

Note: The wider backfill UNION (categories OR template items) matches Pitfall 3 guidance from RESEARCH.md — protects v1.0 users who may have template items but no categories. grep -c "ADD COLUMN setup_completed" supabase/migrations/007_setup_completed.sql && grep -c "UPDATE profiles" supabase/migrations/007_setup_completed.sql File exists. Contains ADD COLUMN statement with boolean NOT NULL DEFAULT false and UPDATE statement with UNION backfill covering both categories and template_items.

Task 3: Update Profile TypeScript interface to include setup_completed src/lib/types.ts - src/lib/types.ts In `src/lib/types.ts`, add `setup_completed: boolean` to the `Profile` interface. The field goes after `currency` and before `created_at`:

export interface Profile {
  id: string
  display_name: string | null
  locale: string
  currency: string
  setup_completed: boolean   // <-- ADD THIS LINE
  created_at: string
  updated_at: string
}

No other changes to types.ts. grep -n "setup_completed: boolean" src/lib/types.ts src/lib/types.ts contains setup_completed: boolean inside the Profile interface. tsc --noEmit passes with no new errors.

<threat_model>

Trust Boundaries

Boundary	Description
Migration SQL → Supabase DB	DDL runs with superuser privileges via Supabase CLI — only run locally or in controlled CI
Client → profiles RLS	Client can UPDATE own profile row including setup_completed — acceptable since it's a UX flag, not a security gate

STRIDE Threat Register

Threat ID	Category	Component	Disposition	Mitigation Plan
T-06-01	Tampering	budgets table — duplicate row creation via concurrent requests	mitigate	UNIQUE constraint on (user_id, start_date) enforced at DB level; second INSERT returns PostgREST error 23505
T-06-02	Tampering	categories table — duplicate name creation	mitigate	UNIQUE constraint on (user_id, name) enforced at DB level; second INSERT returns PostgREST error 23505
T-06-03	Elevation of privilege	profiles.setup_completed — client sets own flag to false to force wizard re-display	accept	setup_completed is a UX routing flag only; no data is gated behind it; RLS allows own-row update
</threat_model>

After all 3 tasks complete:

# Confirm both migration files exist
ls supabase/migrations/006_uniqueness_constraints.sql supabase/migrations/007_setup_completed.sql

# Confirm TypeScript type updated
grep "setup_completed: boolean" src/lib/types.ts

# Confirm TypeScript compiles cleanly
npx tsc --noEmit

<success_criteria>

• supabase/migrations/006_uniqueness_constraints.sql exists, contains BEGIN/COMMIT, two DISTINCT ON dedup DELETEs, two ADD CONSTRAINT statements
• supabase/migrations/007_setup_completed.sql exists, adds column with boolean NOT NULL DEFAULT false, backfills with UNION covering categories and template_items
• src/lib/types.ts Profile interface has setup_completed: boolean
• tsc --noEmit passes with no errors </success_criteria>

After completion, create `.planning/phases/06-preset-data-first-run-detection-and-db-safety/06-01-SUMMARY.md`