Application Invariants Reference¶

Purpose: Define critical invariants for each application that must always hold true. Inspired by the vault-platform architecture patterns.

Why This Matters: - Invariants prevent data corruption and security holes - Schema constraints catch bugs at the database level (cheaper than runtime) - Documented invariants make code review more effective - Breaking an invariant should block a release

How to Read This Document¶

Each invariant follows this format:

Field	Description
ID	Unique identifier (APP-NN)
Invariant	What must always be true
Verification	How we enforce/check it
Severity	CRITICAL (blocks release), HIGH (should fix), MEDIUM (tech debt)

Universal Invariants (All Apps)¶

These apply to every application in the homelab.

ID	Invariant	Verification	Severity
UNI-01	User-owned data has `user_id NOT NULL`	Schema constraint	CRITICAL
UNI-02	RLS is enabled on all user data tables	Migration review, `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`	CRITICAL
UNI-03	RLS policies use `auth.uid() = user_id` for ownership	Migration review	CRITICAL
UNI-04	No direct SQL without parameterization in API routes	Code review, static analysis	CRITICAL
UNI-05	All API routes verify authentication before data access	Code review	CRITICAL
UNI-06	Timestamps use `TIMESTAMPTZ`, not `TIMESTAMP`	Schema review	MEDIUM
UNI-07	UUIDs use `gen_random_uuid()` or `uuid_generate_v4()`	Schema review	LOW
UNI-08	Foreign keys have appropriate `ON DELETE` behavior	Schema review	HIGH

UNI-01: User Ownership¶

Why: Without NOT NULL, orphaned records can exist that no user owns, creating security and data integrity issues.

Check:

-- Find tables missing NOT NULL on user_id
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE column_name = 'user_id' AND is_nullable = 'YES';

UNI-04: SQL Injection Prevention¶

Forbidden patterns:

// NEVER do this
const query = `SELECT * FROM users WHERE id = '${userId}'`
await supabase.rpc('raw_query', { sql: query })

// ALWAYS use parameterized queries via Supabase client
const { data } = await supabase.from('users').select().eq('id', userId)

Static analysis pattern:

# Scan for string interpolation in SQL contexts
grep -r "\`SELECT\|INSERT\|UPDATE\|DELETE.*\${" --include="*.ts" --include="*.tsx"

Money Tracker Invariants¶

Schema: money_tracker

ID	Invariant	Verification	Severity
MT-01	Every transaction has a valid `account_id`	FK constraint + NOT NULL	CRITICAL
MT-02	Transaction amounts are non-null	`amount NUMERIC(12,2) NOT NULL`	CRITICAL
MT-03	Account types are constrained	`CHECK (account_type IN ('checking', 'savings', 'credit'))`	HIGH
MT-04	Category types are constrained	`CHECK (type IN ('income', 'expense'))`	HIGH
MT-05	Budget periods are constrained	`CHECK (period IN ('monthly', 'weekly', 'yearly'))`	HIGH
MT-06	Transaction hash is unique (duplicate prevention)	`UNIQUE(transaction_hash)`	HIGH
MT-07	Deleting an account cascades to transactions	`ON DELETE CASCADE`	HIGH
MT-08	Budget uniqueness per user/category/period	`UNIQUE(user_id, category_id, period, start_date)`	MEDIUM

MT-01: Transaction-Account Integrity¶

Why: A transaction without an account is meaningless and creates orphaned financial data.

Current schema:

account_id UUID REFERENCES accounts(id) ON DELETE CASCADE NOT NULL

MT-06: Duplicate Prevention¶

Why: CSV imports should be idempotent. Re-importing the same file shouldn't create duplicates.

Hash generation (application layer):

// Hash should be deterministic: same input = same hash
const hash = sha256(`${accountId}|${date}|${amount}|${description}`)

Missing Invariants to Add¶

ID	Proposed Invariant	Current State	Recommendation
MT-09	Transaction date cannot be in the future	No constraint	Add `CHECK (date <= CURRENT_DATE)`
MT-10	Account balance matches sum of transactions	Not enforced	Consider trigger or computed column

Trip Planner Invariants¶

Schema: trip_planner

ID	Invariant	Verification	Severity
TP-01	Every trip has a valid `user_id`	`user_id UUID NOT NULL`	CRITICAL
TP-02	Trip status is constrained	`CHECK (status IN ('planning', 'booked', 'in_progress', 'completed', 'cancelled'))`	HIGH
TP-03	Itinerary day numbers are unique per trip	`UNIQUE(trip_id, day_number)`	HIGH
TP-04	Location types are constrained	`CHECK (location_type IN ('accommodation', 'restaurant', 'attraction', 'transport', 'other'))`	HIGH
TP-05	Constraint types are constrained	`CHECK (constraint_type IN ('budget', 'weather', 'accessibility', 'dietary', 'other'))`	HIGH
TP-06	Chat message roles are constrained	`CHECK (role IN ('user', 'assistant', 'system'))`	HIGH
TP-07	Deleting a trip cascades to all child records	`ON DELETE CASCADE` on itineraries, locations, constraints, chat_messages	HIGH
TP-08	Itinerary belongs to a trip	FK constraint	CRITICAL
TP-09	Location belongs to an itinerary	FK constraint	CRITICAL

TP-02: Trip Status State Machine¶

Current states: planning → booked → in_progress → completed | cancelled

Valid transitions (not enforced in schema, application-level):

planning → booked (user confirms bookings)
planning → cancelled (user abandons)
booked → in_progress (trip start date reached)
booked → cancelled (user cancels)
in_progress → completed (trip end date passed)

Future enhancement: Consider adding a previous_status column or audit log to track transitions.

Missing Invariants to Add¶

ID	Proposed Invariant	Current State	Recommendation
TP-10	`end_date >= start_date` when both set	No constraint	Add `CHECK (end_date IS NULL OR start_date IS NULL OR end_date >= start_date)`
TP-11	Budget amount is non-negative	No constraint	Add `CHECK (budget_amount IS NULL OR budget_amount >= 0)`
TP-12	Latitude range: -90 to 90	No constraint	Add `CHECK (latitude IS NULL OR (latitude >= -90 AND latitude <= 90))`
TP-13	Longitude range: -180 to 180	No constraint	Add `CHECK (longitude IS NULL OR (longitude >= -180 AND longitude <= 180))`

TCG (Trading Card Game) Invariants¶

Schema: tcg (uses table prefix tcg_)

ID	Invariant	Verification	Severity
TCG-01	Card cost is non-negative	`CHECK (cost >= 0)`	HIGH
TCG-02	Card attack/defense are non-negative when set	`CHECK (attack >= 0)`, `CHECK (defense >= 0)`	HIGH
TCG-03	Card slug is unique	`UNIQUE(slug)`	HIGH
TCG-04	Scenario slug is unique	`UNIQUE(slug)`	HIGH
TCG-05	Deck belongs to a user	`user_id UUID NOT NULL REFERENCES auth.users(id)`	CRITICAL
TCG-06	Game session has player names	`player1_name TEXT NOT NULL`, `player2_name TEXT NOT NULL`	HIGH
TCG-07	Card types use enum	`tcg_card_type` enum	HIGH
TCG-08	Game status uses enum	`tcg_game_status` enum	HIGH
TCG-09	Only admins can modify cards	RLS policy with admin check	CRITICAL

TCG-09: Admin-Only Card Management¶

Why: Cards are game data, not user data. Regular users shouldn't be able to modify the card database.

Current RLS:

CREATE POLICY "tcg_cards_insert" ON tcg_cards
  FOR INSERT WITH CHECK (
    auth.uid() IN (SELECT id FROM auth.users WHERE raw_user_meta_data->>'role' = 'admin')
  );

TCG Game State Integrity¶

Invariants for tcg_game_sessions:

ID	Invariant	Current State
TCG-10	`current_state` is valid JSON matching game schema	Application-level validation
TCG-11	`state_history` maintains order	Array type preserves order
TCG-12	`winner_id` only set when `status = 'completed'`	Not enforced

RMS (Recipe Management System) Invariants¶

Schema: rms

Note: Add invariants when schema is reviewed.

ID	Invariant	Verification	Severity
RMS-01	Recipe belongs to a user	`user_id NOT NULL`	CRITICAL
RMS-02	Ingredient quantity is non-negative	`CHECK (quantity >= 0)`	HIGH
RMS-03	Recipe has at least one ingredient	Application-level	MEDIUM

Home Portal Invariants¶

Schema: home_portal

ID	Invariant	Verification	Severity
HP-01	Service URLs are valid format	Application-level validation	MEDIUM
HP-02	Icons reference valid icon library	Application-level	LOW

Verification Checklist¶

Before Merging Database Migrations¶

[ ] All user_id columns are NOT NULL (UNI-01)
[ ] RLS is enabled on new tables (UNI-02)
[ ] RLS policies check auth.uid() (UNI-03)
[ ] CHECK constraints exist for enum-like columns
[ ] Foreign keys have appropriate cascade behavior
[ ] Unique constraints prevent duplicate data where needed

Before Merging Application Code¶

[ ] No string interpolation in SQL contexts (UNI-04)
[ ] API routes check authentication (UNI-05)
[ ] State transitions follow documented rules
[ ] Input validation matches schema constraints

Adding New Invariants¶

When adding a new invariant:

Assign an ID following the pattern: APP-NN (e.g., MT-11)
State what must be true in plain English
Specify verification method:
Schema constraint (preferred - catches at DB level)
Application validation (when schema can't express it)
Code review (for patterns that can't be automated)
Static analysis (for code scanning)
Assign severity:
CRITICAL: Security or data integrity risk
HIGH: Should be fixed before release
MEDIUM: Technical debt, fix when convenient
LOW: Nice to have

Migration Plan for Missing Constraints¶

Priority 1 (Add in next migration)¶

-- trip_planner: Date validation
ALTER TABLE trip_planner.trips
  ADD CONSTRAINT trips_date_order
  CHECK (end_date IS NULL OR start_date IS NULL OR end_date >= start_date);

-- trip_planner: Coordinate validation
ALTER TABLE trip_planner.locations
  ADD CONSTRAINT locations_latitude_range
  CHECK (latitude IS NULL OR (latitude >= -90 AND latitude <= 90));

ALTER TABLE trip_planner.locations
  ADD CONSTRAINT locations_longitude_range
  CHECK (longitude IS NULL OR (longitude >= -180 AND longitude <= 180));

-- trip_planner: Budget non-negative
ALTER TABLE trip_planner.trips
  ADD CONSTRAINT trips_budget_positive
  CHECK (budget_amount IS NULL OR budget_amount >= 0);

Priority 2 (Consider for future)¶

-- money_tracker: No future transactions
ALTER TABLE money_tracker.transactions
  ADD CONSTRAINT transactions_no_future
  CHECK (date <= CURRENT_DATE);

This document should be updated when schemas change or new invariants are identified.