tencent/hy3 · d1_schema · rep 2

env | grep -i supabase; echo "---"; which psql supabase 2>/dev/null; echo "---"; psql --version 2>/dev/null; supabase --version 2>/dev/null

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
---
/usr/bin/psql
/usr/bin/supabase
---
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
2.75.0

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
-- 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 courses_author_id_idx ON public.courses(author_id);
CREATE INDEX IF NOT EXISTS enrollments_student_email_idx ON public.enrollments(student_email);
SQL

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public' AND table_name IN ('authors','courses','enrollments')
ORDER BY table_name, ordinal_position;
SQL

 table_name  |  column_name  |        data_type         | is_nullable |  column_default   
-------------+---------------+--------------------------+-------------+-------------------
 authors     | id            | uuid                     | NO          | gen_random_uuid()
 authors     | name          | text                     | NO          | 
 authors     | email         | text                     | NO          | 
 courses     | id            | uuid                     | NO          | gen_random_uuid()
 courses     | author_id     | uuid                     | NO          | 
 courses     | title         | text                     | NO          | 
 courses     | price_cents   | integer                  | NO          | 
 courses     | published     | boolean                  | NO          | false
 courses     | created_at    | timestamp with time zone | NO          | now()
 enrollments | id            | bigint                   | NO          | 
 enrollments | course_id     | uuid                     | NO          | 
 enrollments | student_email | text                     | NO          | 
 enrollments | enrolled_at   | timestamp with time zone | NO          | now()
(13 rows)

echo "=== Constraint / index checks ==="
psql "$SUPABASE_DB_URL" <<'SQL'
SELECT conname, contype, pg_get_constraintdef(oid) AS def
FROM pg_constraint WHERE conrelid IN ('public.authors'::regclass,'public.courses'::regclass,'public.enrollments'::regclass)
ORDER BY conrelid::regclass::text, contype;
SQL
echo
echo "=== Indexes ==="
psql "$SUPABASE_DB_URL" <<'SQL'
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes WHERE schemaname='public' AND tablename IN ('courses','enrollments')
ORDER BY tablename, indexname;
SQL

=== Constraint / index checks ===
                 conname                 | contype |                               def                                
-----------------------------------------+---------+------------------------------------------------------------------
 authors_pkey                            | p       | PRIMARY KEY (id)
 authors_email_key                       | u       | UNIQUE (email)
 courses_price_cents_check               | c       | CHECK ((price_cents >= 0))
 courses_author_id_fkey                  | f       | FOREIGN KEY (author_id) REFERENCES authors(id)
 courses_pkey                            | p       | PRIMARY KEY (id)
 enrollments_course_id_fkey              | f       | FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
 enrollments_pkey                        | p       | PRIMARY KEY (id)
 enrollments_course_id_student_email_key | u       | UNIQUE (course_id, student_email)
(8 rows)


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

echo "=== GET /rest/v1/courses with anon key ==="
curl -s -o /tmp/courses.json -w "HTTP %{http_code}\n" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  "$SUPABASE_URL/rest/v1/courses"
echo "--- body ---"
cat /tmp/courses.json; echo

=== GET /rest/v1/courses with anon key ===
HTTP 200
--- body ---
[]

for t in authors enrollments courses; do
  code=$(curl -s -o /dev/null -w "%{http_code}" \
    -H "apikey: $SUPABASE_ANON_KEY" \
    -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
    "$SUPABASE_URL/rest/v1/$t")
  echo "/rest/v1/$t -> HTTP $code"
done
echo
echo "=== RLS status ==="
psql "$SUPABASE_DB_URL" -t -A -c "SELECT relname, relrowsecurity FROM pg_class WHERE relname IN ('authors','courses','enrollments') ORDER BY relname;"

/rest/v1/authors -> HTTP 200
/rest/v1/enrollments -> HTTP 200
/rest/v1/courses -> HTTP 200

=== RLS status ===
authors|f
courses|f
enrollments|f