Database Schema

Table: ai_credit_purchases

AI credit purchase receipts — store tokens, amounts, duplicate detection

Table: ai_usage

AI credit tracking — weekly allowance + purchased bank credits per user

Table: analytics

Per-user course settings — selected voice, induction, background, deepening

Table: app_logs

Client app logs, error reports, and user feedback submissions

Table: audio_usage

Play count tracking — how many times each user has played each audio file

Table: cached_handshake_responses

Cached sync responses per user — speeds up subsequent handshakes

Table: cohorts

User group definitions — institutional groups, A/B test cohorts, custom splash screens

Table: course_purchases

Links users to purchased courses — store tokens, source, validation

Table: courses

Master course catalog — course codes, pricing, audio, icons, workbooks

Table: custom_course_purchases

Purchase records for custom courses — includes email-based link tokens

Table: custom_courses

AI-generated personalized courses — full JSON data, checksums, audio references

Table: discount_codes

Promo/discount codes — product type, lifespan, expiry, referral chains

Table: manifest_cohorts

Links manifests to user cohorts for targeted content delivery

Table: master_manifests

JSON content files the app syncs — shop.json, ads.json, background.json, etc.

Overview

High-level view of all 21 tables, their relationships, and RLS policies

Table: referral_credits

Credits earned from referring users — tracks amount, source, and expiry

Table: server_logs

Edge function execution logs — debugging and monitoring backend operations

Table: subscription_events

Audit log for subscription lifecycle — created, canceled, expired, renewed

Table: subscriptions

One billing plan per user — free/premium, expiry, auto-renewal, store tokens

Table: user_cohorts

Links users to their cohort group — one cohort per user

Table: user_discounts

Links discount codes to users — one entry per user+code combination

Table: user_profile

User accounts — names, email, devices, referral codes, and role