03 - Architettura Backend e Frontend con Copilot

Progettare l'Architettura con Copilot

Una buona architettura è la fondamenta di un progetto sostenibile. In questa fase, useremo Copilot per progettare sia il backend che il frontend, definendo come i due comunicano attraverso un contratto API chiaro. L'architettura non è solo "dove mettere i file", ma un insieme di decisioni che influenzano scalabilità, testabilità e manutenibilità del progetto.

Panoramica della Serie

Questo è il terzo articolo della serie "GitHub Copilot: AI-Driven Personal Project".

Scelta dell'Architettura Backend

Per progetti personali, esistono diversi pattern architetturali. La scelta dipende dalla complessità del dominio e dalle competenze del team (tu!).

Comparazione Pattern Architetturali

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

Diagramma a Layer

┌─────────────────────────────────────────────────────────────┐
│                    PRESENTATION LAYER                       │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │ Controllers │  │  Middleware │  │ DTOs (Validation)   │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│  Responsabilità: HTTP handling, request/response transform  │
├─────────────────────────────────────────────────────────────┤
│                     BUSINESS LAYER                          │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │  Services   │  │  Use Cases  │  │   Domain Logic      │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│  Responsabilità: Business rules, orchestration, validation  │
├─────────────────────────────────────────────────────────────┤
│                   DATA ACCESS LAYER                         │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │Repositories │  │   Entities  │  │   Query Builders    │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│  Responsabilità: Data persistence, query optimization       │
├─────────────────────────────────────────────────────────────┤
│                    INFRASTRUCTURE                           │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │  Database   │  │External APIs│  │   File System       │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│  Responsabilità: Concrete implementations, external deps    │
└─────────────────────────────────────────────────────────────┘

                    ↑ DEPENDENCY DIRECTION ↑
          (I layer superiori dipendono da quelli inferiori)

Regole di Dipendenza

Flusso di una Richiesta

Vediamo come una richiesta attraversa tutti i layer, dall'HTTP alla persistenza.

// ═══════════════════════════════════════════════════════════
// 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,                    // Verifica JWT
  validateDto(CreateTaskDto),        // Valida body
  controller.create.bind(controller) // Gestisce richiesta
);

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);
  {{ '}' }}
{{ '}' }}

Error Handling Consistente

Un sistema di error handling ben progettato semplifica debugging e 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
    {{ '}' }}
  {{ '}' }});
{{ '}' }}

Architettura Frontend: Feature-Based

Per il frontend, una struttura feature-based scala meglio di una struttura tradizionale per tipo (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

Confronto Strutture Frontend

src/app/
├── components/     # 50+ files
│   ├── user-list.ts
│   ├── user-form.ts
│   ├── task-list.ts
│   ├── task-item.ts
│   └── ... (mescolati)
├── services/
│   └── ... (tutti insieme)
├── models/
│   └── ...
└── pipes/
    └── ...

src/app/
├── core/           # Singleton services
├── shared/         # Componenti riusabili
├── features/
│   ├── auth/       # Tutto di auth
│   │   ├── components/
│   │   ├── services/
│   │   └── auth.routes.ts
│   ├── tasks/      # Tutto di tasks
│   └── dashboard/  # Tutto di dashboard
└── app.routes.ts

Struttura Frontend Dettagliata

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

Contratto API: Backend ↔ Frontend

Un contratto API chiaro evita problemi di comunicazione tra frontend e backend. Definiscilo PRIMA di implementare, non durante.

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
 */

Esempio API Contract Dettagliato

## 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"
      {{ '}' }}
    ]
  {{ '}' }}
{{ '}' }}
```

Versioning dell'API

Implementa il versioning fin dall'inizio per evitare breaking changes in futuro.

Database Schema Design

Lo schema del database deriva direttamente dai requisiti e dalle entità definite.

-- ═══════════════════════════════════════════════════════════
-- 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)

Documenta le decisioni architetturali importanti per riferimento futuro. Gli ADR spiegano il perché, non solo il cosa.

# ADR-001: Scelta di PostgreSQL come Database

## Status
Accepted (2025-01-30)

## Context
Abbiamo bisogno di un database per persistere i dati dell'applicazione TaskFlow.
I requisiti chiave sono:
- Supporto per relazioni tra entità (users → tasks → time_entries)
- Query complesse per report e analytics
- Supporto per dati semi-strutturati (future metadata)
- Scalabilità verticale sufficiente per MVP (1000 users)

## Decision
Usiamo **PostgreSQL 16** come database primario.

## Alternatives Considered

### MongoDB
- ✅ Flessibilità schema
- ✅ Scaling orizzontale nativo
- ❌ Query JOIN complesse difficili
- ❌ Consistenza transazionale limitata

### MySQL
- ✅ Ampia adozione, familiare
- ❌ Supporto JSON meno maturo
- ❌ Meno feature avanzate (CTE, window functions)

### SQLite
- ✅ Zero setup, embedded
- ❌ Non adatto per produzione multi-istanza
- ❌ Limitazioni concurrency

## Consequences

### Positive
- Query SQL potenti e ottimizzate
- Supporto nativo per JSONB
- Transazioni ACID complete
- Ottimo tooling (Prisma, pgAdmin)
- Free tier su Supabase/Neon

### Negative
- Setup più complesso di SQLite
- Richiede managed service in produzione

### Risks
- Potrebbe essere overkill per MVP iniziale
- Migrazione costosa se cambieremo DB

## Notes
Rivalutare dopo 6 mesi di produzione se i pattern di accesso cambiano.

Checklist Pre-Implementazione

Conclusione

Un'architettura ben progettata rende lo sviluppo più fluido e il codice più manutenibile. Copilot può aiutarti a esplorare opzioni, generare boilerplate e documentare decisioni, ma le scelte architetturali richiedono comprensione del dominio e delle esigenze specifiche del progetto. Non copiare architetture complesse: scegli il livello giusto per il tuo progetto.