Backend Systems Protocols Skill

Overview

Database design conventions, API contract definitions, authentication patterns, and error handling standards for backend development. This skill provides the domain knowledge for building consistent, well-structured backend systems.

Type

standards / domain-knowledge

When to Use

Trigger this skill when:

• Designing database schemas
• Defining API contracts and endpoints
• Implementing authentication/authorization
• Standardizing error handling
• Setting up backend conventions for a project

Keywords: database, schema, API, REST, GraphQL, contract, authentication, auth, JWT, session, error handling, backend, server, endpoints

Database Conventions

ID Strategy

-- Decision: UUID for all primary keys
-- Rationale: Frontend-safe generation, no sequential exposure, merge-friendly
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Example
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  -- ...
);

Alternatives and when to use:

Strategy	Use When
UUID	Default choice, distributed systems, frontend ID generation
ULID	Need sortable IDs with timestamp component
Auto-increment	Internal-only tables, legacy compatibility

Timestamp Conventions

-- Always include created_at and updated_at
-- Always use timestamptz (timezone-aware)
-- Auto-update via trigger

CREATE TABLE example (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  -- ... other columns ...
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-update trigger function (create once)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to each table
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON example
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Soft Delete Pattern

-- Add deleted_at column for soft delete
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) NOT NULL,
  -- ...
  deleted_at TIMESTAMPTZ,  -- NULL = active, timestamp = deleted
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial unique index (only active records)
CREATE UNIQUE INDEX users_email_unique
  ON users (email)
  WHERE deleted_at IS NULL;

-- Default query scope
-- SELECT * FROM users WHERE deleted_at IS NULL;

When to use:

Approach	Use Case
Soft delete	User data, audit requirements, undo capability
Hard delete	Logs, sessions, temporary data

Naming Conventions

-- Tables: plural, snake_case
CREATE TABLE user_profiles (...);
CREATE TABLE order_items (...);

-- Columns: snake_case
user_id, created_at, is_active

-- Foreign keys: singular_table_id
user_id REFERENCES users(id)
order_id REFERENCES orders(id)

-- Indexes: table_column_idx
CREATE INDEX users_email_idx ON users(email);

-- Constraints: table_column_type
CONSTRAINT users_email_unique UNIQUE (email)
CONSTRAINT orders_total_positive CHECK (total >= 0)

API Contract Definitions

Response Wrapper Pattern

// Standard success response
interface ApiResponse<T> {
  success: true;
  data: T;
  meta?: {
    requestId: string;
    timestamp: string;
  };
}

// Standard error response
interface ApiError {
  success: false;
  error: {
    code: string;       // Machine-readable: "USER_NOT_FOUND"
    message: string;    // Human-readable: "User not found"
    details?: Record<string, string[]>;  // Field-level validation errors
  };
  meta?: {
    requestId: string;
    timestamp: string;
  };
}

// Union type for handlers
type ApiResult<T> = ApiResponse<T> | ApiError;

Pagination Pattern

interface PaginatedResponse<T> {
  items: T[];
  pagination: {
    page: number;
    pageSize: number;
    totalItems: number;
    totalPages: number;
    hasNextPage: boolean;
    hasPreviousPage: boolean;
  };
}

// Request params
interface PaginationParams {
  page?: number;      // Default: 1
  pageSize?: number;  // Default: 20, Max: 100
  sortBy?: string;
  sortOrder?: 'asc' | 'desc';
}

// Usage: GET /api/users?page=2&pageSize=20&sortBy=createdAt&sortOrder=desc

Resource Representation

// Database model (internal)
interface UserRecord {
  id: string;
  email: string;
  password_hash: string;  // Never exposed
  display_name: string;
  avatar_url: string | null;
  created_at: Date;
  updated_at: Date;
  deleted_at: Date | null;
}

// API representation (external)
interface User {
  id: string;
  email: string;
  displayName: string;      // camelCase transformation
  avatarUrl: string | null;
  createdAt: string;        // ISO 8601 string
  updatedAt: string;
}

// Create request (what client sends)
interface UserCreateRequest {
  email: string;
  password: string;
  displayName: string;
}

// Update request (partial)
interface UserUpdateRequest {
  displayName?: string;
  avatarUrl?: string | null;
}

Naming Transformation

Database (snake_case) → API (camelCase)

user_id      → userId
created_at   → createdAt
display_name → displayName
is_active    → isActive

Transform at the boundary - database layer uses snake_case, API layer uses camelCase.

Error Handling

Standard Error Codes

const ERROR_CODES = {
  // Validation (400)
  VALIDATION_ERROR: 'VALIDATION_ERROR',
  INVALID_INPUT: 'INVALID_INPUT',
  MISSING_FIELD: 'MISSING_FIELD',

  // Authentication (401)
  UNAUTHORIZED: 'UNAUTHORIZED',
  INVALID_CREDENTIALS: 'INVALID_CREDENTIALS',
  TOKEN_EXPIRED: 'TOKEN_EXPIRED',
  TOKEN_INVALID: 'TOKEN_INVALID',

  // Authorization (403)
  FORBIDDEN: 'FORBIDDEN',
  INSUFFICIENT_PERMISSIONS: 'INSUFFICIENT_PERMISSIONS',

  // Resources (404, 409)
  NOT_FOUND: 'NOT_FOUND',
  ALREADY_EXISTS: 'ALREADY_EXISTS',
  CONFLICT: 'CONFLICT',

  // Rate limiting (429)
  RATE_LIMITED: 'RATE_LIMITED',

  // Server (500, 503)
  INTERNAL_ERROR: 'INTERNAL_ERROR',
  SERVICE_UNAVAILABLE: 'SERVICE_UNAVAILABLE',
  DATABASE_ERROR: 'DATABASE_ERROR',
} as const;

// HTTP status mapping
const ERROR_STATUS: Record<string, number> = {
  VALIDATION_ERROR: 400,
  INVALID_INPUT: 400,
  MISSING_FIELD: 400,
  UNAUTHORIZED: 401,
  INVALID_CREDENTIALS: 401,
  TOKEN_EXPIRED: 401,
  TOKEN_INVALID: 401,
  FORBIDDEN: 403,
  INSUFFICIENT_PERMISSIONS: 403,
  NOT_FOUND: 404,
  ALREADY_EXISTS: 409,
  CONFLICT: 409,
  RATE_LIMITED: 429,
  INTERNAL_ERROR: 500,
  SERVICE_UNAVAILABLE: 503,
  DATABASE_ERROR: 500,
};

Validation Error Format

// Field-level validation errors
{
  success: false,
  error: {
    code: "VALIDATION_ERROR",
    message: "Validation failed",
    details: {
      email: ["Invalid email format", "Email already exists"],
      password: ["Must be at least 8 characters"]
    }
  }
}

Authentication Patterns

JWT in HttpOnly Cookies (Recommended)

┌─────────────────────────────────────────────────────────────┐
│                    AUTH FLOW                                 │
├─────────────────────────────────────────────────────────────┤
│ Access token: 15 min expiry, in httpOnly cookie             │
│ Refresh token: 7 day expiry, rotates on use                 │
│ CSRF: Double-submit cookie pattern                          │
├─────────────────────────────────────────────────────────────┤
│ Endpoints:                                                   │
│ POST /api/auth/login                                         │
│   → Sets access_token and refresh_token cookies             │
│   → Returns user object                                      │
│                                                              │
│ POST /api/auth/logout                                        │
│   → Clears all auth cookies                                  │
│   → Invalidates refresh token in DB                         │
│                                                              │
│ POST /api/auth/refresh                                       │
│   → Validates refresh token                                  │
│   → Issues new access + refresh tokens                       │
│   → Rotates refresh token (old one invalidated)             │
│                                                              │
│ GET /api/auth/me                                             │
│   → Returns current user or 401                             │
└─────────────────────────────────────────────────────────────┘

Cookie Configuration

// Access token cookie
{
  name: 'access_token',
  httpOnly: true,
  secure: true,           // HTTPS only
  sameSite: 'strict',     // or 'lax' for OAuth redirects
  path: '/',
  maxAge: 15 * 60,        // 15 minutes
}

// Refresh token cookie
{
  name: 'refresh_token',
  httpOnly: true,
  secure: true,
  sameSite: 'strict',
  path: '/api/auth',      // Only sent to auth endpoints
  maxAge: 7 * 24 * 60 * 60, // 7 days
}

CSRF Protection

// Double-submit cookie pattern
// 1. Server sets CSRF token in non-httpOnly cookie
// 2. Client reads cookie, sends in X-CSRF-Token header
// 3. Server validates header matches cookie

// Set on login
res.cookie('csrf_token', generateCsrfToken(), {
  httpOnly: false,  // JS must read this
  secure: true,
  sameSite: 'strict',
});

// Validate on state-changing requests
const headerToken = req.headers['x-csrf-token'];
const cookieToken = req.cookies.csrf_token;
if (headerToken !== cookieToken) {
  throw new ForbiddenError('CSRF validation failed');
}

State Handling Matrix

Define how frontend should handle each state:

State	Backend Response	HTTP Status	Frontend Behavior
Loading	N/A (pending)	-	Show skeleton/spinner
Success (data)	{ success: true, data: [...] }	200	Render data
Success (empty)	{ success: true, data: [] }	200	Show empty state
Created	{ success: true, data: {...} }	201	Show success, redirect
No Content	-	204	Silent success
Bad Request	{ success: false, error: {...} }	400	Show validation errors
Unauthorized	{ success: false, error: {...} }	401	Redirect to login
Forbidden	{ success: false, error: {...} }	403	Show permission error
Not Found	{ success: false, error: {...} }	404	Show not found state
Server Error	{ success: false, error: {...} }	500	Show error + retry
Offline	No response	-	Show cached + banner

Endpoint Design

RESTful Conventions

Resources are nouns, actions are HTTP methods

GET    /api/users          → List users
POST   /api/users          → Create user
GET    /api/users/:id      → Get single user
PATCH  /api/users/:id      → Partial update
PUT    /api/users/:id      → Full replace (rare)
DELETE /api/users/:id      → Delete user

Nested resources
GET    /api/users/:id/posts     → User's posts
POST   /api/users/:id/posts     → Create post for user

Actions (when REST doesn't fit)
POST   /api/users/:id/activate  → Custom action
POST   /api/auth/login          → Authentication
POST   /api/payments/process    → Complex operation

Request/Response Examples

// GET /api/users/:id
// Response: 200 OK
{
  success: true,
  data: {
    id: "550e8400-e29b-41d4-a716-446655440000",
    email: "user@example.com",
    displayName: "John Doe",
    avatarUrl: null,
    createdAt: "2024-01-15T10:30:00.000Z",
    updatedAt: "2024-01-15T10:30:00.000Z"
  }
}

// POST /api/users
// Request body:
{
  email: "user@example.com",
  password: "securepassword123",
  displayName: "John Doe"
}
// Response: 201 Created
{
  success: true,
  data: {
    id: "550e8400-e29b-41d4-a716-446655440000",
    email: "user@example.com",
    displayName: "John Doe",
    avatarUrl: null,
    createdAt: "2024-01-15T10:30:00.000Z",
    updatedAt: "2024-01-15T10:30:00.000Z"
  }
}

// PATCH /api/users/:id
// Request body:
{
  displayName: "Jane Doe"
}
// Response: 200 OK
{
  success: true,
  data: { /* updated user */ }
}

// DELETE /api/users/:id
// Response: 204 No Content

Pre-Implementation Checklist

• Database naming conventions documented
• ID strategy decided (UUID recommended)
• Timestamp handling specified
• Soft delete vs hard delete decided per entity
• API response wrapper types defined
• Error codes enumerated
• Pagination pattern specified
• Authentication flow documented
• CSRF protection planned

Anti-Patterns

NEVER do these:

• Expose database column names directly in API (snake_case leak)
• Return password hashes or other secrets
• Use auto-increment IDs in public APIs
• Inconsistent error response shapes
• Store JWT in localStorage (XSS vulnerable)
• Skip CSRF protection on state-changing requests
• Return 200 OK for errors (use proper status codes)
• Inconsistent timestamp formats (always ISO 8601)

---

Standalone skill for backend system protocols