Backend Systems Protocols

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:

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:

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:

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