Designing Luna Yoga’s Backend: A Star Schema on Supabase

What I set out to do

Luna Yoga pairs three things — a user’s menstrual-cycle phase, the current moon phase, and a library of yoga styles — to recommend a practice. In Phase 1 that logic lived in the front end with mocked data. For Phase 2 I needed a real database that could store every practice a user logs and then slice that history: by cycle phase, by moon phase, by yoga type, by date. That “one central record, many ways to slice it” shape is exactly what a star schema is for.

Why a star schema

A star schema has one fact table in the middle — the events you measure — surrounded by dimension tables that describe each event. It’s named for its shape: the fact table is the center of the star, and the dimensions are the points.

For Luna, the fact is a logged practice session, and the dimensions are everything I’d want to filter or group by:

• Who practiced — the user
• When — the date
• What — the yoga type
• Which moon phase it fell on
• Which cycle phase the user was in

Keeping those as separate dimension tables (instead of cramming every detail into one giant table) means the moon-phase names, yoga descriptions, and cycle-phase info are each stored once and reused, and queries like “which styles do users reach for during their luteal phase?” become a simple join and group-by.

The tables, one by one

fact_practice — the center

One row per yoga session a user logs. It holds the measurements (how long, how it felt) and a foreign key out to each dimension. This is the table that grows; the dimensions stay small.

create table public.fact_practice (
  practice_id   bigint generated always as identity primary key,
  user_id       uuid        not null references public.dim_user (user_id) on delete cascade,
  date_id       date        not null references public.dim_date (date_id),
  yoga_type_id  smallint    references public.dim_yoga_type (yoga_type_id),
  moon_phase_id smallint    references public.dim_moon_phase (moon_phase_id),
  cycle_phase_id smallint   references public.dim_cycle_phase (cycle_phase_id),
  duration_min  int,
  completed     boolean     default false,
  rating        smallint    -- 1–5, how the session felt
);

dim_user — who

The person practicing. On Supabase I tie this to the built-in auth.users table so a profile row only exists for a real authenticated account, and I store the cycle info the recommendation logic needs.

create table public.dim_user (
  user_id          uuid primary key references auth.users (id) on delete cascade,
  display_name     text,
  avg_cycle_length int  default 28,
  created_at       timestamptz default now()
);

dim_moon_phase — which moon phase

The eight lunar phases, stored once. Small, stable, and seeded up front.

create table public.dim_moon_phase (
  moon_phase_id smallint primary key,
  name          text not null,   -- New, Waxing Crescent, First Quarter, …
  illumination  numeric(4,1)     -- 0.0–100.0 % lit
);

dim_cycle_phase — which cycle phase

The four menstrual-cycle phases, each with the energy level Luna uses to tune its suggestions.

create table public.dim_cycle_phase (
  cycle_phase_id smallint primary key,
  name           text not null,  -- Menstrual, Follicular, Ovulatory, Luteal
  energy_level   text            -- low, rising, peak, winding-down
);

dim_yoga_type — what

The library of styles the app can recommend.

create table public.dim_yoga_type (
  yoga_type_id smallint primary key,
  name         text not null,    -- Yin, Vinyasa, Restorative, …
  intensity    text,             -- gentle, moderate, vigorous
  description  text
);

dim_date — when

A date dimension lets me group practices by weekday, weekend, or month without parsing timestamps in every query. I also hang the moon phase off the date, since the moon’s phase is a property of the day.

create table public.dim_date (
  date_id       date primary key,
  day_of_week   text,
  is_weekend    boolean,
  moon_phase_id smallint references public.dim_moon_phase (moon_phase_id)
);

The Supabase part

Supabase is Postgres underneath, so all of the above is just standard SQL run in the Supabase SQL editor. The two things I leaned on that are specific to it:

• Auth integration. dim_user.user_id references auth.users(id), so identities come from Supabase Auth and I never store passwords myself.
• Row Level Security (RLS). This is the important one. By default any authenticated client could read any row, so I enable RLS on the fact table and write a policy that limits each user to their own practice history.

alter table public.fact_practice enable row level security;

create policy "Users read their own practice"
  on public.fact_practice
  for select
  using (auth.uid() = user_id);

create policy "Users log their own practice"
  on public.fact_practice
  for insert
  with check (auth.uid() = user_id);

auth.uid() returns the logged-in user’s id, so a user can only ever see or insert rows tagged with their own user_id — enforced at the database, not just in the app.

Decisions I made (and why)

• Star over one big table. Dimensions are tiny and reused, which keeps the data clean and the filtering queries simple.
• Seed the dimensions. Moon phases, cycle phases, and yoga types are fixed sets, so I insert them once at setup rather than creating them on the fly.
• RLS from day one. Health and cycle data is sensitive. Turning on row-level security before writing any endpoints meant I never had a window where the data was exposed.

What I’d do differently

Add a note here once you’ve lived with the schema — e.g. whether a bridge table is needed for practices that span more than one yoga type, or how indexing the foreign keys affects query speed as the fact table grows.

Next

With the schema in place, the next entry is the recommendation query itself: joining the fact table to the current moon phase and cycle phase to surface the right practice.