moonshotai/kimi-k3 · d1_schema · rep 1

env | grep -i supabase | sed -E 's/(KEY|PASSWORD)=.{8}.*/\1=<redacted-present>/; s/(SUPABASE_URL|SUPABASE_PROJECT_REF|SUPABASE_DB_URL)=.*/\1=present/' ; echo '---'; which psql || echo 'no psql'; which curl || echo 'no curl'

SUPABASE_DB_PASSWORD=<redacted-present>
SUPABASE_DB_URL=present
SUPABASE_URL=present
SUPABASE_SERVICE_ROLE_KEY=<redacted-present>
SUPABASE_PROJECT_REF=present
SUPABASE_ANON_KEY=<redacted-present>
---
/usr/bin/psql
/usr/bin/curl

psql "$SUPABASE_DB_URL" -c "select version();" 2>&1 | head -5

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

cat > /tmp/schema.sql <<'EOF'
begin;

create table if not exists public.authors (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null unique
);

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

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

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

-- ensure REST API roles can read the new tables
grant select, insert, update, delete on public.authors, public.courses, public.enrollments to anon, authenticated, service_role;
grant usage, select on sequence public.enrollments_id_seq to anon, authenticated, service_role;

commit;

-- ask PostgREST to reload its schema cache so the new tables are served immediately
select pg_notify('pgrst', 'reload schema');
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/schema.sql

BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
GRANT
GRANT
COMMIT
 pg_notify 
-----------
 
(1 row)

psql "$SUPABASE_DB_URL" -c "
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;" -c "
select conrelid::regclass as table_name, conname, pg_get_constraintdef(oid) as def
from pg_constraint
where connamespace='public'::regnamespace and conrelid::regclass::text in ('authors','courses','enrollments')
order by 1,2;" -c "
select tablename, indexname from pg_indexes
where schemaname='public' and tablename in ('courses','enrollments') order by 1,2;"

 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)

 table_name  |                 conname                 |                               def                                
-------------+-----------------------------------------+------------------------------------------------------------------
 authors     | authors_email_key                       | UNIQUE (email)
 authors     | authors_pkey                            | PRIMARY KEY (id)
 courses     | courses_author_id_fkey                  | FOREIGN KEY (author_id) REFERENCES authors(id)
 courses     | courses_pkey                            | PRIMARY KEY (id)
 courses     | courses_price_cents_check               | CHECK ((price_cents >= 0))
 enrollments | enrollments_course_id_fkey              | FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
 enrollments | enrollments_course_id_student_email_key | UNIQUE (course_id, student_email)
 enrollments | enrollments_pkey                        | PRIMARY KEY (id)
(8 rows)

  tablename  |                indexname                
-------------+-----------------------------------------
 courses     | courses_author_id_idx
 courses     | courses_pkey
 enrollments | enrollments_course_id_student_email_key
 enrollments | enrollments_pkey
 enrollments | enrollments_student_email_idx
(5 rows)

for t in courses authors enrollments; do
  echo "=== /rest/v1/$t ==="
  curl -sS -w '\nHTTP %{http_code}\n' \
    "$SUPABASE_URL/rest/v1/$t?select=*" \
    -H "apikey: $SUPABASE_ANON_KEY" \
    -H "Authorization: Bearer $SUPABASE_ANON_KEY"
done

=== /rest/v1/courses ===
[]
HTTP 200
=== /rest/v1/authors ===
[]
HTTP 200
=== /rest/v1/enrollments ===
[]
HTTP 200

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=0 <<'EOF'
begin;
insert into public.authors (name, email) values ('Test Author', 'test@example.com') returning id \gset
insert into public.courses (author_id, title, price_cents) values (:'id', 'Test Course', 100) returning id \gset
insert into public.enrollments (course_id, student_email) values (:'id', 'student@example.com');
-- expect failures:
insert into public.courses (author_id, title, price_cents) values (:'id', 'Bad', -5);
insert into public.enrollments (course_id, student_email) values (:'id', 'student@example.com');
-- cascade check:
delete from public.courses where id = :'id';
select count(*) as remaining_enrollments from public.enrollments where course_id = :'id';
rollback;
EOF

BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
ROLLBACK

ERROR:  new row for relation "courses" violates check constraint "courses_price_cents_check"
DETAIL:  Failing row contains (ade4aa55-8132-4318-b0bc-32859ff69d0b, 7f92b2e4-823b-46d2-b6d1-cf6e9d5450e0, Bad, -5, f, 2026-07-24 05:00:05.280653+00).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=0 <<'EOF'
begin;
insert into public.authors (name, email) values ('T', 't@example.com') returning id \gset
insert into public.courses (author_id, title, price_cents) values (:'id', 'C', 100) returning id \gset
insert into public.enrollments (course_id, student_email) values (:'id', 's@example.com');
savepoint sp1;
insert into public.enrollments (course_id, student_email) values (:'id', 's@example.com');
rollback to savepoint sp1;
delete from public.courses where id = :'id';
select count(*) as remaining_enrollments_after_cascade from public.enrollments where course_id = :'id';
rollback;
EOF

BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
SAVEPOINT
ROLLBACK
DELETE 1
 remaining_enrollments_after_cascade 
-------------------------------------
                                   0
(1 row)

ROLLBACK

ERROR:  duplicate key value violates unique constraint "enrollments_course_id_student_email_key"
DETAIL:  Key (course_id, student_email)=(0bbd4134-641b-4b15-b854-141506bc23e8, s@example.com) already exists.