qwen/qwen3-coder-plus · d1_schema · rep 2

printenv | grep SUPABASE

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>

which psql || sudo apt-get update && sudo apt-get install -y postgresql-client

/usr/bin/psql

bash: line 1: sudo: command not found

psql "$SUPABASE_DB_URL" -c "
-- Enable UUID extension if not already enabled
CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";

-- Create authors table
CREATE TABLE public.authors (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- Create courses table
CREATE TABLE public.courses (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    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 enrollments table
CREATE TABLE public.enrollments (
    id BIGSERIAL 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 indexes
CREATE INDEX idx_courses_author_id ON public.courses(author_id);
CREATE INDEX idx_enrollments_student_email ON public.enrollments(student_email);

-- Grant permissions necessary for the REST API to work
GRANT ALL PRIVILEGES ON TABLE public.authors TO anon, authenticated, service_role;
GRANT ALL PRIVILEGES ON TABLE public.courses TO anon, authenticated, service_role;
GRANT ALL PRIVILEGES ON TABLE public.enrollments TO anon, authenticated, service_role;
GRANT USAGE ON SCHEMA public TO anon, authenticated, service_role;

-- Grant sequences usage if needed
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;
"

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

NOTICE:  extension "uuid-ossp" already exists, skipping

psql "$SUPABASE_DB_URL" -c "
\\dt public.*;

-- Check each table structure
\\d public.authors;
\\d public.courses;
\\d public.enrollments;

-- Check the indexes
\\di public.*;
"

ERROR:  syntax error at or near "\"
LINE 2: \dt public.*;
        ^

echo "
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;
" | psql "$SUPABASE_DB_URL"

 table_name  |  column_name  |        data_type         | is_nullable |             column_default              
-------------+---------------+--------------------------+-------------+-----------------------------------------
 authors     | id            | uuid                     | NO          | uuid_generate_v4()
 authors     | name          | text                     | NO          | 
 authors     | email         | text                     | NO          | 
 courses     | id            | uuid                     | NO          | uuid_generate_v4()
 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          | nextval('enrollments_id_seq'::regclass)
 enrollments | course_id     | uuid                     | NO          | 
 enrollments | student_email | text                     | NO          | 
 enrollments | enrolled_at   | timestamp with time zone | NO          | now()
(13 rows)

echo "
SELECT tc.table_name, tc.constraint_name, tc.constraint_type, kcu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_name = kcu.table_name
WHERE tc.table_name IN ('authors', 'courses', 'enrollments') AND tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_type;
" | psql "$SUPABASE_DB_URL"

 table_name  |             constraint_name             | constraint_type |  column_name  
-------------+-----------------------------------------+-----------------+---------------
 authors     | authors_pkey                            | PRIMARY KEY     | id
 authors     | authors_email_key                       | UNIQUE          | email
 courses     | courses_author_id_fkey                  | FOREIGN KEY     | author_id
 courses     | courses_pkey                            | PRIMARY KEY     | id
 enrollments | enrollments_course_id_fkey              | FOREIGN KEY     | course_id
 enrollments | enrollments_pkey                        | PRIMARY KEY     | id
 enrollments | enrollments_course_id_student_email_key | UNIQUE          | course_id
 enrollments | enrollments_course_id_student_email_key | UNIQUE          | student_email
(8 rows)

echo "
SELECT indexname, indexdef FROM pg_indexes WHERE tablename IN ('authors', 'courses', 'enrollments') AND schemaname = 'public';
" | psql "$SUPABASE_DB_URL"

                indexname                |                                                         indexdef                                                         
-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------
 authors_pkey                            | CREATE UNIQUE INDEX authors_pkey ON public.authors USING btree (id)
 authors_email_key                       | CREATE UNIQUE INDEX authors_email_key ON public.authors USING btree (email)
 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_pkey                        | CREATE UNIQUE INDEX enrollments_pkey ON public.enrollments USING btree (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)
 idx_enrollments_student_email           | CREATE INDEX idx_enrollments_student_email ON public.enrollments USING btree (student_email)
(7 rows)

echo "
SELECT conname, contype, conrelid::regclass, confrelid::regclass, conkey, confkey 
FROM pg_constraint 
WHERE conrelid = 'courses'::regclass;
" | psql "$SUPABASE_DB_URL"

          conname          | contype | conrelid | confrelid | conkey | confkey 
---------------------------+---------+----------+-----------+--------+---------
 courses_author_id_fkey    | f       | courses  | authors   | {2}    | {1}
 courses_pkey              | p       | courses  | -         | {1}    | 
 courses_price_cents_check | c       | courses  | -         | {4}    | 
(3 rows)