schema/
├── extensions/
│   └── extensions.sql
├── types/
│   ├── enums.sql
│   └── composite_types.sql
├── tables/
│   ├── users.sql
│   ├── orders.sql
│   └── products.sql
├── views/
│   └── reports.sql
├── functions/
│   └── utilities.sql
└── timescaledb/
    └── policies.sql

schema/tables/users.sql          # User table definition
schema/tables/users_indexes.sql  # Indexes for users table (optional split)
schema/functions/auth.sql        # Authentication functions
schema/views/dashboard.sql       # Dashboard-related views

CREATE TABLE users (
    -- Identity column (PostgreSQL 10+)
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    -- Standard columns
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100),

    -- Timestamps with defaults
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT users_email_unique UNIQUE (email)
);

-- Separate index definition
CREATE INDEX idx_users_created_at ON users(created_at);

-- Partial index
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Or with explicit constraint
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,

    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

CREATE VIEW active_users AS
SELECT id, email, name
FROM users
WHERE status = 'active';

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- PL/pgSQL function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- SQL function
CREATE OR REPLACE FUNCTION get_user_order_count(user_id BIGINT)
RETURNS INT AS $$
    SELECT COUNT(*)::INT FROM orders WHERE user_id = $1;
$$ LANGUAGE sql STABLE;

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Enum type
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered');

-- Composite type
CREATE TYPE address AS (
    street VARCHAR(255),
    city VARCHAR(100),
    country VARCHAR(100),
    postal_code VARCHAR(20)
);

-- Domain type
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "timescaledb";

schema/
├── extensions.sql      # All extensions
├── types.sql           # All custom types
├── tables/             # One file per table
├── views/              # One file per view
└── functions.sql       # All functions

schema/
├── core/
│   ├── users.sql       # users table + indexes + functions
│   └── auth.sql        # sessions, tokens, auth functions
├── commerce/
│   ├── products.sql
│   ├── orders.sql
│   └── payments.sql
└── analytics/
    ├── events.sql
    └── aggregates.sql

schema/
├── extensions/
│   └── extensions.sql
├── types/
│   └── enums.sql
├── tables/
│   ├── users.sql
│   ├── orders.sql
│   └── products.sql
├── views/
│   └── reports.sql
└── functions/
    └── triggers.sql

-- schema/tables/users.sql
CREATE TABLE users (...);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- Good: explicit name
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)

-- Avoid: auto-generated name
FOREIGN KEY (user_id) REFERENCES users(id)

-- Explicit schema (recommended for clarity)
CREATE TABLE public.users (...);

-- Implicit schema (works, but less clear)
CREATE TABLE users (...);

COMMENT ON TABLE users IS 'Application users with authentication info';
COMMENT ON COLUMN users.status IS 'Account status: active, suspended, deleted';

git add schema/
git commit -m "Add users table definition"