Optimize AI

Problem

Personal fitness data is sensitive: body weight, body composition, and training history. Most multi-tenant fitness apps enforce tenant isolation in the application layer: a WHERE clause on every query, an authorization check in every handler, middleware that hopes no one hits the API directly. One forgotten filter in one hot code path becomes an enumeration vulnerability for every other user’s data, and that bug class has shipped in enough real products to be embarrassing. I wanted a fitness platform where cross-user data leakage was structurally impossible even if the application code had bugs: authorization had to live in the database, every runtime value had to be schema-checked before it hit the component tree, and the streak logic had to survive a user in any timezone without the UTC-midnight off-by-one that breaks consecutive-day math.

Approach & Architecture

I built Optimize AI as a Next.js 15 App Router application with Supabase providing Postgres, auth, and storage. Zero custom API routes. All data operations go hooks → Supabase PostgREST → Postgres, with Row-Level Security handling what would otherwise be middleware authorization checks.

The defining decision is the RLS policy set across all nine tables: user_profiles, habits, habit_logs, weight_logs, user_macros, workouts, workout_exercises, workout_logs, workout_log_exercises. Tables with a direct user_id column enforce (SELECT auth.uid()) = user_id. Child tables (workout_exercises, workout_log_exercises) derive ownership through EXISTS subqueries against their parent, so exercises inherit isolation from the workout that owns them without denormalizing user_id onto every row. Even if a hook accidentally omits a filter, Postgres returns only the rows the caller’s JWT authorizes.

The data layer is hook-per-domain on TanStack React Query v5. Each hook (useHabits, useWeightLogs, useWorkouts, useMacros, useUser) encapsulates its Supabase query, Zod safeParse, cache key, and mutation logic. The UI layer never touches Supabase directly, so every call has exactly one place where schemas are enforced. No unsafe as assertions; TypeScript strict is enabled project-wide.

The macro calculator is a four-function pipeline: calculateBMR runs Mifflin-St Jeor with sex-specific offsets (+5 male, -161 female), calculateTDEE multiplies BMR by activity factor (sedentary 1.2×, moderate 1.55×, active 1.9×), goal adjustment applies a per-goal offset (fat loss −400 kcal, muscle gain +250, recomp 0), and getMacroSplit divides the adjusted calories into protein/fat/carb grams using goal-tuned ratios. Each is a pure function with full Vitest coverage.

Habit streaks are the other hot-spot. I store all date-sensitive values as YYYY-MM-DD strings in the user’s local timezone through a dedicated getLocalDate() utility, rather than UTC. The useHabits composite query fetches habits, fetches completion logs, walks backward from today through consecutive local dates to compute streaks, and fetches today’s completions separately so the UI reflects taps instantly. Session management uses a cookie-bridge: Next.js middleware checks the Supabase session cookie on every /dashboard/* route, and an onAuthStateChange listener clears the cookie on token expiry. Open-redirect injection on the login ?redirect= parameter is blocked with a startsWith("/") && !startsWith("//") check.

Tradeoffs

RLS imposes a per-query policy evaluation that a middleware-only design would skip, so every read pays the planner cost of the auth.uid() subquery. That cost is small for the workloads a personal fitness app sees, and it eliminates a bug class that middleware cannot. Supabase locks me into Postgres, their PostgREST conventions, and their auth model; acceptable for a solo project where the alternative is maintaining a custom API tier. Storing dates as local YYYY-MM-DD strings makes cross-timezone aggregation harder if I ever wanted a global leaderboard, but for a personal tracker timezone correctness on the streak math is worth more than theoretical portability. Skipping server actions and API routes means every table change requires an RLS policy migration, not just a code change: a disciplined constraint that has kept the schema honest.

Outcome

Optimize AI ships across nine PostgreSQL tables, all with RLS enabled, covering five fitness domains (workouts, nutrition, habits, weight, profile). The macro pipeline runs BMR → TDEE → goal adjustment → macro split as four pure functions with full Vitest coverage. The data layer is hook-per-domain on React Query v5, wrapping Supabase queries with Zod safeParse. Styling is Tailwind CSS 3.4 over a CSS-variable token system that gives both light and dark themes through next-themes. Framer Motion 12 handles page transitions. Vitest 4 with Testing Library and MSW covers BMR variants, TDEE multipliers, macro splits, date correctness, Zod schema edges, and component renders through a shared renderWithProviders wrapper. CI runs ESLint and type-check on every push through GitHub Actions.

Learnings

The subtlest bug was in streak calculation. My first implementation stored completion timestamps in UTC, so a user completing a habit at 11pm EST had the log land on the next calendar day in UTC. Their streak appeared broken even though they had hit it on time. The fix was switching every date-sensitive column to YYYY-MM-DD strings in local time and routing every read through getLocalDate(). The broader lesson was that timezone correctness is not a storage detail to paper over with formatters. It is a schema decision made once, up front, enforced everywhere. The second lesson came from designing RLS for child tables. My first draft added user_id to every table, which felt simple but denormalized ownership and meant three places to keep in sync on every write. The EXISTS-subquery pattern against the parent’s user_id kept the schema normalized, the policy readable, and made new tables cheap. Authorization became a schema shape, not a code path.