Created
December 4, 2025 02:09
-
-
Save Davis-3450/7aa0986b1a8445caa81027237e4e0174 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TYPE user_role AS ENUM ('admin','editor','visitor'); | |
| CREATE TYPE testimonial_status AS ENUM ('draft','in_review','published','rejected'); | |
| CREATE TYPE visibility AS ENUM ('public','private'); | |
| CREATE TYPE media_type AS ENUM ('image','video','file'); | |
| CREATE TYPE media_provider AS ENUM ('cloudinary','youtube','other'); | |
| CREATE TYPE moderation_decision AS ENUM ('approved','rejected'); | |
| CREATE TABLE users ( | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| email TEXT UNIQUE NOT NULL, | |
| name TEXT NOT NULL, | |
| password_hash TEXT NOT NULL, | |
| role user_role NOT NULL DEFAULT 'visitor', | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), | |
| last_login_at TIMESTAMPTZ | |
| ); | |
| CREATE TABLE categories ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT UNIQUE NOT NULL, | |
| slug TEXT UNIQUE NOT NULL, | |
| description TEXT, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE tags ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT UNIQUE NOT NULL, | |
| slug TEXT UNIQUE NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE testimonials ( | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| title TEXT NOT NULL, | |
| summary TEXT, | |
| body TEXT, | |
| author_name TEXT, | |
| author_title TEXT, | |
| organization TEXT, | |
| status testimonial_status NOT NULL DEFAULT 'draft', | |
| visibility visibility NOT NULL DEFAULT 'private', | |
| published_at TIMESTAMPTZ, | |
| featured BOOLEAN NOT NULL DEFAULT false, | |
| category_id INT REFERENCES categories(id), | |
| created_by UUID REFERENCES users(id), | |
| updated_by UUID REFERENCES users(id), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), | |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE testimonial_tags ( | |
| testimonial_id UUID REFERENCES testimonials(id) ON DELETE CASCADE, | |
| tag_id INT REFERENCES tags(id) ON DELETE CASCADE, | |
| PRIMARY KEY (testimonial_id, tag_id) | |
| ); | |
| CREATE TABLE media_assets ( | |
| id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| testimonial_id UUID REFERENCES testimonials(id) ON DELETE CASCADE, | |
| type media_type NOT NULL, | |
| provider media_provider NOT NULL, | |
| provider_id TEXT NOT NULL, | |
| url TEXT NOT NULL, | |
| thumbnail_url TEXT, | |
| metadata JSONB, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE moderation_reviews ( | |
| id SERIAL PRIMARY KEY, | |
| testimonial_id UUID REFERENCES testimonials(id) ON DELETE CASCADE, | |
| reviewer_id UUID REFERENCES users(id), | |
| decision moderation_decision NOT NULL, | |
| notes TEXT, | |
| decided_at TIMESTAMPTZ NOT NULL DEFAULT now(), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE embeds ( | |
| id SERIAL PRIMARY KEY, | |
| testimonial_id UUID REFERENCES testimonials(id) ON DELETE CASCADE, | |
| embed_token TEXT UNIQUE NOT NULL, | |
| allowed_domains TEXT[], | |
| expires_at TIMESTAMPTZ, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE api_clients ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| api_key_hash TEXT NOT NULL, | |
| role TEXT NOT NULL CHECK (role IN ('reader','editor')), | |
| allowed_origins TEXT[], | |
| rate_limit_per_minute INT DEFAULT 60, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), | |
| last_used_at TIMESTAMPTZ | |
| ); | |
| CREATE TABLE collections ( | |
| id SERIAL PRIMARY KEY, | |
| slug TEXT UNIQUE NOT NULL, | |
| title TEXT NOT NULL, | |
| description TEXT, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now() | |
| ); | |
| CREATE TABLE collection_items ( | |
| collection_id INT REFERENCES collections(id) ON DELETE CASCADE, | |
| testimonial_id UUID REFERENCES testimonials(id) ON DELETE CASCADE, | |
| position INT NOT NULL DEFAULT 0, | |
| start_at TIMESTAMPTZ, | |
| end_at TIMESTAMPTZ, | |
| PRIMARY KEY (collection_id, testimonial_id) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment