z-ai/glm-5.2 · d1_schema · rep 1

echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"; echo "ANON key set: ${SUPABASE_ANON_KEY:+yes}"; echo "SERVICE key set: ${SUPABASE_SERVICE_ROLE_KEY:+yes}"; echo "DB password set: ${SUPABASE_DB_PASSWORD:+yes}"

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
ANON key set: yes
SERVICE key set: yes
DB password set: yes

psql "$SUPABASE_DB_URL" -c "SELECT version();"

                                   version                                    
------------------------------------------------------------------------------
 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

psql "$SUPABASE_DB_URL" <<'SQL'
BEGIN;

-- 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
);

-- 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()
);

-- 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);

COMMIT;
SQL

BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
COMMIT

psql "$SUPABASE_DB_URL" <<'SQL'
-- Expose tables via PostgREST (API roles)
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON public.authors, public.courses, public.enrollments TO anon, authenticated;
GRANT INSERT, UPDATE, DELETE ON public.authors, public.courses, public.enrollments TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.enrollments_id_seq TO anon, authenticated;
SQL

GRANT
GRANT
GRANT
GRANT

psql "$SUPABASE_DB_URL" <<'SQL'
-- Verify the Realtime publication isn't needed; tables are auto-discovered by PostgREST via GRANTs.
-- Also ensure tables are in the Supabase meta so they show in the dashboard:
ALTER TABLE public.authors REPLICA IDENTITY DEFAULT;
ALTER TABLE public.courses REPLICA IDENTITY DEFAULT;
ALTER TABLE public.enrollments REPLICA IDENTITY DEFAULT;
SQL

ALTER TABLE
ALTER TABLE
ALTER TABLE

# Test the REST API as anon
curl -s -w "\nHTTP_STATUS:%{http_code}" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Accept: application/json" \
  "$SUPABASE_URL/rest/v1/courses"

[]
HTTP_STATUS:200

# Verify the schema is exactly as requested
psql "$SUPABASE_DB_URL" <<'SQL'
\d public.authors
\d public.courses
\d public.enrollments
SQL

                  Table "public.authors"
 Column | Type | Collation | Nullable |      Default      
--------+------+-----------+----------+-------------------
 id     | uuid |           | not null | gen_random_uuid()
 name   | text |           | not null | 
 email  | text |           | not null | 
Indexes:
    "authors_pkey" PRIMARY KEY, btree (id)
    "authors_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "courses" CONSTRAINT "courses_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(id)

                              Table "public.courses"
   Column    |           Type           | Collation | Nullable |      Default      
-------------+--------------------------+-----------+----------+-------------------
 id          | uuid                     |           | not null | gen_random_uuid()
 author_id   | uuid                     |           | not null | 
 title       | text                     |           | not null | 
 price_cents | integer                  |           | not null | 
 published   | boolean                  |           | not null | false
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "courses_pkey" PRIMARY KEY, btree (id)
    "courses_author_id_idx" btree (author_id)
Check constraints:
    "courses_price_cents_check" CHECK (price_cents >= 0)
Foreign-key constraints:
    "courses_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(id)
Referenced by:
    TABLE "enrollments" CONSTRAINT "enrollments_course_id_fkey" FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE

                                   Table "public.enrollments"
    Column     |           Type           | Collation | Nullable |           Default            
---------------+--------------------------+-----------+----------+------------------------------
 id            | bigint                   |           | not null | generated always as identity
 course_id     | uuid                     |           | not null | 
 student_email | text                     |           | not null | 
 enrolled_at   | timestamp with time zone |           | not null | now()
Indexes:
    "enrollments_pkey" PRIMARY KEY, btree (id)
    "enrollments_course_id_student_email_key" UNIQUE CONSTRAINT, btree (course_id, student_email)
    "enrollments_student_email_idx" btree (student_email)
Foreign-key constraints:
    "enrollments_course_id_fkey" FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE

# Verify all three tables are accessible via REST API
echo "=== courses ===" && curl -s -w "\n%{http_code}\n" -H "apikey: $SUPABASE_ANON_KEY" "$SUPABASE_URL/rest/v1/courses"
echo "=== authors ===" && curl -s -w "\n%{http_code}\n" -H "apikey: $SUPABASE_ANON_KEY" "$SUPABASE_URL/rest/v1/authors"
echo "=== enrollments ===" && curl -s -w "\n%{http_code}\n" -H "apikey: $SUPABASE_ANON_KEY" "$SUPABASE_URL/rest/v1/enrollments"

=== courses ===
[]
200
=== authors ===
[]
200
=== enrollments ===
[]
200