REST API Starter

Category: Backend & APIs · ⭐ Featured · 📦 1 install

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

A production-shaped CRUD API over Postgres with three resource types (users, posts, comments) and optional JWT validation. No external services required — just a Postgres database. Deploy and start making requests in under 10 minutes.

---

What's included

File	Purpose
schema.sql	Postgres tables and indexes
users.yml	User CRUD endpoints
posts.yml	Post CRUD endpoints with author join
comments.yml	Comment endpoints
auth.yml	JWT validation pattern (optional)
seed.yml	Creates all tables and loads sample data

---

Endpoints

Users

Method	Path	Body	Description
GET	/api/users	—	List all users
POST	/api/users/create	{email, username, password}	Create user
POST	/api/users/get	{id}	Get user by ID
PUT	/api/users/update	{id, email?, username?}	Update user
DELETE	/api/users/delete	{id}	Delete user

Posts

Method	Path	Body	Description
GET	/api/posts	—	List published posts
GET	/api/posts/all	—	List all posts
POST	/api/posts/create	{user_id, title, body?, status?}	Create post
POST	/api/posts/get	{id}	Get post with comment count
PUT	/api/posts/update	{id, title?, body?, status?}	Update post
DELETE	/api/posts/delete	{id}	Delete post

Comments

Method	Path	Body	Description
POST	/api/comments/list	{post_id}	List comments for a post
POST	/api/comments/create	{post_id, user_id, body}	Add a comment
DELETE	/api/comments/delete	{id}	Delete a comment

Auth (optional)

Method	Path	Body	Description
POST	/api/me	{token, ...}	JWT-protected current-user fetch

Seed

Method	Path	Body	Description
POST	/api/seed	—	Create tables and load sample data

---

Setup

1. Create the database tables

Either run the schema directly:

psql $DATABASE_URL -f schema.sql

Or deploy seed.yml alongside the other configs and call the seed endpoint — it creates all tables and loads sample users, posts, and comments in one step:

curl -X POST https://your-airpipe-host/api/seed

2. Set the managed variable

In the AirPipe dashboard, add a managed variable:

Name	Value
DATABASE_URL	postgresql://user:pass@host:5432/dbname

3. Deploy the configs

Point AirPipe at the config files. In hosted mode, upload via the dashboard or CLI. All files can be deployed together — each name: is unique so they won't conflict.

---

Quick start: curl walkthrough

BASE=https://your-airpipe-host

# Create a user (password is bcrypt-hashed before storage)
curl -X POST $BASE/api/users/create \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "username": "alice", "password": "Secret1!"}'
# → { "id": 1, "email": "[email protected]", "username": "alice", ... }

# Create a post
curl -X POST $BASE/api/posts/create \
  -H "Content-Type: application/json" \
  -d '{"user_id": 1, "title": "Hello World", "body": "My first post.", "status": "published"}'
# → { "id": 1, "title": "Hello World", "status": "published", ... }

# Add a comment
curl -X POST $BASE/api/comments/create \
  -H "Content-Type: application/json" \
  -d '{"post_id": 1, "user_id": 1, "body": "Great post!"}'

# List published posts
curl $BASE/api/posts

---

JWT validation (optional)

AirPipe validates JWTs but does not generate them. To add auth to an endpoint:

Generate a test token at jwt.io:

• Algorithm: HS256
• Payload: {"sub": "1", "username": "alice", "exp": 9999999999}
• Secret: set to the value of JWT_SECRET

Add a managed variable in the AirPipe dashboard: JWT_SECRET = your 32+ character secret.

Deploy auth.yml alongside the other configs.

Test the protected /api/me endpoint:

curl -X POST $BASE/api/me \
  -H "Content-Type: application/json" \
  -d '{"token": "<your-jwt-here>"}'

To protect any other endpoint, copy the ValidateToken action from auth.yml and add it as the first action in the interface, then make all subsequent actions depend on it:

actions:
  - name: ValidateToken
    input: a|body|
    hide_data_on_success: true
    assert:
      http_code_on_error: 401
      tests:
        - value: token
          is_not_null: true
          is_valid_jwt: a|ap_var::JWT_SECRET|
    post_transforms:
      - extract_value: jwt_claims

  - name: YourAction
    run_when_succeeded:
      actions: [ValidateToken]
      http_code_on_error: 401
    database: main
    query: ...

---

Customisation

Add a field to users

1. Add the column to schema.sql and run the migration
2. Add the field to the SELECT in ListUsers and GetUser
3. Add a validation assertion in CreateUser's ValidateBody
4. Add $N to the INSERT query and the matching params entry

Change the post status values

The status column has a CHECK constraint in schema.sql. Update the constraint and the COALESCE($4, 'draft') default in posts.yml to match your values.

Use a different database

Change driver: postgres to driver: mysql, driver: sqlite, or driver: mongodb and update the conn_string and queries accordingly.

---

Notes

• Password hashing: users/create runs a bcrypt post-transform (cost 12) before the INSERT. The raw password is never stored. The HashPassword action uses hide_data_on_success and hide_data_on_error so the hash is never surfaced in responses.
• Pagination: the list endpoints use LIMIT 100. Add OFFSET support by passing a page field in the body and using LIMIT 100 OFFSET ($1 * 100).

Configuration

auth.yml

name: RestApiAuth

docs: true

# JWT validation middleware pattern.
#
# AirPipe validates JWTs but does not generate them. To get a token for testing:
#   1. Go to https://jwt.io
#   2. Set algorithm to HS256
#   3. Set payload: { "sub": "1", "username": "alice", "exp": <future timestamp> }
#   4. Set "your-256-bit-secret" to match JWT_SECRET below
#   5. Copy the encoded token
#
# Then pass the token as { "token": "<jwt>" } alongside your request data.
#
# Required environment variable:
#   JWT_SECRET — 32+ character secret used to sign tokens

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /api/me
  # Example of a JWT-protected endpoint.
  # Body: { "token": "<jwt>", "id": 1 }
  #
  # To protect any endpoint: copy the ValidateToken action and add
  # run_when_succeeded: { actions: [ValidateToken], http_code_on_error: 401 }
  # to every subsequent action.
  api/me:
    output: http
    method: POST
    summary: Get current user
    description: >
      JWT-protected endpoint. Validates the token and returns the authenticated user's profile.
      Pass the token in the request body alongside any other fields.
    tags: [auth]
    request_example:
      token: "<your-jwt-here>"
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateToken
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 401
          error_message: "Invalid or missing token"
          tests:
            - value: token
              is_not_null: true
              is_valid_jwt: a|ap_var::JWT_SECRET|
        post_transforms:
          - extract_value: jwt_claims

      - name: GetCurrentUser
        run_when_succeeded:
          actions: [ValidateToken]
          http_code_on_error: 401
        database: main
        query: |
          SELECT id, email, username, created_at
          FROM users
          WHERE id = $1;
        params:
          - a|ValidateToken::sub|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

comments.yml

name: RestApiComments

docs: true

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /api/comments/list
  # List comments for a post.
  # Body: { "post_id": 1 }
  api/comments/list:
    output: http
    method: POST
    summary: List comments
    description: List all comments for a post, oldest first.
    tags: [comments]
    request_example:
      post_id: 1
    response_example:
      - id: 1
        body: "Great post!"
        created_at: "2024-01-01T13:00:00Z"
        author: bob

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: post_id
              is_not_null: true

      - name: ListComments
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT c.id, c.body, c.created_at, u.username AS author
          FROM comments c
          JOIN users u ON u.id = c.user_id
          WHERE c.post_id = $1
          ORDER BY c.created_at ASC;
        params:
          - a|ValidateBody::post_id|

  # POST /api/comments/create
  # Add a comment to a post.
  # Body: { "post_id": 1, "user_id": 2, "body": "Great post!" }
  api/comments/create:
    output: http
    method: POST
    summary: Create comment
    description: Add a comment to a post.
    tags: [comments]
    notes: |
      `post_id` and `user_id` must reference existing records. Run `POST /api/seed` first to populate sample data, or use ids returned from the create endpoints.
    request_example:
      post_id: 1
      user_id: 2
      body: "Great post!"
    response_example:
      id: 1
      post_id: 1
      user_id: 2
      body: "Great post!"
      created_at: "2024-01-01T13:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: post_id
              is_not_null: true
            - value: user_id
              is_not_null: true
            - value: body
              is_not_null: true
              is_not_empty: true

      - name: CreateComment
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO comments (post_id, user_id, body)
          VALUES ($1, $2, $3)
          RETURNING id, post_id, user_id, body, created_at;
        params:
          - a|ValidateBody::post_id|
          - a|ValidateBody::user_id|
          - a|ValidateBody::body|
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/comments/delete
  # Delete a comment by ID.
  # Body: { "id": 1 }
  api/comments/delete:
    output: http
    method: DELETE
    summary: Delete comment
    description: Permanently delete a comment by ID.
    tags: [comments]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeleteComment
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM comments WHERE id = $1 RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Comment not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

posts.yml

name: RestApiPosts

docs: true

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /api/posts
  # List all published posts.
  api/posts:
    output: http
    summary: List published posts
    description: Returns the 100 most recent published posts with author username.
    tags: [posts]
    response_example:
      - id: 1
        title: Hello World
        status: published
        created_at: "2024-01-01T12:00:00Z"
        author: alice

    actions:
      - name: ListPosts
        database: main
        query: |
          SELECT p.id, p.title, p.status, p.created_at, u.username AS author
          FROM posts p
          JOIN users u ON u.id = p.user_id
          WHERE p.status = 'published'
          ORDER BY p.created_at DESC
          LIMIT 100;

  # GET /api/posts/all
  # List all posts regardless of status (admin view).
  api/posts/all:
    output: http
    summary: List all posts
    description: Returns all posts regardless of status. Useful for admin views.
    tags: [posts]
    response_example:
      - id: 1
        title: Hello World
        status: published
        created_at: "2024-01-01T12:00:00Z"
        author: alice
      - id: 2
        title: Draft post
        status: draft
        created_at: "2024-01-02T08:00:00Z"
        author: bob

    actions:
      - name: ListAllPosts
        database: main
        query: |
          SELECT p.id, p.title, p.status, p.created_at, u.username AS author
          FROM posts p
          JOIN users u ON u.id = p.user_id
          ORDER BY p.created_at DESC
          LIMIT 100;

  # POST /api/posts/create
  # Create a new post.
  # Body: { "user_id": 1, "title": "Hello World", "body": "...", "status": "draft" }
  api/posts/create:
    output: http
    method: POST
    summary: Create post
    description: Create a new post. Status defaults to draft if not provided.
    tags: [posts]
    notes: |
      `user_id` must reference an existing user. Run `POST /api/seed` first to populate sample users (ids 1–3), or use the id returned from `POST /api/users/create`.
    request_example:
      user_id: 1
      title: Hello World
      body: "My first post."
      status: published
    response_example:
      id: 1
      user_id: 1
      title: Hello World
      status: published
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: user_id
              is_not_null: true
            - value: title
              is_not_null: true
              is_not_empty: true

      - name: CreatePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO posts (user_id, title, body, status)
          VALUES ($1, $2, $3, COALESCE($4, 'draft'))
          RETURNING id, user_id, title, status, created_at;
        params:
          - a|ValidateBody::user_id|
          - a|ValidateBody::title|
          - a|ValidateBody::body->default(null)|
          - a|ValidateBody::status->default(null)|
        post_transforms:
          - extract_value: "[0]"

  # POST /api/posts/get
  # Fetch a single post with its comment count.
  # Body: { "id": 1 }
  api/posts/get:
    output: http
    method: POST
    summary: Get post
    description: Fetch a single post with its author and comment count.
    tags: [posts]
    request_example:
      id: 1
    response_example:
      id: 1
      title: Hello World
      body: "My first post."
      status: published
      created_at: "2024-01-01T12:00:00Z"
      updated_at: "2024-01-01T12:00:00Z"
      author: alice
      comment_count: 3

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: GetPost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT
            p.id, p.title, p.body, p.status, p.created_at, p.updated_at,
            u.username AS author,
            COUNT(c.id) AS comment_count
          FROM posts p
          JOIN users u ON u.id = p.user_id
          LEFT JOIN comments c ON c.post_id = p.id
          WHERE p.id = $1
          GROUP BY p.id, u.username;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/posts/update
  # Update a post's title, body, or status.
  # Body: { "id": 1, "title": "Updated title", "status": "published" }
  api/posts/update:
    output: http
    method: PUT
    summary: Update post
    description: Update a post's title, body, or status. Omit a field to leave it unchanged.
    tags: [posts]
    request_example:
      id: 1
      title: Updated title
      status: published
    response_example:
      id: 1
      title: Updated title
      status: published
      updated_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: UpdatePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          UPDATE posts
          SET
            title      = COALESCE($2, title),
            body       = COALESCE($3, body),
            status     = COALESCE($4, status),
            updated_at = NOW()
          WHERE id = $1
          RETURNING id, title, status, updated_at;
        params:
          - a|ValidateBody::id|
          - a|ValidateBody::title->default(null)|
          - a|ValidateBody::body->default(null)|
          - a|ValidateBody::status->default(null)|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/posts/delete
  # Delete a post (cascades to comments).
  # Body: { "id": 1 }
  api/posts/delete:
    output: http
    method: DELETE
    summary: Delete post
    description: Permanently delete a post. Cascades to all its comments.
    tags: [posts]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeletePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM posts WHERE id = $1 RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

seed.yml

name: RestApiStarterSeed
description: Creates all tables (idempotent) and loads sample users, posts, and comments. Safe to re-run — truncates before inserting.

docs: true

# Required managed variables:
#   DATABASE_URL — Postgres connection string

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /api/seed
  # Creates all required tables and indexes then loads sample data.
  # Safe to re-run — truncates all tables before inserting.
  api/seed:
    output: http
    method: POST

    actions:
      - name: CreateUsersTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS users (
            id            BIGSERIAL    PRIMARY KEY,
            email         TEXT         UNIQUE NOT NULL,
            username      TEXT         UNIQUE NOT NULL,
            password_hash TEXT         NOT NULL,
            created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
            updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

      - name: CreatePostsTable
        run_when_succeeded: [CreateUsersTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS posts (
            id         BIGSERIAL    PRIMARY KEY,
            user_id    BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            title      TEXT         NOT NULL,
            body       TEXT,
            status     TEXT         NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

      - name: CreateCommentsTable
        run_when_succeeded: [CreatePostsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS comments (
            id         BIGSERIAL    PRIMARY KEY,
            post_id    BIGINT       NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
            user_id    BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            body       TEXT         NOT NULL,
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

      - name: CreateIndexes
        run_when_succeeded: [CreateCommentsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE INDEX IF NOT EXISTS idx_posts_user_id    ON posts    (user_id);
          CREATE INDEX IF NOT EXISTS idx_posts_status     ON posts    (status);
          CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments (post_id);

      - name: TruncateTables
        run_when_succeeded: [CreateIndexes]
        database: main
        hide_data_on_success: true
        query: |
          TRUNCATE comments, posts, users RESTART IDENTITY CASCADE;

      - name: InsertUsers
        run_when_succeeded: [TruncateTables]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO users (email, username, password_hash) VALUES
            ('[email protected]', 'alice', 'hashed_pw_1'),
            ('[email protected]',   'bob',   'hashed_pw_2'),
            ('[email protected]', 'carol', 'hashed_pw_3');

      - name: InsertPosts
        run_when_succeeded: [InsertUsers]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO posts (user_id, title, body, status)
          SELECT u.id, v.title, v.body, v.status
          FROM (VALUES
            ('alice', 'Getting Started with AirPipe', 'AirPipe lets you build APIs from config files.', 'published'),
            ('alice', 'Draft: Advanced Patterns',     'Work in progress.',                              'draft'),
            ('bob',   'My First Post',                'Hello from Bob!',                               'published'),
            ('bob',   'REST APIs Made Easy',          'CRUD without the boilerplate.',                 'published'),
            ('carol', 'Archived Post',                'This one is no longer relevant.',               'archived')
          ) AS v(username, title, body, status)
          JOIN users u ON u.username = v.username;

      - name: InsertComments
        run_when_succeeded: [InsertPosts]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO comments (post_id, user_id, body)
          SELECT p.id, u.id, v.body
          FROM (VALUES
            ('Getting Started with AirPipe', 'bob',   'Really helpful, thanks Alice!'),
            ('Getting Started with AirPipe', 'carol', 'Bookmarked this.'),
            ('My First Post',                'alice', 'Welcome Bob!'),
            ('REST APIs Made Easy',          'carol', 'This saved me hours.'),
            ('REST APIs Made Easy',          'alice', 'Great write-up.')
          ) AS v(post_title, username, body)
          JOIN posts    p ON p.title    = v.post_title
          JOIN users    u ON u.username = v.username;

      - name: SeedSummary
        run_when_succeeded: [InsertComments]
        database: main
        query: |
          SELECT
            (SELECT COUNT(*) FROM users)    AS users,
            (SELECT COUNT(*) FROM posts)    AS posts,
            (SELECT COUNT(*) FROM comments) AS comments;
        post_transforms:
          - extract_value: "[0]"

users.yml

name: RestApiUsers

docs: true

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /api/users
  # List all users (paginated — extend with query params as needed).
  api/users:
    output: http
    summary: List users
    description: Returns the 100 most recently created users.
    tags: [users]
    response_example:
      - id: 1
        email: [email protected]
        username: alice
        created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ListUsers
        database: main
        query: |
          SELECT id, email, username, created_at
          FROM users
          ORDER BY created_at DESC
          LIMIT 100;

  # POST /api/users/create
  # Create a new user.
  # Body: { "email": "...", "username": "...", "password": "..." }
  api/users/create:
    output: http
    method: POST
    summary: Create user
    description: >
      Create a new user. Password is bcrypt-hashed (cost 12) before storage —
      the raw value is never persisted or returned.
    tags: [users]
    request_example:
      email: [email protected]
      username: jane
      password: "Secret1!"
    response_example:
      id: 6
      email: [email protected]
      username: jane
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: email
              is_not_null: true
              is_not_empty: true
            - value: username
              is_not_null: true
              is_not_empty: true
            - value: password
              is_not_null: true
              is_not_empty: true

      - name: HashPassword
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        input: a|ValidateBody|
        hide_data_on_success: true
        hide_data_on_error: true
        post_transforms:
          - bcrypt:
              value: password
              cost: 12

      - name: CreateUser
        run_when_succeeded:
          actions: [HashPassword]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO users (email, username, password_hash)
          VALUES ($1, $2, $3)
          RETURNING id, email, username, created_at;
        params:
          - a|ValidateBody::email|
          - a|ValidateBody::username|
          - a|HashPassword::password|
        post_transforms:
          - extract_value: "[0]"

  # POST /api/users/get
  # Fetch a single user by ID.
  # Body: { "id": 1 }
  api/users/get:
    output: http
    method: POST
    summary: Get user
    description: Fetch a single user by ID.
    tags: [users]
    request_example:
      id: 1
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2024-01-01T12:00:00Z"
      updated_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: GetUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT id, email, username, created_at, updated_at
          FROM users
          WHERE id = $1;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/users/update
  # Update email and/or username for a user.
  # Body: { "id": 1, "email": "[email protected]", "username": "newname" }
  # Omit a field to leave it unchanged.
  api/users/update:
    output: http
    method: PUT
    summary: Update user
    description: Update email and/or username. Omit a field to leave it unchanged.
    tags: [users]
    request_example:
      id: 1
      email: [email protected]
      username: newname
    response_example:
      id: 1
      email: [email protected]
      username: newname
      updated_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: UpdateUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          UPDATE users
          SET
            email      = COALESCE($2, email),
            username   = COALESCE($3, username),
            updated_at = NOW()
          WHERE id = $1
          RETURNING id, email, username, updated_at;
        params:
          - a|ValidateBody::id|
          - a|ValidateBody::email->default(null)|
          - a|ValidateBody::username->default(null)|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/users/delete
  # Delete a user by ID.
  # Body: { "id": 1 }
  api/users/delete:
    output: http
    method: DELETE
    summary: Delete user
    description: Permanently delete a user. Cascades to their posts and comments.
    tags: [users]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeleteUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM users
          WHERE id = $1
          RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

schema.sql

-- REST API Starter schema
-- Run this once against your database before deploying the configs.

CREATE TABLE IF NOT EXISTS users (
  id            BIGSERIAL    PRIMARY KEY,
  email         TEXT         UNIQUE NOT NULL,
  username      TEXT         UNIQUE NOT NULL,
  password_hash TEXT         NOT NULL,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS posts (
  id         BIGSERIAL    PRIMARY KEY,
  user_id    BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT         NOT NULL,
  body       TEXT,
  status     TEXT         NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS comments (
  id         BIGSERIAL    PRIMARY KEY,
  post_id    BIGINT       NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id    BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body       TEXT         NOT NULL,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_posts_user_id    ON posts    (user_id);
CREATE INDEX IF NOT EXISTS idx_posts_status     ON posts    (status);
CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments (post_id);