Schema Overview - Database Schema

At a Glance

21

Tables

8

RLS Policies

13

Server-Only Tables

150+

Columns

All 21 Tables

#	Table	Purpose	Access
1	user_profile	User accounts, devices, referral codes	Open
2	subscriptions	Billing plans (free/premium)	Own rows
3	subscription_events	Subscription audit log	Server
4	courses	Master course catalog	Auth read
5	course_purchases	Course purchases per user	Own rows
6	custom_courses	AI-generated courses	Server
7	custom_course_purchases	Custom course purchases	Own rows
8	master_manifests	JSON content (shop, ads, etc.)	Server
9	manifest_cohorts	Manifest-to-cohort links	Server
10	ai_usage	AI credit balances	Own rows
11	ai_credit_purchases	AI credit receipts	Own rows
12	analytics	Course usage settings	Server
13	audio_usage	Play count tracking	Server
14	app_logs	Client logs and reports	Open
15	server_logs	Edge function logs	Server
16	cohorts	User group definitions	Server
17	user_cohorts	User group assignments	Server
18	discount_codes	Promo/discount codes	Server
19	user_discounts	User discount assignments	Server
20	referral_credits	Referral reward credits	Server
21	cached_handshake_responses	Cached sync responses	Server

Entity Relationships

auth.users ├── user_profile (1:1) ├── subscriptions (1:1) │ └── subscription_events (1:N) ├── course_purchases (1:N) ── courses (N:1) ├── custom_courses (1:N) │ └── custom_course_purchases (1:N) ├── ai_usage (1:1) ├── ai_credit_purchases (1:N) ├── analytics (1:N) ── courses (N:1) ├── audio_usage (1:N) ├── app_logs (1:N) ├── user_cohorts (1:1) ── cohorts (N:1) ├── user_discounts (1:N) ── discount_codes (N:1) ├── referral_credits (1:N) └── cached_handshake_responses (1:1) master_manifests ├── courses (N:1) └── manifest_cohorts (1:N) ── cohorts (N:1)

Table Groups

User & Auth (3)

user_profile, subscriptions, subscription_events

Courses (4)

courses, course_purchases, custom_courses, custom_course_purchases

Content Delivery (3)

master_manifests, manifest_cohorts, cached_handshake_responses

AI Features (2)

ai_usage, ai_credit_purchases

Analytics & Logging (4)

analytics, audio_usage, app_logs, server_logs

Cohorts & Discounts (5)

cohorts, user_cohorts, discount_codes, user_discounts, referral_credits

Security Model

Access Level	Tables	Count
Open (anon + auth)	user_profile, app_logs	2
All authenticated	courses	1
Own rows only	subscriptions, course_purchases, custom_course_purchases, ai_usage, ai_credit_purchases	5
Server only	All remaining 13 tables	13