Start With the Data Model, Not the UI

When vibe coding a new app, I always advise starting with your Postgres schema — not a button or a layout. Once your tables exist on Railway, you hand the schema to your AI agent and let it introspect the data model to craft a purpose-built UI/UX that perfectly mirrors your structure.

Below are three production-ready schema boilerplates for specific business verticals, a Railway-compatible Python creation script, and a guide to running safe, non-destructive migrations.

PostgreSQL Railway Python + psycopg2 Vibe Coding

Why Define the Schema First?

The schema is the spec. Everything else — routes, forms, views — flows from it.

Schema as Spec

Your tables define every entity, relationship, and constraint your app will ever handle. It is the single source of truth before a single line of Flask code is written.

AI Introspection

Give your AI agent the schema and it knows exactly what fields to put in forms, what columns to show in tables, and what relationships to navigate — zero guessing.

Zero Throwaway UI

When the UI is derived from the data model, it fits perfectly from day one. No "I need to add a column" mid-build. No data migrations born from UI afterthoughts.

Ready-to-Copy Schema Boilerplates

Three niche use cases. Production-grade schemas. Copy the SQL, run the script below, and start building.

CRM for Dog Walkers

Manage clients, their dogs, scheduled walks, and invoicing. Tracks walker assignment, walk duration and status, emergency contacts per client, vet info per dog, and line-item invoices.

clients dogs walks invoices invoice_items

-- ============================================================
-- Dog Walker CRM — PostgreSQL Schema
-- All tables use IF NOT EXISTS: safe to re-run at any time.
-- ============================================================

CREATE TABLE IF NOT EXISTS clients (
    id                  SERIAL PRIMARY KEY,
    first_name          VARCHAR(100) NOT NULL,
    last_name           VARCHAR(100) NOT NULL,
    email               VARCHAR(255) UNIQUE,
    phone               VARCHAR(20),
    address             TEXT,
    emergency_contact   VARCHAR(255),
    emergency_phone     VARCHAR(20),
    notes               TEXT,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS dogs (
    id                   SERIAL PRIMARY KEY,
    client_id            INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    name                 VARCHAR(100) NOT NULL,
    breed                VARCHAR(100),
    age_years            INTEGER,
    weight_kg            NUMERIC(5,2),
    vet_name             VARCHAR(255),
    vet_phone            VARCHAR(20),
    medical_notes        TEXT,
    feeding_instructions TEXT,
    created_at           TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS walks (
    id               SERIAL PRIMARY KEY,
    dog_id           INTEGER NOT NULL REFERENCES dogs(id) ON DELETE CASCADE,
    walker_name      VARCHAR(100),
    scheduled_at     TIMESTAMP NOT NULL,
    started_at       TIMESTAMP,
    ended_at         TIMESTAMP,
    duration_minutes INTEGER,
    status           VARCHAR(20) DEFAULT 'scheduled'
                     CHECK (status IN ('scheduled','in_progress','completed','cancelled')),
    notes            TEXT,
    created_at       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
    id           SERIAL PRIMARY KEY,
    client_id    INTEGER NOT NULL REFERENCES clients(id),
    period_start DATE NOT NULL,
    period_end   DATE NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    status       VARCHAR(20) DEFAULT 'draft'
                 CHECK (status IN ('draft','sent','paid','overdue')),
    issued_at    TIMESTAMP,
    paid_at      TIMESTAMP,
    notes        TEXT,
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoice_items (
    id          SERIAL PRIMARY KEY,
    invoice_id  INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    walk_id     INTEGER REFERENCES walks(id),
    description VARCHAR(255) NOT NULL,
    quantity    INTEGER DEFAULT 1,
    unit_price  NUMERIC(10,2) NOT NULL,
    line_total  NUMERIC(10,2) NOT NULL
);

Project Management for Web Designers

Track clients, projects (with status workflow from discovery to delivery), tasks with priorities, billable time entries, and structured invoicing per project. Perfect for a freelance studio or small agency.

clients projects tasks time_entries invoices

-- ============================================================
-- Web Designer Project Management — PostgreSQL Schema
-- All tables use IF NOT EXISTS: safe to re-run at any time.
-- ============================================================

CREATE TABLE IF NOT EXISTS clients (
    id           SERIAL PRIMARY KEY,
    company_name VARCHAR(255),
    contact_name VARCHAR(255) NOT NULL,
    email        VARCHAR(255) UNIQUE NOT NULL,
    phone        VARCHAR(20),
    website      VARCHAR(255),
    industry     VARCHAR(100),
    notes        TEXT,
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS projects (
    id           SERIAL PRIMARY KEY,
    client_id    INTEGER NOT NULL REFERENCES clients(id),
    name         VARCHAR(255) NOT NULL,
    description  TEXT,
    status       VARCHAR(30) DEFAULT 'discovery'
                 CHECK (status IN ('discovery','proposal','active','review','delivered','archived')),
    budget       NUMERIC(10,2),
    hourly_rate  NUMERIC(8,2),
    start_date   DATE,
    deadline     DATE,
    delivered_at TIMESTAMP,
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tasks (
    id           SERIAL PRIMARY KEY,
    project_id   INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title        VARCHAR(255) NOT NULL,
    description  TEXT,
    status       VARCHAR(20) DEFAULT 'todo'
                 CHECK (status IN ('todo','in_progress','review','done')),
    priority     VARCHAR(10) DEFAULT 'normal'
                 CHECK (priority IN ('low','normal','high','urgent')),
    due_date     DATE,
    completed_at TIMESTAMP,
    sort_order   INTEGER DEFAULT 0,
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS time_entries (
    id          SERIAL PRIMARY KEY,
    project_id  INTEGER NOT NULL REFERENCES projects(id),
    task_id     INTEGER REFERENCES tasks(id),
    description VARCHAR(255) NOT NULL,
    hours       NUMERIC(5,2) NOT NULL,
    billable    BOOLEAN DEFAULT TRUE,
    worked_on   DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
    id             SERIAL PRIMARY KEY,
    client_id      INTEGER NOT NULL REFERENCES clients(id),
    project_id     INTEGER REFERENCES projects(id),
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    status         VARCHAR(20) DEFAULT 'draft'
                   CHECK (status IN ('draft','sent','paid','overdue','cancelled')),
    subtotal       NUMERIC(10,2) NOT NULL,
    tax_rate       NUMERIC(5,2) DEFAULT 0,
    tax_amount     NUMERIC(10,2) DEFAULT 0,
    total          NUMERIC(10,2) NOT NULL,
    issued_date    DATE,
    due_date       DATE,
    paid_date      DATE,
    notes          TEXT,
    created_at     TIMESTAMP DEFAULT NOW()
);

Field Reporting App for Electricians

Manage clients, job sites, work orders, on-site field reports (with signature tracking), materials used per job, and invoicing. Built for trades businesses that need a digital paper trail.

clients sites jobs field_reports materials invoices

-- ============================================================
-- Electrician Field Reporting — PostgreSQL Schema
-- All tables use IF NOT EXISTS: safe to re-run at any time.
-- ============================================================

CREATE TABLE IF NOT EXISTS clients (
    id              SERIAL PRIMARY KEY,
    company_name    VARCHAR(255),
    contact_name    VARCHAR(255) NOT NULL,
    email           VARCHAR(255),
    phone           VARCHAR(20) NOT NULL,
    billing_address TEXT,
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sites (
    id                   SERIAL PRIMARY KEY,
    client_id            INTEGER NOT NULL REFERENCES clients(id),
    name                 VARCHAR(255) NOT NULL,
    address              TEXT NOT NULL,
    site_type            VARCHAR(20)
                         CHECK (site_type IN ('residential','commercial','industrial')),
    access_instructions  TEXT,
    panel_location       TEXT,
    hazard_notes         TEXT,
    created_at           TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS jobs (
    id              SERIAL PRIMARY KEY,
    site_id         INTEGER NOT NULL REFERENCES sites(id),
    client_id       INTEGER NOT NULL REFERENCES clients(id),
    job_number      VARCHAR(50) UNIQUE NOT NULL,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    job_type        VARCHAR(20)
                    CHECK (job_type IN ('installation','maintenance','repair','inspection','emergency')),
    status          VARCHAR(20) DEFAULT 'scheduled'
                    CHECK (status IN ('scheduled','in_progress','on_hold','completed','invoiced')),
    assigned_to     VARCHAR(255),
    scheduled_date  DATE,
    started_at      TIMESTAMP,
    completed_at    TIMESTAMP,
    estimated_hours NUMERIC(5,2),
    actual_hours    NUMERIC(5,2),
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS field_reports (
    id                        SERIAL PRIMARY KEY,
    job_id                    INTEGER NOT NULL REFERENCES jobs(id),
    reported_by               VARCHAR(255) NOT NULL,
    report_date               DATE NOT NULL DEFAULT CURRENT_DATE,
    work_performed            TEXT NOT NULL,
    materials_used            TEXT,
    issues_found              TEXT,
    recommendations           TEXT,
    next_steps                TEXT,
    hours_on_site             NUMERIC(5,2),
    client_signature_obtained BOOLEAN DEFAULT FALSE,
    photos_count              INTEGER DEFAULT 0,
    created_at                TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS materials (
    id              SERIAL PRIMARY KEY,
    job_id          INTEGER NOT NULL REFERENCES jobs(id),
    field_report_id INTEGER REFERENCES field_reports(id),
    item_name       VARCHAR(255) NOT NULL,
    part_number     VARCHAR(100),
    quantity        NUMERIC(8,2) NOT NULL,
    unit            VARCHAR(20),
    unit_cost       NUMERIC(10,2),
    total_cost      NUMERIC(10,2),
    supplier        VARCHAR(255),
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
    id             SERIAL PRIMARY KEY,
    job_id         INTEGER NOT NULL REFERENCES jobs(id),
    client_id      INTEGER NOT NULL REFERENCES clients(id),
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    status         VARCHAR(20) DEFAULT 'draft'
                   CHECK (status IN ('draft','sent','paid','overdue')),
    labour_cost    NUMERIC(10,2) DEFAULT 0,
    materials_cost NUMERIC(10,2) DEFAULT 0,
    tax_rate       NUMERIC(5,2) DEFAULT 10,
    total_amount   NUMERIC(10,2) NOT NULL,
    issued_date    DATE,
    due_date       DATE,
    paid_date      DATE,
    created_at     TIMESTAMP DEFAULT NOW()
);

Create Your Tables on Railway

Copy the schema above, drop it into the script below, and run it once. Your tables will be created on your remote Railway Postgres database.

Install dependencies

psycopg2-binary and python-dotenv

Set your .env variables

Copy from your Railway project dashboard

Run the script

python create_tables.py — done

Step 1 — Install dependencies

pip install psycopg2-binary python-dotenv

Step 2 — Set your `.env` file

Get these values from your Railway project → your Postgres service → Connect tab.

PGDATABASE=railway
PGUSER=postgres
PGPASSWORD=your-railway-password
PGHOST=your-host.railway.app
PGPORT=5432

Step 3 — `create_tables.py`

Save your chosen schema as schema.sql and place both files in the same folder (or at the root of your project). The script reads the SQL file directly — no pasting, no formatting risk.

import os
import psycopg2
from dotenv import load_dotenv

load_dotenv()


def get_connection():
    """Create a connection to your Railway Postgres database."""
    return psycopg2.connect(
        dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
        host=os.environ["PGHOST"],
        port=os.environ.get("PGPORT", 5432),
        sslmode="require"  # required by Railway
    )


def create_tables():
    """Read schema.sql from the same folder and run it against the database."""
    script_dir = os.path.dirname(os.path.abspath(__file__))
    sql_path   = os.path.join(script_dir, "schema.sql")

    with open(sql_path, "r") as f:
        schema_sql = f.read()

    conn = get_connection()
    cur  = conn.cursor()
    try:
        cur.execute(schema_sql)
        conn.commit()
        print("✓ Tables created successfully.")
    except Exception as e:
        conn.rollback()
        print(f"✗ Error creating tables: {e}")
        raise
    finally:
        cur.close()
        conn.close()


if __name__ == "__main__":
    create_tables()

Expected folder layout: schema.sql and create_tables.py side by side, plus your .env file.

Adding Columns Without Breaking Anything

Your app is live. Users are in the database. You need to add a column. Here is the only safe way to do it.

The Golden Rule: `ADD COLUMN IF NOT EXISTS`

PostgreSQL's ADD COLUMN IF NOT EXISTS is idempotent — it only adds the column if it doesn't already exist, and does nothing (without error) if it does. This makes your migration scripts safe to run multiple times without side effects.

No downtime required for adding a nullable column
Existing rows get NULL (or your DEFAULT) for the new column
Safe to re-run — running it twice does nothing on the second pass
Never use DROP COLUMN on a live database without a backup
Never use ALTER COLUMN TYPE without explicit casting — it rewrites the table

`migrate.py` — run it from your machine, same as the creation script

Same connection setup as create_tables.py. Each migration is a labeled SQL string — append new ones at the bottom as your schema evolves. Safe to re-run at any time: ADD COLUMN IF NOT EXISTS does nothing if the column already exists.

import os
import psycopg2
from dotenv import load_dotenv

load_dotenv()


def get_connection():
    """Create a connection to your Railway Postgres database."""
    return psycopg2.connect(
        dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
        host=os.environ["PGHOST"],
        port=os.environ.get("PGPORT", 5432),
        sslmode="require"
    )


def run_migration(description, sql):
    """
    Execute a single migration statement against the database.
    Each call opens and closes its own connection so a failed
    migration never blocks the ones that follow.
    """
    conn = get_connection()
    cur  = conn.cursor()
    try:
        cur.execute(sql)
        conn.commit()
        print(f"  ✓ {description}")
    except Exception as e:
        conn.rollback()
        print(f"  ✗ {description} — FAILED: {e}")
        raise
    finally:
        cur.close()
        conn.close()


if __name__ == "__main__":
    print("Running migrations...")

    # ── Append new migrations here. Safe to re-run — IF NOT EXISTS is idempotent.

    run_migration(
        "Add tags column to clients",
        "ALTER TABLE clients ADD COLUMN IF NOT EXISTS tags TEXT[];"
    )
    run_migration(
        "Add referred_by column to clients",
        "ALTER TABLE clients ADD COLUMN IF NOT EXISTS referred_by VARCHAR(255);"
    )
    run_migration(
        "Add github_url column to projects",
        "ALTER TABLE projects ADD COLUMN IF NOT EXISTS github_url VARCHAR(255);"
    )
    run_migration(
        "Add is_warranty_job flag to jobs",
        "ALTER TABLE jobs ADD COLUMN IF NOT EXISTS is_warranty_job BOOLEAN DEFAULT FALSE;"
    )

    print("Done.")

Build the Rest of Your Stack

Schema done. Now scaffold the Flask app and generate your first prompts.

Project Generator

Scaffold a full Flask + psycopg2 project in seconds

Prompt Generator

Turn your schema into precise AI prompts for Flask routes

Code Verifier

Audit AI-generated code for SQL injection and security flaws

Join the Movement

Get tutorials, tools, and insights on building with Flask + PostgreSQL + Vanilla JS.

No spam. Unsubscribe anytime. We respect your inbox.