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

psql --version

psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

cat > setup_supabase_db.sql << 'EOF'
-- Create authors table
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 courses table
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 enrollments table
CREATE TABLE IF NOT EXISTS 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);

-- Ensure RLS policies are appropriate for public access via REST API
ALTER TABLE public.authors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.courses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.enrollments ENABLE ROW LEVEL SECURITY;

-- Add some basic policies to allow public access through anon key
CREATE POLICY "Allow public read for courses" ON public.courses FOR SELECT USING (true);
CREATE POLICY "Allow public read for authors" ON public.authors FOR SELECT USING (true);
CREATE POLICY "Allow public read for enrollments" ON public.enrollments FOR SELECT USING (true);
EOF

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -f setup_supabase_db.sql

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -c "
SELECT table_name, column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_schema = 'public' AND table_name IN ('authors', 'courses', 'enrollments')
ORDER BY table_name, ordinal_position;"

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

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -c "
SELECT constraint_name, constraint_type 
FROM information_schema.table_constraints 
WHERE table_schema = 'public' AND table_name IN ('authors', 'courses', 'enrollments');"

             constraint_name             | constraint_type 
-----------------------------------------+-----------------
 authors_email_key                       | UNIQUE
 authors_pkey                            | PRIMARY KEY
 courses_author_id_fkey                  | FOREIGN KEY
 courses_pkey                            | PRIMARY KEY
 courses_price_cents_check               | CHECK
 enrollments_course_id_fkey              | FOREIGN KEY
 enrollments_course_id_student_email_key | UNIQUE
 enrollments_pkey                        | PRIMARY KEY
 24042_24052_1_not_null                  | CHECK
 24042_24052_2_not_null                  | CHECK
 24042_24052_3_not_null                  | CHECK
 24042_24062_1_not_null                  | CHECK
 24042_24062_2_not_null                  | CHECK
 24042_24062_3_not_null                  | CHECK
 24042_24062_4_not_null                  | CHECK
 24042_24062_5_not_null                  | CHECK
 24042_24062_6_not_null                  | CHECK
 24042_24079_1_not_null                  | CHECK
 24042_24079_2_not_null                  | CHECK
 24042_24079_3_not_null                  | CHECK
 24042_24079_4_not_null                  | CHECK
(21 rows)

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

                indexname                
-----------------------------------------
 courses_pkey
 idx_courses_author_id
 enrollments_pkey
 enrollments_course_id_student_email_key
 idx_enrollments_student_email
(5 rows)