Overview of the final database structure

This page explains all entities, their relationships and why they exist in the Soulfire workflow: SEO article → registration + free PDF → tests / gamification → paid materials → mentoring.

Architecture update: Lovable is the main SPA front end. News and frequently edited pages are generated with a static-site generator. WordPress has been removed.

1. Identity and profiles

Entity Purpose Main columns Notes
auth.users (built-in Supabase) Core identity, sign-in, 2FA, refresh tokens. id, email, hashed_password, last_sign_in_at … Left untouched, used as provided.
user_profiles Extended personal data used across the SPA and static site. id (= auth.uid), first_name, last_name, birth_date, relationship_status, partner_id, avatar_url, tier_result, created_at RLS: user can view and update only their row.

SQL schema

The database is created with the SQL below (for reference only).


CREATE TABLE public.coaching_bookings (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  service_id uuid NOT NULL,
  booking_time timestamp with time zone NOT NULL,
  status USER-DEFINED DEFAULT 'requested'::booking_status,
  price_cents integer,
  notes text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT coaching_bookings_pkey PRIMARY KEY (id),
  CONSTRAINT coaching_bookings_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT coaching_bookings_service_id_fkey FOREIGN KEY (service_id) REFERENCES public.coaching_services(id)
);
CREATE TABLE public.coaching_services (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  slug text UNIQUE,
  title text NOT NULL,
  description text,
  price_cents integer,
  duration_minutes integer,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT coaching_services_pkey PRIMARY KEY (id)
);
CREATE TABLE public.coupon_codes (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  product_id uuid,
  percent_off smallint CHECK (percent_off >= 1 AND percent_off <= 100),
  expires_at timestamp with time zone,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT coupon_codes_pkey PRIMARY KEY (id),
  CONSTRAINT coupon_codes_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id)
);
CREATE TABLE public.downloads (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  material_id uuid NOT NULL,
  ts timestamp with time zone DEFAULT now(),
  CONSTRAINT downloads_pkey PRIMARY KEY (id),
  CONSTRAINT downloads_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT downloads_material_id_fkey FOREIGN KEY (material_id) REFERENCES public.materials(id)
);
CREATE TABLE public.email_events (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid,
  email_type text,
  meta jsonb,
  sent_at timestamp with time zone DEFAULT now(),
  CONSTRAINT email_events_pkey PRIMARY KEY (id),
  CONSTRAINT email_events_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.materials (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL,
  path text NOT NULL,
  title text,
  is_free boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  locale text NOT NULL DEFAULT 'cs'::text,
  CONSTRAINT materials_pkey PRIMARY KEY (id),
  CONSTRAINT materials_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id)
);
CREATE TABLE public.orders (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  product_id uuid NOT NULL,
  stripe_session_id text UNIQUE,
  status USER-DEFINED DEFAULT 'pending'::order_status,
  total_amount_cents integer,
  currency character DEFAULT 'CZK'::bpchar,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT orders_pkey PRIMARY KEY (id),
  CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT orders_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id)
);
CREATE TABLE public.products (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  title text NOT NULL,
  description text,
  stripe_price_id text UNIQUE,
  is_free boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  locale text NOT NULL DEFAULT 'cs'::text,
  CONSTRAINT products_pkey PRIMARY KEY (id)
);
CREATE TABLE public.site_sections (
  slug text NOT NULL,
  label text NOT NULL,
  parent text,
  is_enabled boolean NOT NULL DEFAULT true,
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  display_order integer NOT NULL DEFAULT 999,
  label_en text,
  label_cs text,
  CONSTRAINT site_sections_pkey PRIMARY KEY (slug),
  CONSTRAINT site_sections_parent_fkey FOREIGN KEY (parent) REFERENCES public.site_sections(slug)
);
CREATE TABLE public.posts (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  title text NOT NULL,
  body text,
  excerpt text,
  published_at timestamp with time zone,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT posts_pkey PRIMARY KEY (id)
);
CREATE TABLE public.test_questions (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  test_id uuid NOT NULL,
  q_order integer,
  question text NOT NULL,
  scale text,
  locale text NOT NULL DEFAULT 'cs'::text,
  CONSTRAINT test_questions_pkey PRIMARY KEY (id),
  CONSTRAINT test_questions_test_id_fkey FOREIGN KEY (test_id) REFERENCES public.tests(id)
);
CREATE TABLE public.test_result_configs (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  test_slug text NOT NULL,
  result_key text NOT NULL,
  title text NOT NULL,
  description text,
  min_score numeric NOT NULL,
  max_score numeric NOT NULL,
  display_order smallint DEFAULT 999,
  icon_slug text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT test_result_configs_pkey PRIMARY KEY (id)
);
CREATE TABLE public.test_results (
  test_type text,
  tier_result text,
  personality_results jsonb,
  status text DEFAULT 'completed'::text CHECK (status = ANY (ARRAY['in_progress'::text, 'completed'::text])),
  meta jsonb DEFAULT '{}'::jsonb,
  user_id uuid NOT NULL,
  answers jsonb,
  scores jsonb,
  total_score numeric,
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  completed_at timestamp with time zone DEFAULT now(),
  test_id uuid,
  CONSTRAINT test_results_pkey PRIMARY KEY (id),
  CONSTRAINT test_results_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT test_results_test_id_fkey FOREIGN KEY (test_id) REFERENCES public.tests(id)
);
CREATE TABLE public.tests (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  title text NOT NULL,
  description text,
  CONSTRAINT tests_pkey PRIMARY KEY (id)
);
CREATE TABLE public.user_profiles (
  divorce_risk_level text CHECK (divorce_risk_level = ANY (ARRAY['low'::text, 'moderate'::text, 'high'::text])),
  id uuid NOT NULL,
  first_name text,
  last_name text,
  birth_date date,
  relationship_status text,
  avatar_url text,
  tier_result text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  couple_name text UNIQUE,
  male_first_name text,
  male_last_name text,
  male_birth_date date,
  female_first_name text,
  female_last_name text,
  female_birth_date date,
  male_avatar_url text,
  female_avatar_url text,
  couple_avatar_url text,
  locale text NOT NULL DEFAULT 'cs'::text,
  CONSTRAINT user_profiles_pkey PRIMARY KEY (id),
  CONSTRAINT user_profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id)
);

2. Content catalog

Entity Purpose Main columns Relations
posts News and blog articles generated into static pages. id, slug, title, body, excerpt, published_at, created_at —
products Packages that are sold or distributed for free (guide, course, TIER upgrade). id, slug, title, description, stripe_price_id, is_free, created_at 1 :n to materials.
materials Individual files or lessons belonging to a product. id, product_id, path, title, is_free, created_at RLS: row is visible if is_free = true or the user owns a row in orders with status='paid'.

Why the separation?
products enable pricing in Stripe while materials provide flexibility: a single course can contain multiple PDFs, videos and tests.

3. Orders and access

Entity Purpose Main columns Relations / logic
orders Record of every purchase (Stripe Checkout via webhook). id, user_id, product_id, stripe_session_id, status (pending/paid/…), total_amount_cents, created_at Webhook sets status='paid' which unlocks materials.
downloads Log of every download or playback. id, user_id, material_id, ts Used for conversion tracking and progress bar.

4. Tests and personalization

Entity Purpose Main columns Relations / logic
tests Test definition (TIER, OpenFit, BIG‑5…). id, slug, title, description 1 :n to test_questions.
test_questions Question bank for a single test. id, test_id, q_order, question, scale —
test_result_configs Definition of result tiers for scoring. test_slug, result_key, title, min_score, max_score Matches scores to descriptions.
test_results Result of one user in one test. id, test_id, user_id, answers JSONB, scores JSONB, total_score, completed_at RLS: uživatel vidí jen své výsledky.
user_progress (VIEW) Aggregated overview of free/paid downloads and whether a paid order exists. user_id, free_downloads, paid_downloads, total_downloads, has_paid_order No duplicate data – the view computes from downloads and orders.

5. Services and bookings

Entity Purpose Main columns Notes
coaching_services Price list and description of available services (two-on-two mentoring, event accompaniment…). id, slug, title, description, price_cents, duration_minutes, created_at Administrator can change prices without migrations.
coaching_bookings Reservation of a single appointment. id, user_id, service_id, booking_time, status (requested/confirmed/…), price_cents, notes, created_at RLS: only the owner can view and update.

6. Marketing and helper tables

Entity Why it's useful Key columns
coupon_codes Affiliate campaigns, discount promotions. code, product_id, percent_off, expires_at
email_events Audit trail and protection from duplicate drip emails (Resend). user_id, email_type, meta, sent_at
site_sections Navigation structure for the static site. slug, label, parent, is_enabled, updated_at, display_order, label_en, label_cs

7. Row-Level Security (RLS) at a glance

Table Who may SELECT / UPDATE? Summary
user_profiles id = auth.uid() Everyone sees only their own record.
materials is_free = true or the user has paid A single condition unlocks paid content.
orders, downloads, test_results, coaching_bookings, email_events user_id = auth.uid() The owner can access their own rows.
products, tests, test_questions, coaching_services, site_sections Public SELECT Harmless public tables.

How it all works together

  1. Registration / login happens via Supabase Auth.
  2. After login the SPA sets a sb-access-token cookie shared for subdomains so the static site also knows the user is signed in.
  3. Product catalog is loaded with a public SELECT from products.
  4. The user clicks a free PDF: the frontend checks material.is_free = true, signs the URL (60 s) and logs a row in downloads.
  5. After a campaign they purchase a guide → Stripe Checkout → webhook inserts/updates a row in orders with status='paid'.
  6. When they return to the product page, RLS now allows paid materials and the download works the same way.
  7. Tests (TIER, BIG‑5 …) use a generic structure, so adding a new test is just seeding tests and its questions. Results are stored in test_results; the profile can be enriched with tier_result.
  8. Mentoring bookings – the user selects a service, creates a row in coaching_bookings (status='requested'). Back‑office confirms and sends notifications via Resend (logged in email_events).

Why this structure is useful