03 - Backend and Frontend Architecture with Copilot

Designing Architecture with Copilot

Good architecture is the foundation of a sustainable project. In this phase, we'll use Copilot to design both backend and frontend, defining how they communicate through a clear API contract. Architecture isn't just "where to put files", but a set of decisions that influence scalability, testability, and maintainability of the project.

Series Overview

This is the third article of the series "GitHub Copilot: AI-Driven Personal Project".

Choosing Backend Architecture

For personal projects, there are different architectural patterns. The choice depends on domain complexity and team skills (you!).

Architectural Pattern Comparison

Backend: Layered Architecture

Design a backend architecture for my project.

PROJECT: TaskFlow (time tracking + task management)
STACK: Node.js + Express + TypeScript + PostgreSQL
TEAM SIZE: Solo developer
EXPECTED SCALE: 1000 users, 100k requests/day

Requirements:
- Clean separation between layers
- Easy to test each layer independently
- Scalable for future features
- Simple enough for a solo developer to maintain
- Support for both REST API and future GraphQL

Include:
1. Layer diagram with responsibilities
2. Complete folder structure with explanations
3. Data flow example for a typical request
4. Error handling strategy across layers
5. Dependency injection approach

Layer Diagram

+-------------------------------------------------------------+
|                    PRESENTATION LAYER                        |
|  +-----------+  +-----------+  +---------------------+       |
|  | Controllers|  |Middleware |  | DTOs (Validation)  |       |
|  +-----------+  +-----------+  +---------------------+       |
|  Responsibility: HTTP handling, request/response transform   |
+-------------------------------------------------------------+
|                     BUSINESS LAYER                           |
|  +-----------+  +-----------+  +---------------------+       |
|  |  Services |  | Use Cases |  |   Domain Logic     |       |
|  +-----------+  +-----------+  +---------------------+       |
|  Responsibility: Business rules, orchestration, validation   |
+-------------------------------------------------------------+
|                   DATA ACCESS LAYER                          |
|  +-----------+  +-----------+  +---------------------+       |
|  |Repositories|  | Entities |  |  Query Builders    |       |
|  +-----------+  +-----------+  +---------------------+       |
|  Responsibility: Data persistence, query optimization        |
+-------------------------------------------------------------+
|                    INFRASTRUCTURE                            |
|  +-----------+  +-----------+  +---------------------+       |
|  | Database  |  |External APIs| |  File System      |       |
|  +-----------+  +-----------+  +---------------------+       |
|  Responsibility: Concrete implementations, external deps     |
+-------------------------------------------------------------+

                    DEPENDENCY DIRECTION
          (Upper layers depend on lower layers)

Dependency Rules

Request Flow

Let's see how a request traverses all layers, from HTTP to persistence.

// =============================================================
// 1. ROUTE DEFINITION (Presentation Layer)
// =============================================================
// routes/task.routes.ts
import {{ '{' }} Router {{ '}' }} from 'express';
import {{ '{' }} TaskController {{ '}' }} from '../controllers/task.controller';
import {{ '{' }} authMiddleware {{ '}' }} from '../middleware/auth.middleware';
import {{ '{' }} validateDto {{ '}' }} from '../middleware/validation.middleware';
import {{ '{' }} CreateTaskDto {{ '}' }} from '../dto/create-task.dto';

const router = Router();
const controller = new TaskController();

router.post(
  '/',
  authMiddleware,                    // Verify JWT
  validateDto(CreateTaskDto),        // Validate body
  controller.create.bind(controller) // Handle request
);

export default router;

// =============================================================
// 2. DTO WITH VALIDATION (Presentation Layer)
// =============================================================
// dto/create-task.dto.ts
import {{ '{' }} IsString, IsOptional, IsDateString, MinLength, MaxLength {{ '}' }} from 'class-validator';

export class CreateTaskDto {{ '{' }}
  @IsString()
  @MinLength(3, {{ '{' }} message: 'Title must be at least 3 characters' {{ '}' }})
  @MaxLength(100)
  title: string;

  @IsOptional()
  @IsString()
  @MaxLength(1000)
  description?: string;

  @IsOptional()
  @IsDateString()
  dueDate?: string;
{{ '}' }}

// =============================================================
// 3. CONTROLLER (Presentation Layer)
// =============================================================
// controllers/task.controller.ts
import {{ '{' }} Request, Response, NextFunction {{ '}' }} from 'express';
import {{ '{' }} TaskService {{ '}' }} from '../services/task.service';
import {{ '{' }} CreateTaskDto {{ '}' }} from '../dto/create-task.dto';

export class TaskController {{ '{' }}
  private taskService: TaskService;

  constructor() {{ '{' }}
    this.taskService = new TaskService();
  {{ '}' }}

  async create(req: Request, res: Response, next: NextFunction) {{ '{' }}
    try {{ '{' }}
      const userId = req.user.id; // From auth middleware
      const dto: CreateTaskDto = req.body;

      const task = await this.taskService.createTask(userId, dto);

      res.status(201).json({{ '{' }}
        success: true,
        data: task
      {{ '}' }});
    {{ '}' }} catch (error) {{ '{' }}
      next(error); // Forward to error middleware
    {{ '}' }}
  {{ '}' }}
{{ '}' }}

// =============================================================
// 4. SERVICE (Business Layer)
// =============================================================
// services/task.service.ts
import {{ '{' }} TaskRepository {{ '}' }} from '../repositories/task.repository';
import {{ '{' }} Task {{ '}' }} from '../entities/task.entity';
import {{ '{' }} CreateTaskDto {{ '}' }} from '../dto/create-task.dto';
import {{ '{' }} ValidationError {{ '}' }} from '../errors/validation.error';

export class TaskService {{ '{' }}
  private taskRepo: TaskRepository;

  constructor() {{ '{' }}
    this.taskRepo = new TaskRepository();
  {{ '}' }}

  async createTask(userId: string, dto: CreateTaskDto): Promise<Task> {{ '{' }}
    // Business rule: due date must be in future
    if (dto.dueDate && new Date(dto.dueDate) < new Date()) {{ '{' }}
      throw new ValidationError('Due date must be in the future');
    {{ '}' }}

    // Business rule: max 100 active tasks per user
    const activeTasksCount = await this.taskRepo.countActiveByUser(userId);
    if (activeTasksCount >= 100) {{ '{' }}
      throw new ValidationError('Maximum 100 active tasks allowed');
    {{ '}' }}

    // Create entity
    const task = new Task({{ '{' }}
      userId,
      title: dto.title,
      description: dto.description,
      dueDate: dto.dueDate ? new Date(dto.dueDate) : null,
      status: 'TODO',
      createdAt: new Date()
    {{ '}' }});

    // Persist and return
    return this.taskRepo.save(task);
  {{ '}' }}
{{ '}' }}

// =============================================================
// 5. REPOSITORY (Data Access Layer)
// =============================================================
// repositories/task.repository.ts
import {{ '{' }} db {{ '}' }} from '../database/connection';
import {{ '{' }} Task {{ '}' }} from '../entities/task.entity';

export class TaskRepository {{ '{' }}
  async save(task: Task): Promise<Task> {{ '{' }}
    const result = await db.query(
      `INSERT INTO tasks (id, user_id, title, description, due_date, status, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING *`,
      [task.id, task.userId, task.title, task.description,
       task.dueDate, task.status, task.createdAt]
    );
    return Task.fromRow(result.rows[0]);
  {{ '}' }}

  async countActiveByUser(userId: string): Promise<number> {{ '{' }}
    const result = await db.query(
      `SELECT COUNT(*) FROM tasks
       WHERE user_id = $1 AND status != 'DONE' AND deleted_at IS NULL`,
      [userId]
    );
    return parseInt(result.rows[0].count);
  {{ '}' }}
{{ '}' }}

Consistent Error Handling

A well-designed error handling system simplifies debugging and UX.

// =============================================================
// errors/app-error.ts - Base Error Class
// =============================================================
export class AppError extends Error {{ '{' }}
  public readonly statusCode: number;
  public readonly code: string;
  public readonly isOperational: boolean;
  public readonly details?: unknown;

  constructor(
    message: string,
    statusCode: number = 500,
    code: string = 'INTERNAL_ERROR',
    details?: unknown
  ) {{ '{' }}
    super(message);
    this.statusCode = statusCode;
    this.code = code;
    this.isOperational = true;
    this.details = details;

    Error.captureStackTrace(this, this.constructor);
  {{ '}' }}
{{ '}' }}

// =============================================================
// errors/specific-errors.ts - Domain Specific Errors
// =============================================================
export class ValidationError extends AppError {{ '{' }}
  constructor(message: string, details?: unknown) {{ '{' }}
    super(message, 400, 'VALIDATION_ERROR', details);
  {{ '}' }}
{{ '}' }}

export class NotFoundError extends AppError {{ '{' }}
  constructor(resource: string, id?: string) {{ '{' }}
    const message = id
      ? `${{ '{' }}resource{{ '}' }} with id '${{ '{' }}id{{ '}' }}' not found`
      : `${{ '{' }}resource{{ '}' }} not found`;
    super(message, 404, 'NOT_FOUND');
  {{ '}' }}
{{ '}' }}

export class UnauthorizedError extends AppError {{ '{' }}
  constructor(message: string = 'Unauthorized') {{ '{' }}
    super(message, 401, 'UNAUTHORIZED');
  {{ '}' }}
{{ '}' }}

export class ForbiddenError extends AppError {{ '{' }}
  constructor(message: string = 'Forbidden') {{ '{' }}
    super(message, 403, 'FORBIDDEN');
  {{ '}' }}
{{ '}' }}

export class ConflictError extends AppError {{ '{' }}
  constructor(message: string) {{ '{' }}
    super(message, 409, 'CONFLICT');
  {{ '}' }}
{{ '}' }}

// =============================================================
// middleware/error.middleware.ts - Global Error Handler
// =============================================================
import {{ '{' }} Request, Response, NextFunction {{ '}' }} from 'express';
import {{ '{' }} AppError {{ '}' }} from '../errors/app-error';
import {{ '{' }} logger {{ '}' }} from '../utils/logger';

export function errorHandler(
  err: Error,
  req: Request,
  res: Response,
  next: NextFunction
) {{ '{' }}
  // Handle known operational errors
  if (err instanceof AppError) {{ '{' }}
    logger.warn({{ '{' }}
      code: err.code,
      message: err.message,
      path: req.path,
      method: req.method
    {{ '}' }});

    return res.status(err.statusCode).json({{ '{' }}
      success: false,
      error: {{ '{' }}
        code: err.code,
        message: err.message,
        ...(err.details && {{ '{' }} details: err.details {{ '}' }})
      {{ '}' }}
    {{ '}' }});
  {{ '}' }}

  // Handle unexpected errors
  logger.error({{ '{' }}
    message: err.message,
    stack: err.stack,
    path: req.path,
    method: req.method
  {{ '}' }});

  // Don't leak internal errors in production
  const message = process.env.NODE_ENV === 'production'
    ? 'An unexpected error occurred'
    : err.message;

  return res.status(500).json({{ '{' }}
    success: false,
    error: {{ '{' }}
      code: 'INTERNAL_ERROR',
      message
    {{ '}' }}
  {{ '}' }});
{{ '}' }}

Frontend Architecture: Feature-Based

For frontend, a feature-based structure scales better than traditional type-based structure (components, services, etc.).

Design a frontend architecture for my project.

PROJECT: TaskFlow (time tracking + task management)
STACK: Angular 21 with standalone components
TEAM SIZE: Solo developer

Requirements:
- Feature-based folder structure
- Lazy loading per route for performance
- State management strategy (signals vs NgRx)
- API communication layer with interceptors
- Reusable component library (design system)
- Offline support consideration

Include:
1. Detailed folder structure with explanations
2. State management approach
3. Data flow diagram
4. Component communication patterns
5. API service architecture

Frontend Structure Comparison

src/app/
|-- components/     # 50+ files
|   |-- user-list.ts
|   |-- user-form.ts
|   |-- task-list.ts
|   |-- task-item.ts
|   |-- ... (mixed)
|-- services/
|   |-- ... (all together)
|-- models/
|   |-- ...
|-- pipes/
    |-- ...

src/app/
|-- core/           # Singleton services
|-- shared/         # Reusable components
|-- features/
|   |-- auth/       # All auth stuff
|   |   |-- components/
|   |   |-- services/
|   |   |-- auth.routes.ts
|   |-- tasks/      # All tasks stuff
|   |-- dashboard/  # All dashboard stuff
|-- app.routes.ts

Detailed Frontend Structure

src/
|-- app/
|   |-- core/                          # Singleton services (providedIn: 'root')
|   |   |-- services/
|   |   |   |-- api.service.ts         # HTTP client wrapper
|   |   |   |-- auth.service.ts        # Authentication state
|   |   |   |-- storage.service.ts     # LocalStorage abstraction
|   |   |   |-- toast.service.ts       # Notifications
|   |   |-- guards/
|   |   |   |-- auth.guard.ts          # Protected routes
|   |   |   |-- guest.guard.ts         # Redirect if logged in
|   |   |-- interceptors/
|   |   |   |-- auth.interceptor.ts    # Add JWT to requests
|   |   |   |-- error.interceptor.ts   # Global error handling
|   |   |   |-- loading.interceptor.ts # Loading state
|   |   |-- core.module.ts             # Core providers
|   |
|   |-- shared/                        # Reusable (import in features)
|   |   |-- components/
|   |   |   |-- button/
|   |   |   |   |-- button.component.ts
|   |   |   |   |-- button.component.html
|   |   |   |   |-- button.component.scss
|   |   |   |-- modal/
|   |   |   |-- form-field/
|   |   |   |-- loading-spinner/
|   |   |   |-- empty-state/
|   |   |-- directives/
|   |   |   |-- click-outside.directive.ts
|   |   |   |-- autofocus.directive.ts
|   |   |-- pipes/
|   |   |   |-- time-ago.pipe.ts
|   |   |   |-- duration.pipe.ts
|   |   |-- index.ts                   # Barrel export
|   |
|   |-- features/                      # Feature modules (lazy loaded)
|   |   |-- auth/
|   |   |   |-- components/
|   |   |   |   |-- login-form/
|   |   |   |   |-- register-form/
|   |   |   |-- pages/
|   |   |   |   |-- login.page.ts
|   |   |   |   |-- register.page.ts
|   |   |   |-- services/
|   |   |   |   |-- auth-api.service.ts
|   |   |   |-- auth.routes.ts
|   |   |
|   |   |-- tasks/
|   |   |   |-- components/
|   |   |   |   |-- task-list/
|   |   |   |   |-- task-item/
|   |   |   |   |-- task-form/
|   |   |   |   |-- task-timer/
|   |   |   |-- pages/
|   |   |   |   |-- tasks-list.page.ts
|   |   |   |   |-- task-detail.page.ts
|   |   |   |-- services/
|   |   |   |   |-- tasks-api.service.ts
|   |   |   |-- models/
|   |   |   |   |-- task.model.ts
|   |   |   |-- state/
|   |   |   |   |-- tasks.store.ts     # Signals-based state
|   |   |   |-- tasks.routes.ts
|   |   |
|   |   |-- dashboard/
|   |       |-- components/
|   |       |   |-- stats-card/
|   |       |   |-- recent-tasks/
|   |       |-- pages/
|   |       |   |-- dashboard.page.ts
|   |       |-- dashboard.routes.ts
|   |
|   |-- layouts/                       # Layout components
|   |   |-- main-layout/               # Authenticated layout
|   |   |   |-- main-layout.component.ts
|   |   |   |-- header/
|   |   |   |-- sidebar/
|   |   |-- auth-layout/               # Guest layout
|   |
|   |-- app.component.ts
|   |-- app.routes.ts                  # Root routes with lazy loading
|   |-- app.config.ts                  # App configuration
|
|-- assets/
|   |-- images/
|   |-- icons/
|
|-- styles/
|   |-- _variables.scss                # Design tokens
|   |-- _mixins.scss
|   |-- global.scss
|
|-- environments/
    |-- environment.ts
    |-- environment.prod.ts

API Contract: Backend to Frontend

A clear API contract avoids communication issues between frontend and backend. Define it BEFORE implementing, not during.

Design a complete API contract for my project.

PROJECT: TaskFlow
RESOURCES: Users, Tasks, TimeEntries, Reports

For each resource include:
1. All endpoints (REST conventions)
2. Request/Response schemas (TypeScript interfaces)
3. Standard error response format
4. Authentication requirements per endpoint
5. Pagination strategy for lists
6. Rate limiting considerations

Use OpenAPI/Swagger style descriptions.
Include examples for each endpoint.

Standard Response Format

// =============================================================
// types/api-response.ts - Standard Response Wrappers
// =============================================================

// Success response for single item
interface ApiResponse<T> {{ '{' }}
  success: true;
  data: T;
{{ '}' }}

// Success response for lists with pagination
interface ApiListResponse<T> {{ '{' }}
  success: true;
  data: T[];
  meta: {{ '{' }}
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrev: boolean;
  {{ '}' }};
{{ '}' }}

// Error response (always same structure)
interface ApiErrorResponse {{ '{' }}
  success: false;
  error: {{ '{' }}
    code: string;           // Machine-readable: 'VALIDATION_ERROR'
    message: string;        // Human-readable: 'Invalid input'
    details?: unknown;      // Optional: field-specific errors
  {{ '}' }};
{{ '}' }}

// =============================================================
// Example: Task Endpoints
// =============================================================

/**
 * GET /api/v1/tasks
 * List all tasks for authenticated user
 *
 * Query Parameters:
 * - page: number (default: 1)
 * - limit: number (default: 20, max: 100)
 * - status: 'TODO' | 'IN_PROGRESS' | 'DONE' (optional filter)
 * - sort: 'createdAt' | 'dueDate' | 'title' (default: 'createdAt')
 * - order: 'asc' | 'desc' (default: 'desc')
 *
 * Response 200: ApiListResponse<Task>
 * Response 401: ApiErrorResponse (not authenticated)
 */

/**
 * POST /api/v1/tasks
 * Create a new task
 *
 * Request Body: CreateTaskDto
 * Response 201: ApiResponse<Task>
 * Response 400: ApiErrorResponse (validation error)
 * Response 401: ApiErrorResponse (not authenticated)
 */

/**
 * GET /api/v1/tasks/:id
 * Get single task by ID
 *
 * Response 200: ApiResponse<Task>
 * Response 404: ApiErrorResponse (task not found)
 * Response 403: ApiErrorResponse (not owner)
 */

/**
 * PATCH /api/v1/tasks/:id
 * Update task (partial update)
 *
 * Request Body: UpdateTaskDto (all fields optional)
 * Response 200: ApiResponse<Task>
 * Response 404: ApiErrorResponse
 */

/**
 * DELETE /api/v1/tasks/:id
 * Soft delete task
 *
 * Response 204: No content
 * Response 404: ApiErrorResponse
 */

Detailed API Contract Example

## Tasks API v1

Base URL: `https://api.taskflow.com/v1`
Authentication: Bearer token required for all endpoints

---

### List Tasks

`GET /tasks`

**Query Parameters:**
| Param | Type | Default | Description |
|-------|------|---------|-------------|
| page | integer | 1 | Page number (1-indexed) |
| limit | integer | 20 | Items per page (max: 100) |
| status | string | - | Filter by status |
| search | string | - | Search in title/description |

**Example Request:**
```
GET /tasks?page=1&limit=10&status=TODO
Authorization: Bearer eyJhbGciOiJIUzI1...
```

**Response 200:**
```json
{{ '{' }}
  "success": true,
  "data": [
    {{ '{' }}
      "id": "task_abc123",
      "title": "Complete API documentation",
      "description": "Write OpenAPI specs",
      "status": "TODO",
      "dueDate": "2025-02-15T10:00:00Z",
      "createdAt": "2025-01-30T12:00:00Z",
      "updatedAt": "2025-01-30T12:00:00Z"
    {{ '}' }}
  ],
  "meta": {{ '{' }}
    "page": 1,
    "limit": 10,
    "total": 45,
    "totalPages": 5,
    "hasNext": true,
    "hasPrev": false
  {{ '}' }}
{{ '}' }}
```

**Error Responses:**
| Code | When |
|------|------|
| 401 | Missing or invalid token |
| 500 | Server error |

---

### Create Task

`POST /tasks`

**Request Body:**
```json
{{ '{' }}
  "title": "Complete API documentation",
  "description": "Write OpenAPI specs for all endpoints",
  "dueDate": "2025-02-15T10:00:00Z"
{{ '}' }}
```

**Validation Rules:**
| Field | Rules |
|-------|-------|
| title | Required, 3-100 characters |
| description | Optional, max 1000 characters |
| dueDate | Optional, must be future date |

**Response 201:**
```json
{{ '{' }}
  "success": true,
  "data": {{ '{' }}
    "id": "task_xyz789",
    "title": "Complete API documentation",
    "status": "TODO",
    ...
  {{ '}' }}
{{ '}' }}
```

**Error Response 400:**
```json
{{ '{' }}
  "success": false,
  "error": {{ '{' }}
    "code": "VALIDATION_ERROR",
    "message": "Invalid input",
    "details": [
      {{ '{' }}
        "field": "title",
        "message": "Title must be at least 3 characters"
      {{ '}' }},
      {{ '{' }}
        "field": "dueDate",
        "message": "Due date must be in the future"
      {{ '}' }}
    ]
  {{ '}' }}
{{ '}' }}
```

API Versioning

Implement versioning from the start to avoid breaking changes in the future.

Database Schema Design

The database schema derives directly from requirements and defined entities.

-- =============================================================
-- PostgreSQL Schema for TaskFlow
-- =============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- -------------------------------------------------------------
-- Users Table
-- -------------------------------------------------------------
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  avatar_url VARCHAR(500),
  email_verified_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP  -- Soft delete
);

CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;

-- -------------------------------------------------------------
-- Tasks Table
-- -------------------------------------------------------------
CREATE TYPE task_status AS ENUM ('TODO', 'IN_PROGRESS', 'DONE');

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(100) NOT NULL,
  description TEXT,
  status task_status NOT NULL DEFAULT 'TODO',
  due_date TIMESTAMP,
  completed_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP  -- Soft delete
);

CREATE INDEX idx_tasks_user_status ON tasks(user_id, status)
  WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_user_due ON tasks(user_id, due_date)
  WHERE deleted_at IS NULL AND status != 'DONE';

-- -------------------------------------------------------------
-- Time Entries Table
-- -------------------------------------------------------------
CREATE TABLE time_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP,  -- NULL = timer running
  duration_seconds INTEGER,  -- Calculated on stop
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_time_entries_task ON time_entries(task_id);
CREATE INDEX idx_time_entries_user_date ON time_entries(user_id, started_at);

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

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

CREATE TRIGGER tasks_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Architecture Decision Records (ADR)

Document important architectural decisions for future reference. ADRs explain the why, not just the what.

# ADR-001: PostgreSQL as Database Choice

## Status
Accepted (2025-01-30)

## Context
We need a database to persist application data for TaskFlow.
Key requirements:
- Support for entity relationships (users -> tasks -> time_entries)
- Complex queries for reports and analytics
- Support for semi-structured data (future metadata)
- Vertical scalability sufficient for MVP (1000 users)

## Decision
We use **PostgreSQL 16** as primary database.

## Alternatives Considered

### MongoDB
- Pros: Schema flexibility
- Pros: Native horizontal scaling
- Cons: Complex JOIN queries difficult
- Cons: Transactional consistency limited

### MySQL
- Pros: Wide adoption, familiar
- Cons: Less mature JSON support
- Cons: Fewer advanced features (CTE, window functions)

### SQLite
- Pros: Zero setup, embedded
- Cons: Not suitable for multi-instance production
- Cons: Concurrency limitations

## Consequences

### Positive
- Powerful and optimized SQL queries
- Native JSONB support
- Complete ACID transactions
- Great tooling (Prisma, pgAdmin)
- Free tier on Supabase/Neon

### Negative
- More complex setup than SQLite
- Requires managed service in production

### Risks
- Might be overkill for initial MVP
- Costly migration if we change DB later

## Notes
Re-evaluate after 6 months in production if access patterns change.

Pre-Implementation Checklist

Conclusion

Well-designed architecture makes development smoother and code more maintainable. Copilot can help you explore options, generate boilerplate, and document decisions, but architectural choices require understanding of the domain and specific project needs. Don't copy complex architectures: choose the right level for your project.