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

Phase 6: Preset Data, First-Run Detection, and DB Safety - Research

Researched: 2026-04-20 Domain: PostgreSQL constraints, Supabase migrations, React Query hooks, i18n data files Confidence: HIGH

---

<user_constraints>

User Constraints (from CONTEXT.md)

Locked Decisions

• Default amounts in EUR (matches existing profiles.currency default)
• 4 income, 4 bill, 5 variable_expense, 2 debt, 2 saving, 2 investment items (~19 total)
• i18n key format: presets.{category_type}.{slug} (e.g., presets.bill.rent)
• Round number amounts (e.g., rent=1000, groceries=400) — easier to adjust, less presumptuous
• First-run triggers when user has zero categories OR zero template items
• New dedicated hook: src/hooks/useFirstRunState.ts (follows useCategories/useTemplate pattern)
• Backfill via Supabase migration SQL: UPDATE profiles SET setup_completed = true WHERE id IN (SELECT DISTINCT user_id FROM categories)
• Hook derives state from existing hooks (useCategories count + useTemplate items count) — no extra network call
• Cleanup step before adding unique constraints (deduplicate existing data safely)
• Separate migration files: 006_uniqueness_constraints.sql and 007_setup_completed.sql
• setup_completed column defaults to false; existing users backfilled to true
• Unique constraint on budgets: (user_id, start_date) only — end_date always derived

Claude's Discretion

• Exact preset item names and amounts (within the balanced category distribution)
• German translation text for preset i18n keys
• Order of migration operations within each file
• Error handling approach for constraint violations in application code

Deferred Ideas (OUT OF SCOPE)

None — discussion stayed within phase scope. </user_constraints>

<phase_requirements>

Phase Requirements

ID	Description	Research Support
AUTO-01	User's monthly budget is auto-created from template when visiting a month for the first time	useFirstRunState hook detects zero-category / zero-template-item state; budgets unique constraint prevents duplicate auto-creation
AUTO-03	Auto-creation uses the user's configured currency, not a hardcoded default	profiles.currency confirmed as text not null default 'EUR' — hook/migration must not hardcode currency
SETUP-01	New user is guided through a 3-step wizard: income → recurring items → review	setup_completed column + backfill migration provides the flag Phase 7 wizard reads to decide whether to show onboarding
SETUP-02	User sees pre-filled common budget items with sensible default amounts (~15-20 items)	src/data/presets.ts delivers the curated item library with i18n keys and EUR amounts
</phase_requirements>

---

Summary

Phase 6 is a pure data/infrastructure layer — no new UI. It consists of four discrete deliverables: two Supabase migration files (uniqueness constraints + setup_completed column), a new TypeScript data file (src/data/presets.ts), a new React Query hook (src/hooks/useFirstRunState.ts), and additions to both i18n JSON files.

The existing schema (verified against migrations 001–005) has no duplicate-prevention constraints on budgets or categories. The profiles table has no setup_completed column yet. The templates table already has unique(user_id). All hook patterns follow useQuery + useMutation from @tanstack/react-query with the Supabase JS client — useFirstRunState will be read-only (query-only) and simpler than existing hooks.

The main planning risk is the cleanup step before adding unique constraints: the deduplication SQL must run inside the same transaction as the ADD CONSTRAINT statement so the constraint cannot be violated by orphaned duplicates surviving the cleanup.

Primary recommendation: Write migration 006 as a single transaction — DELETE duplicates, then ADD CONSTRAINT — so it's atomic and safe to run on existing databases.

---

Architectural Responsibility Map

Capability	Primary Tier	Secondary Tier	Rationale
Duplicate-write prevention	Database	—	Constraint enforced at DB level; application code is a secondary guard only
First-run detection state	Frontend (React Query hook)	—	Derived from cached query data, no extra network call
Preset item library	Frontend (static data file)	—	Static TypeScript module; no DB persistence, consumed by wizard (Phase 7)
setup_completed persistence	Database (profiles table)	—	Boolean column; written by migration backfill and by wizard completion (Phase 7)
i18n for preset names	Frontend (JSON files)	—	Follows existing en.json / de.json pattern

---

Standard Stack

Core (all already installed — verified against package.json)

Library	Version	Purpose	Why Standard
@tanstack/react-query	(existing)	Server state, caching for useFirstRunState	Already used by all hooks
@supabase/supabase-js	(existing)	DB client in hooks and migrations	Already used by all hooks
PostgreSQL (via Supabase)	(existing)	Unique constraints, ALTER TABLE	Native DB feature; no library needed

No new npm packages are required for this phase.

Migration Tooling

Supabase migrations are plain .sql files in supabase/migrations/. They are run in filename order by the Supabase CLI (supabase db push) or applied directly via the Supabase dashboard SQL editor. [VERIFIED: project migration files 001–005]

---

Architecture Patterns

System Architecture Diagram

Supabase DB
  ├── Migration 006: uniqueness_constraints
  │     ├── DELETE duplicate budgets (keep oldest per user+start_date)
  │     ├── DELETE duplicate categories (keep oldest per user+name)
  │     ├── ADD CONSTRAINT budgets_user_month_unique ON budgets(user_id, start_date)
  │     └── ADD CONSTRAINT categories_user_name_unique ON categories(user_id, name)
  │
  └── Migration 007: setup_completed
        ├── ALTER TABLE profiles ADD COLUMN setup_completed boolean NOT NULL DEFAULT false
        └── UPDATE profiles SET setup_completed = true
              WHERE id IN (SELECT DISTINCT user_id FROM categories)

src/data/presets.ts  ──────────────────────────────────────► Phase 7 wizard
  (static array of PresetItem objects, keyed by i18n slug)

src/hooks/useFirstRunState.ts
  ├── calls useCategories()  (uses cached ["categories"] query — no new fetch)
  ├── calls useTemplate()    (uses cached ["template-items"] query — no new fetch)
  └── returns { isFirstRun: boolean, loading: boolean }
        isFirstRun = categories.length === 0 || items.length === 0

src/i18n/en.json  }  add "presets" top-level key
src/i18n/de.json  }  add "presets" top-level key

Recommended Project Structure (additions only)

src/
├── data/
│   └── presets.ts          # NEW: preset budget item library
├── hooks/
│   └── useFirstRunState.ts # NEW: first-run detection hook
└── i18n/
    ├── en.json             # ADD: presets.* keys
    └── de.json             # ADD: presets.* keys

supabase/migrations/
├── 006_uniqueness_constraints.sql  # NEW
└── 007_setup_completed.sql         # NEW

Pattern 1: Safe Deduplication Before Unique Constraint

What: Delete all but the oldest row per unique key before adding the constraint, wrapped in a transaction. When to use: Any migration adding a unique constraint on a table that may have existing duplicates.

-- Source: [VERIFIED: standard PostgreSQL CTE deduplication pattern]
BEGIN;

-- Keep only the oldest budget 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);

-- Keep only the oldest category 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;

Pattern 2: ADD COLUMN with Default + Immediate Backfill

What: Add a boolean column with a default, then immediately UPDATE to backfill existing rows. When to use: Column needs a non-null default for new rows, but existing rows need a different value.

-- Source: [VERIFIED: standard PostgreSQL ALTER TABLE pattern]
ALTER TABLE profiles
  ADD COLUMN setup_completed boolean NOT NULL DEFAULT false;

-- Backfill: existing users who have categories are considered set up
UPDATE profiles
SET setup_completed = true
WHERE id IN (SELECT DISTINCT user_id FROM categories);

Important: DEFAULT false in the ALTER TABLE statement means the column is added atomically with false for all existing rows before the UPDATE runs. The UPDATE then flips the qualifying rows. Order matters here. [VERIFIED: PostgreSQL behavior]

Pattern 3: Derived State Hook (no extra network call)

What: Build a hook that computes a boolean from two already-cached queries. When to use: When state can be inferred from data already loaded by sibling hooks.

// Source: [VERIFIED: matches useCategories.ts + useTemplate.ts patterns in codebase]
import { useCategories } from "@/hooks/useCategories"
import { useTemplate } from "@/hooks/useTemplate"

export function useFirstRunState() {
  const { categories, loading: catLoading } = useCategories()
  const { items, loading: tmplLoading } = useTemplate()

  return {
    isFirstRun: categories.length === 0 || items.length === 0,
    loading: catLoading || tmplLoading,
  }
}

Key insight: useCategories caches under ["categories"] and useTemplate caches under ["template"] + ["template-items"]. If those queries have already been called (e.g., by mounted pages), useFirstRunState returns instantly from cache — no extra Supabase round-trip. [VERIFIED: React Query staleTime default behavior]

Pattern 4: Preset Data File Shape

What: Static TypeScript module exporting typed preset items. When to use: Read-only reference data consumed by the wizard UI (Phase 7).

// Source: [ASSUMED — no existing presets.ts to verify against; shape inferred from types.ts]
export interface PresetItem {
  slug: string                // used to build i18n key: presets.{type}.{slug}
  type: CategoryType          // from src/lib/types.ts
  defaultAmount: number       // EUR, round number
  item_tier: "fixed" | "variable"
}

export const PRESETS: PresetItem[] = [
  // income (4)
  { slug: "salary",         type: "income",           defaultAmount: 3000, item_tier: "fixed"    },
  { slug: "freelance",      type: "income",           defaultAmount: 500,  item_tier: "variable" },
  { slug: "rental_income",  type: "income",           defaultAmount: 800,  item_tier: "fixed"    },
  { slug: "other_income",   type: "income",           defaultAmount: 200,  item_tier: "variable" },
  // bill (4)
  { slug: "rent",           type: "bill",             defaultAmount: 1000, item_tier: "fixed"    },
  { slug: "electricity",    type: "bill",             defaultAmount: 80,   item_tier: "fixed"    },
  { slug: "internet",       type: "bill",             defaultAmount: 40,   item_tier: "fixed"    },
  { slug: "phone",          type: "bill",             defaultAmount: 30,   item_tier: "fixed"    },
  // variable_expense (5)
  { slug: "groceries",      type: "variable_expense", defaultAmount: 400,  item_tier: "variable" },
  { slug: "transport",      type: "variable_expense", defaultAmount: 100,  item_tier: "variable" },
  { slug: "dining_out",     type: "variable_expense", defaultAmount: 150,  item_tier: "variable" },
  { slug: "health",         type: "variable_expense", defaultAmount: 50,   item_tier: "variable" },
  { slug: "clothing",       type: "variable_expense", defaultAmount: 100,  item_tier: "variable" },
  // debt (2)
  { slug: "loan_repayment", type: "debt",             defaultAmount: 200,  item_tier: "fixed"    },
  { slug: "credit_card",    type: "debt",             defaultAmount: 100,  item_tier: "fixed"    },
  // saving (2)
  { slug: "emergency_fund", type: "saving",           defaultAmount: 200,  item_tier: "fixed"    },
  { slug: "vacation",       type: "saving",           defaultAmount: 100,  item_tier: "fixed"    },
  // investment (2)
  { slug: "etf",            type: "investment",       defaultAmount: 200,  item_tier: "fixed"    },
  { slug: "pension",        type: "investment",       defaultAmount: 100,  item_tier: "fixed"    },
]

Pattern 5: i18n Key Structure for Presets

The existing i18n files use a flat nested structure. Add a "presets" top-level key, nested by category_type, then slug. [VERIFIED: matches en.json structure]

// en.json addition
{
  "presets": {
    "income": {
      "salary": "Salary",
      "freelance": "Freelance Income",
      "rental_income": "Rental Income",
      "other_income": "Other Income"
    },
    "bill": {
      "rent": "Rent",
      "electricity": "Electricity",
      "internet": "Internet",
      "phone": "Phone"
    },
    "variable_expense": {
      "groceries": "Groceries",
      "transport": "Transport",
      "dining_out": "Dining Out",
      "health": "Health & Pharmacy",
      "clothing": "Clothing"
    },
    "debt": {
      "loan_repayment": "Loan Repayment",
      "credit_card": "Credit Card"
    },
    "saving": {
      "emergency_fund": "Emergency Fund",
      "vacation": "Vacation Fund"
    },
    "investment": {
      "etf": "ETF / Index Fund",
      "pension": "Pension"
    }
  }
}

Anti-Patterns to Avoid

• Adding UNIQUE constraint without deduplication first: Will fail with duplicate key value violates unique constraint on any DB with existing duplicate rows.
• Separate transaction for cleanup and constraint: If cleanup succeeds but constraint ADD fails (e.g., missed a duplicate), the DB is left partially modified. Always wrap both in one BEGIN/COMMIT.
• isFirstRun computed at render without loading guard: Returns true spuriously while queries are still in-flight (both lengths are 0). Always gate on loading before acting on isFirstRun.
• Hardcoding 'EUR' in presets.ts: The preset amounts are EUR, but the label/currency displayed in the wizard must come from profiles.currency, not from presets.ts. Keep amounts as plain numbers.

---

Don't Hand-Roll

Problem	Don't Build	Use Instead	Why
Duplicate row prevention	Application-level upsert logic with SELECT-then-INSERT	PostgreSQL UNIQUE constraint	Race condition between SELECT and INSERT; DB constraint is atomic
Query caching for derived state	Manual state tracking / local useState	React Query (already installed)	useCategories + useTemplate already cache — just consume the cached data
i18n key resolution	Custom translation lookup	Existing i18n framework already in use	Check how t() is called in existing components and replicate

---

Common Pitfalls

Pitfall 1: DISTINCT ON Requires ORDER BY on the Same Columns

What goes wrong: SELECT DISTINCT ON (user_id, start_date) without ORDER BY user_id, start_date throws a PostgreSQL error. Why it happens: PostgreSQL requires the DISTINCT ON expressions to be the leftmost ORDER BY expressions. How to avoid: Always write ORDER BY user_id, start_date, created_at ASC (or whichever tiebreaker). [VERIFIED: PostgreSQL docs behavior] Warning signs: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Pitfall 2: isFirstRun is True During Loading

What goes wrong: categories.length === 0 || items.length === 0 is true while data is still loading (both arrays are empty defaults). If Phase 7 wizard triggers on isFirstRun, it fires on every page load while data fetches. Why it happens: useCategories returns [] as default; hook consumers see isFirstRun = true before the query resolves. How to avoid: Export loading from useFirstRunState. Callers must check !loading && isFirstRun before acting. [VERIFIED: useCategories.ts returns query.data ?? []] Warning signs: Wizard flashes on every load for users who have data.

Pitfall 3: Backfill Misses Users Without Categories

What goes wrong: The backfill SQL sets setup_completed = true only for users who have categories. A v1.0 user who created a template but no categories would remain false and see the wizard on first v2.0 login. Why it happens: The backfill uses categories as the signal; it's possible (though unlikely in v1.0) to have a template with no categories. How to avoid: The agreed logic (zero categories OR zero template items = first run) means a user with template items but no categories is legitimately not set up. Verify that v1.0 users always created categories before template items. If uncertain, widen the backfill:

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
);

Warning signs: v1.0 users reporting wizard appearing unexpectedly.

Pitfall 4: Profile Type Missing setup_completed

What goes wrong: After migration 007 adds setup_completed, the TypeScript Profile interface in src/lib/types.ts still lacks the field. Any code reading profile.setup_completed gets a TypeScript error or undefined. Why it happens: Migrations and TypeScript types are manually kept in sync — there is no auto-generation in this project. How to avoid: Include updating src/lib/types.ts as an explicit task in the plan. Add setup_completed: boolean to the Profile interface. [VERIFIED: types.ts inspected — field absent] Warning signs: TypeScript compiler error on profile.setup_completed.

Pitfall 5: Migration File Ordering

What goes wrong: A migration named 006_... that runs after one named 007_... (or vice versa) due to filename sort. Why it happens: Supabase CLI applies migrations in lexicographic filename order. How to avoid: Uniqueness constraints must come in 006_... and setup_completed in 007_... because the backfill in 007 reads from categories — which needs to exist and be constraint-safe first. The order is correct as specified. [VERIFIED: existing migrations 001–005 confirm naming convention]

---

Code Examples

Confirmed: Category Type Enum Values

From 002_categories.sql and src/lib/types.ts [VERIFIED]:

'income' | 'bill' | 'variable_expense' | 'debt' | 'saving' | 'investment'

Preset items must use these exact string values as type.

Confirmed: profiles Table Columns (before migration 007)

From 001_profiles.sql [VERIFIED]:

id, display_name, locale, currency, created_at, updated_at

No setup_completed column exists yet. Migration 007 adds it.

Confirmed: budgets Table (before migration 006)

From 004_budgets.sql [VERIFIED]:

• No unique constraint on (user_id, start_date).
• start_date date not null, end_date date not null — both present.

Confirmed: categories Table (before migration 006)

From 002_categories.sql [VERIFIED]:

• No unique constraint on (user_id, name).
• Only a categories_user_id_idx index exists.

---

State of the Art

Old Approach	Current Approach	When Changed	Impact
Application-level duplicate checks	DB-level UNIQUE constraint	This phase	Eliminates race conditions; constraint error surfaces as Supabase PostgREST error code 23505
No first-run concept	setup_completed + useFirstRunState	This phase	Enables Phase 7 wizard to show only to new users

---

Assumptions Log

#	Claim	Section	Risk if Wrong
A1	item_tier: "one_off" is excluded from presets (only "fixed" and "variable")	Pattern 4	If wizard allows one_off presets, the type needs updating — but template_items.item_tier check constraint already limits to ('fixed', 'variable') so this is safe
A2	No existing duplicate budgets or categories in production data	Migration 006	If duplicates exist the DELETE+CONSTRAINT pattern handles them; risk is low, the cleanup step is exactly for this
A3	The i18n library in use supports nested key access via dot notation (e.g., t('presets.bill.rent'))	i18n pattern	If it uses a flat key format, key structure needs adjustment — check existing t() calls in components before writing keys

---

Open Questions

1. How does the app call t()?

   • What we know: en.json and de.json use nested objects (e.g., categories.types.income).
   • What's unclear: Whether translation is t('categories.types.income') (dot-path) or t('categories')['types']['income']. Most react-i18next setups use dot-path.
   • Recommendation: Before writing i18n keys, grep one existing t() call in the codebase to confirm syntax. Plan task should include this verification.

2. Are there any existing v1.0 users in production?

   • What we know: The blocker in STATE.md says "existing v1.0 users must not see the wizard on first v2.0 login."
   • What's unclear: Whether production has real user rows in profiles or if this is a personal/dev-only app.
   • Recommendation: The migration is written defensively regardless — backfill runs on all qualifying rows, so there's no harm if the table is empty.

---

Environment Availability

Step 2.6: SKIPPED (no new external dependencies — all tools are existing: Supabase CLI, PostgreSQL, Node/TypeScript toolchain already in use by the project).

---

Validation Architecture

Test Framework

Property	Value
Framework	None detected — no vitest.config., jest.config., or test scripts in package.json
Config file	None — Wave 0 must create if tests are written
Quick run command	N/A
Full suite command	N/A

Phase Requirements → Test Map

Req ID	Behavior	Test Type	Automated Command	File Exists?
AUTO-01	useFirstRunState returns isFirstRun=true when categories=[]	unit	vitest run src/hooks/useFirstRunState.test.ts	❌ Wave 0
AUTO-01	useFirstRunState returns isFirstRun=false when categories and items present	unit	vitest run src/hooks/useFirstRunState.test.ts	❌ Wave 0
AUTO-01	useFirstRunState returns loading=true while queries in flight	unit	vitest run src/hooks/useFirstRunState.test.ts	❌ Wave 0
SETUP-02	PRESETS exports exactly 19 items	unit	vitest run src/data/presets.test.ts	❌ Wave 0
SETUP-02	Each preset has valid type from CategoryType enum	unit	vitest run src/data/presets.test.ts	❌ Wave 0
SETUP-02	Each preset has item_tier of fixed or variable (not one_off)	unit	vitest run src/data/presets.test.ts	❌ Wave 0
AUTO-03	Profile.setup_completed TypeScript type is boolean	compile-time	tsc --noEmit	❌ Wave 0 (after types.ts update)
DB constraints	Unique constraint SQL — not unit-testable without Supabase local instance	manual	Supabase local dev + insert duplicate	N/A

Sampling Rate

• Per task commit: tsc --noEmit (type safety at minimum)
• Per wave merge: vitest run (once vitest is set up)
• Phase gate: All passing before /gsd-verify-work

Wave 0 Gaps

• vitest not installed — install: npm install -D vitest @vitest/ui
• src/hooks/useFirstRunState.test.ts — covers AUTO-01 loading guard and isFirstRun logic
• src/data/presets.test.ts — covers SETUP-02 count, type validity, item_tier validity
• vitest.config.ts — framework config (or add "test" to vite.config.ts)

---

Security Domain

Applicable ASVS Categories

ASVS Category	Applies	Standard Control
V2 Authentication	no	—
V3 Session Management	no	—
V4 Access Control	yes	Supabase RLS already enabled on all tables — new constraints do not bypass RLS
V5 Input Validation	no	No new user-facing input in this phase
V6 Cryptography	no	—

Known Threat Patterns

Pattern	STRIDE	Standard Mitigation
Duplicate budget creation via parallel requests	Tampering	UNIQUE constraint on (user_id, start_date) — DB rejects second insert atomically
First-run flag manipulation (client sets setup_completed)	Elevation of privilege	RLS policy "Users can update own profile" — user can update their own row; acceptable since setup_completed is not a security gate, only a UX flag

---

Sources

Primary (HIGH confidence)

• supabase/migrations/001_profiles.sql — profiles table schema, trigger, no setup_completed column confirmed
• supabase/migrations/002_categories.sql — categories table schema, no unique constraint on (user_id, name) confirmed
• supabase/migrations/004_budgets.sql — budgets table schema, no unique constraint on (user_id, start_date) confirmed
• src/hooks/useCategories.ts — hook pattern, query key ["categories"], default returns []
• src/hooks/useTemplate.ts — hook pattern, query keys, items default []
• src/lib/types.ts — CategoryType values, Profile interface (no setup_completed), all confirmed
• src/i18n/en.json, de.json — i18n structure and nesting pattern confirmed
• .planning/config.json — nyquist_validation: true confirmed

Secondary (MEDIUM confidence)

• PostgreSQL DISTINCT ON + ORDER BY requirement — standard behavior, well-documented

Tertiary (LOW confidence)

• A3: i18n dot-path notation — inferred from JSON structure; needs grep verification

---

Metadata

Confidence breakdown:

• Standard stack: HIGH — all libraries verified as already installed
• Architecture: HIGH — all schema details verified against actual migration files and hook source
• Pitfalls: HIGH — derived from direct inspection of existing code and standard PostgreSQL semantics
• Preset content: MEDIUM — exact amounts and slugs are at Claude's discretion; structure is HIGH confidence

Research date: 2026-04-20 Valid until: 2026-07-20 (stable domain — PostgreSQL constraints and React Query patterns are not fast-moving)