apps/api-go/migrations/20251226095007_create_tables.up.sql

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Enum
CREATE TYPE website_status AS ENUM ('up', 'down', 'unknown');

-- Users
CREATE TABLE users (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- Regions
CREATE TABLE region (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    CONSTRAINT region_pkey PRIMARY KEY (id)
);

-- Websites
CREATE TABLE website (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    url TEXT NOT NULL UNIQUE,
    user_id UUID NOT NULL,
    time_added TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT website_pkey PRIMARY KEY (id),
    CONSTRAINT website_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Website ticks
CREATE TABLE website_ticks (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    website_id UUID NOT NULL,
    region_id UUID NOT NULL,
    status website_status NOT NULL,
    response_time_ms INTEGER NOT NULL,
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT website_ticks_pkey PRIMARY KEY (id),
    CONSTRAINT website_ticks_website_id_fkey FOREIGN KEY (website_id)
        REFERENCES website(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT website_ticks_region_id_fkey FOREIGN KEY (region_id)
        REFERENCES region(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_website_user_id ON website(user_id);
CREATE INDEX idx_ticks_website_id ON website_ticks(website_id);
CREATE INDEX idx_ticks_region_id ON website_ticks(region_id);

apps/api-go/migrations/20251226095007_create_tables.down.sql

-- Drop foreign key constraints first
ALTER TABLE website_ticks DROP CONSTRAINT IF EXISTS website_ticks_region_id_fkey;
ALTER TABLE website_ticks DROP CONSTRAINT IF EXISTS website_ticks_website_id_fkey;
ALTER TABLE website DROP CONSTRAINT IF EXISTS website_user_id_fkey;

-- Drop tables (children first)
DROP TABLE IF EXISTS website_ticks;
DROP TABLE IF EXISTS website;
DROP TABLE IF EXISTS region;
DROP TABLE IF EXISTS users;

-- Drop enum
DROP TYPE IF EXISTS website_status;

-- Drop extension (optional, safe)
DROP EXTENSION IF EXISTS "pgcrypto";

Remigrate the DATABASE

// update DATABASE_URL

.env
DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres_go?sslmode=disable"

‣

# CLI is unable to read DATABASE_URL variable from .env unless its exported
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres_go?sslmode=disable"

# Check if DATABASE_URL is set
echo $DATABASE_URL

cd apps/api-go # (Run from root of project )
migrate -database "$DATABASE_URL" -path migrations up