Prd V06 Technical Specification

API-001: Create Report
Method: POST
Path: /api/reports
Purpose: Create new report from selected data source and template (implements UJ-001 Step 1)
Auth: User

Journey: UJ-001 (Step 1 - Create Report)
Screen: SCR-002 (Report Builder)

Request:
  Body:
    {
      title: string (required) — Report name
      templateId: string (required) — Selected template (FEA-008)
      dataSourceId: string (required) — Connected data source (FEA-001)
      options: { dateRange: { start, end }, filters: [...] }
    }

Response:
  Success (201):
    {
      data: { id, title, status: "pending|generating|ready", createdAt }
    }
  Errors:
    - 400: Invalid input — Missing required field
    - 403: Forbidden — User doesn't own data source
    - 404: Not found — Template or data source not found
    - 429: Rate limit exceeded

Business Rules: BR-015 (max 100 reports per user)
Data: DBT-001 (reports table), DBT-002 (data_sources table)

DBT-001: Reports
Purpose: Stores user-generated reports (entities created by API-001, updated by API-004)
Table: reports

Fields:
  - id: uuid — Primary key
  - user_id: uuid — Report owner (FK → users) [NOT NULL]
  - title: varchar(255) — Display name [NOT NULL]
  - status: enum('pending','generating','ready','failed') [NOT NULL]
  - created_at: timestamp [NOT NULL, DEFAULT now()]

Relationships:
  - belongs_to: users via user_id
  - belongs_to: templates via template_id

Indexes:
  - user_id — List reports by user (API-003)
  - (user_id, created_at DESC) — Recent reports (API-003)
  - status — Find pending reports (background job)

Constraints:
  - title: NOT NULL, length 1-255
  - status: valid enum only

Business Rules: BR-015 (max 100 per user — enforce in API-001)
APIs: API-001 (create), API-002 (get), API-003 (list), API-005 (delete)

API-XXX: [Endpoint Name]
Method: [GET | POST | PUT | PATCH | DELETE]
Path: [/resource/{id}/action]
Purpose: [What this endpoint does]
Auth: [Public | User | Admin | Service]

Journey: [UJ-XXX that uses this]
Screen: [SCR-XXX that calls this]

Request:
  Headers:
    - Authorization: Bearer <token>
    - Content-Type: application/json
  Params:
    - id: string (required) — Resource identifier
  Query:
    - limit: number (optional, default 20) — Pagination limit
  Body:
    {
      field: type — Description
    }

Response:
  Success (200/201):
    {
      data: { ... }
    }
  Errors:
    - 400: Invalid input — [when this occurs]
    - 401: Unauthorized — [when this occurs]
    - 404: Not found — [when this occurs]
    - 500: Server error — [when this occurs]

Business Rules: [BR-XXX enforced here]
Data: [DBT-XXX entities accessed]
Rate Limit: [requests/minute if applicable]

API-001: Create Report
Method: POST
Path: /api/reports
Purpose: Create a new report from selected data source and template
Auth: User

Journey: UJ-001 (Step 1 - Create Report)
Screen: SCR-002 (Report Builder)

Request:
  Headers:
    - Authorization: Bearer <token>
    - Content-Type: application/json
  Body:
    {
      title: string (required) — Report name
      templateId: string (required) — Selected template
      dataSourceId: string (required) — Connected data source
      options: {
        dateRange: { start: ISO8601, end: ISO8601 }
        filters: [{ field: string, operator: string, value: any }]
      }
    }

Response:
  Success (201):
    {
      data: {
        id: string
        title: string
        status: "pending" | "generating" | "ready"
        createdAt: ISO8601
      }
    }
  Errors:
    - 400: Invalid input — Missing required field or invalid templateId
    - 401: Unauthorized — Invalid or expired token
    - 403: Forbidden — User doesn't own data source
    - 404: Not found — Template or data source not found
    - 429: Too many requests — Rate limit exceeded

Business Rules: BR-015 (max 100 reports per user)
Data: DBT-001 (reports), DBT-002 (data_sources)
Rate Limit: 10 requests/minute

DBT-XXX: [Entity Name]
Purpose: [What this entity represents]
Table: [database_table_name]

Fields:
  - id: uuid — Primary key, auto-generated
  - [field_name]: [type] — Description [constraints]
  - created_at: timestamp — Record creation (auto)
  - updated_at: timestamp — Last modification (auto)

Relationships:
  - belongs_to: [DBT-YYY] via [foreign_key]
  - has_many: [DBT-ZZZ]

Indexes:
  - [field_name] — [Query pattern it supports]

Constraints:
  - [field]: [UNIQUE | NOT NULL | CHECK expression]

Business Rules: [BR-XXX that affect this entity]
APIs: [API-XXX that read/write this]

DBT-001: Reports
Purpose: Stores user-generated reports with configuration and status
Table: reports

Fields:
  - id: uuid — Primary key
  - user_id: uuid — Report owner (FK → users) [NOT NULL]
  - title: varchar(255) — Display name [NOT NULL]
  - template_id: uuid — Template used (FK → templates) [NOT NULL]
  - data_source_id: uuid — Data source (FK → data_sources) [NOT NULL]
  - status: enum('pending','generating','ready','failed') — Generation status [NOT NULL, DEFAULT 'pending']
  - options: jsonb — Report configuration (date range, filters) [DEFAULT '{}']
  - output_url: varchar(500) — Generated report file URL [NULL until ready]
  - created_at: timestamp — [NOT NULL, DEFAULT now()]
  - updated_at: timestamp — [NOT NULL, DEFAULT now()]

Relationships:
  - belongs_to: DBT-010 (users) via user_id
  - belongs_to: DBT-002 (templates) via template_id
  - belongs_to: DBT-003 (data_sources) via data_source_id

Indexes:
  - user_id — List reports by user
  - (user_id, created_at DESC) — List recent reports
  - status — Find pending reports for processing

Constraints:
  - title: NOT NULL, length 1-255
  - status: NOT NULL, valid enum value

Business Rules: BR-015 (max 100 reports per user — enforce in API)
APIs: API-001 (create), API-002 (get), API-003 (list), API-005 (delete)