xiaomi/mimo-v2.5 · d1_schema · rep 3

echo "SUPABASE_URL=$SUPABASE_URL"
echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"
echo "SUPABASE_ANON_KEY=$SUPABASE_ANON_KEY"
echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"
echo "SUPABASE_DB_PASSWORD=SET: ${SUPABASE_DB_PASSWORD:+yes}"

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_DB_PASSWORD=SET: yes

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" <<'SQL'
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 1) authors
CREATE TABLE IF NOT EXISTS public.authors (
  id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name  text NOT NULL,
  email text NOT NULL UNIQUE
);

-- 2) courses
CREATE TABLE IF NOT EXISTS public.courses (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id    uuid NOT NULL REFERENCES public.authors(id),
  title        text NOT NULL,
  price_cents  integer NOT NULL CHECK (price_cents >= 0),
  published    boolean NOT NULL DEFAULT false,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- 3) enrollments
CREATE TABLE IF NOT EXISTS public.enrollments (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id     uuid NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  student_email text NOT NULL,
  enrolled_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (course_id, student_email)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_courses_author_id    ON public.courses(author_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_student   ON public.enrollments(student_email);

-- Grant usage to the anon and authenticated roles so PostgREST can see the tables
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO anon, authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO anon, authenticated;
SQL

CREATE EXTENSION
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

NOTICE:  extension "pgcrypto" already exists, skipping

# Test REST API for all three tables (anonymous key, limit 0 to avoid data but confirm endpoint works)
echo "=== courses ==="
curl -s -o /dev/null -w "%{http_code}" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  "$SUPABASE_URL/rest/v1/courses?select=*&limit=0"

echo ""
echo "=== authors ==="
curl -s -o /dev/null -w "%{http_code}" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  "$SUPABASE_URL/rest/v1/authors?select=*&limit=0"

echo ""
echo "=== enrollments ==="
curl -s -o /dev/null -w "%{http_code}" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  "$SUPABASE_URL/rest/v1/enrollments?select=*&limit=0"

echo ""

=== courses ===
200
=== authors ===
200
=== enrollments ===
200

# Quick full verification: confirm empty JSON arrays come back, and check indexes in DB
echo "=== REST response for courses ==="
curl -s \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  "$SUPABASE_URL/rest/v1/courses?select=*&limit=1" | python3 -m json.tool

echo ""
echo "=== Indexes ==="
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "
  SELECT indexname, indexdef
  FROM pg_indexes
  WHERE schemaname='public'
    AND tablename IN ('courses','enrollments')
  ORDER BY tablename, indexname;
"

=== REST response for courses ===
[]

=== Indexes ===
                indexname                |                                                         indexdef                                                         
-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------
 courses_pkey                            | CREATE UNIQUE INDEX courses_pkey ON public.courses USING btree (id)
 idx_courses_author_id                   | CREATE INDEX idx_courses_author_id ON public.courses USING btree (author_id)
 enrollments_course_id_student_email_key | CREATE UNIQUE INDEX enrollments_course_id_student_email_key ON public.enrollments USING btree (course_id, student_email)
 enrollments_pkey                        | CREATE UNIQUE INDEX enrollments_pkey ON public.enrollments USING btree (id)
 idx_enrollments_student                 | CREATE INDEX idx_enrollments_student ON public.enrollments USING btree (student_email)
(5 rows)