Database Schema

Traces uses Supabase PostgreSQL with Prisma ORM. The schema is designed for forensic integrity with soft deletes and immutable audit logs. The full source of truth is packages/database/prisma/schema.prisma (the generated client lives in packages/database/generated/prisma, imported as @traces/database).

Core Principles

1. No secrets in database - Credentials stored only in Doppler (only a secret reference is persisted)
2. Immutable audit trail - Chain of custody events are append-only
3. Soft deletes - Most records are marked deleted_at rather than removed (audit/log tables are append-only and have no soft-delete column)
4. Row-level security - Database-enforced access control (planned; authorization is currently enforced in the application layer via Clerk + route handlers)

note

This page documents the shapes of the main tables. Field names use the database (snake_case) column names; the Prisma models map these to camelCase properties (e.g. platform_name ↔ platformName). Refer to schema.prisma for the exact @map/@relation details.

Entity Relationship

┌─────────┐       ┌───────────┐       ┌─────────────┐
│  users  │──────►│ customers │──────►│ collections │
└────┬────┘       └─────┬─────┘       └──────┬──────┘
     │                  │                    │
     │                  │   ┌────────────────┤
     │                  │   │                │
     │            ┌─────┴───┴──┐             │
     └───────────►│  analysts  │─────────────┤
                  └─────┬──────┘             │
                        │                    │
┌───────────┐     ┌─────┴───────┐           │
│connectors │────►│ credentials │───────────┤
└─────┬─────┘     └─────────────┘           │
      │                                      │
      │           ┌─────────────────┐        │
      │           │ collected_files │◄───────┤
      │           └─────────────────┘        │
      │                                      │
      │           ┌─────────────────────┐    │
      │           │ chain_of_custody_   │◄───┤
      │           │ events              │    │
      │           └─────────────────────┘    │
      │                                      │
      │           ┌─────────────────────┐    │
      │           │ credential_access_  │◄───┘
      │           │ logs                │
      │           └─────────────────────┘
      │
      │           ┌─────────────────────┐
      └──────────►│ connector_generation│
                  │ _logs               │
                  └─────────────────────┘

Core Tables

users

Managed by Clerk - stores only references.

{
  id: string                 // Clerk user ID
  email: string              // @unique
  role: enum ['customer', 'analyst', 'admin']
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?     // soft delete
}

Indexes: role, deleted_at

---

customers

{
  id: string (UUID)
  user_id: string (FK -> users.id)  // @unique
  company_name: string
  contact_name: string
  contact_email: string
  contact_phone: string?
  verification_status: enum ['pending', 'verified', 'rejected']
  verification_notes: text?
  terms_accepted_at: timestamp?
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: verification_status, deleted_at

---

analysts

{
  id: string (UUID)
  user_id: string (FK -> users.id)  // @unique
  full_name: string
  signature_data: text?      // base64 encoded signature
  certifications: jsonb?
  is_active: boolean
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: is_active, deleted_at

---

connectors

Publishing a connector creates a new row with a new UUID (for forensic integrity) and soft-deletes the previous version. Endpoint IDs and test-data references are remapped on publish (matched by method+path).

{
  id: string (UUID)
  platform_name: string
  platform_url: string?
  version: string
  endpoints: jsonb                    // array of endpoint configs
  auth_config: jsonb
  rate_limit_global: jsonb?
  default_data_extraction: jsonb?     // default extraction config
  documentation_url: string?
  download_queue_config: jsonb?
  runtime_parameters: jsonb?          // runtime parameter schema for collections
  static_variable_values: jsonb?
  restored_from: string?              // source version id when restored from history
  commit_message: string?            // analyst's note for this published version
  status: enum ['generating', 'testing', 'production', 'disabled']  // default: testing
  priority: enum ['low', 'medium', 'high', 'critical']              // default: medium (RoadmapPriority)
  created_by: string (UUID, FK -> analysts.id)
  updated_by: string? (UUID, FK -> analysts.id)
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: platform_name, status, priority, created_by, deleted_at

---

credentials

{
  id: string (UUID)
  customer_id: string? (FK -> customers.id)      // nullable for analyst test creds
  connector_id: string (FK -> connectors.id)
  secret_arn: string                             // Doppler secret reference (legacy column name)
  secret_version: string
  credential_type: enum ['oauth2', 'api_key', 'bearer', 'username_password', 'certificate', 'query_params', 'token_exchange']

  // Analyst-uploaded on behalf of customer
  uploaded_by_analyst_id: string? (FK -> analysts.id)
  on_behalf_of_customer_id: string? (FK -> customers.id)

  // Validation
  expires_at: timestamp?
  last_validated_at: timestamp?
  validation_status: enum ['valid', 'invalid', 'expired', 'not_tested']
  validation_error: text?

  // Revocation
  revoked_at: timestamp?
  revoked_by: string? (FK -> users.id)
  revocation_reason: text?

  // Analyst test credentials (temporary)
  is_analyst_test: boolean
  test_expires_at: timestamp?
  test_analyst_id: string? (FK -> analysts.id)

  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: customer_id, connector_id, uploaded_by_analyst_id, expires_at, validation_status, deleted_at, is_analyst_test

---

collections

{
  id: string (UUID)
  customer_id: string? (FK -> customers.id)
  connector_id: string (FK -> connectors.id)
  credential_id: string (FK -> credentials.id)
  assigned_analyst_id: string? (FK -> analysts.id)

  // Analyst-initiated on behalf of customer
  initiated_by_analyst_id: string? (FK -> analysts.id)
  on_behalf_of_customer_id: string? (FK -> customers.id)

  // Collection details
  case_reference: string?
  business_context: text
  priority: enum ['low', 'normal', 'high', 'urgent']
  scope_config: jsonb

  // Status tracking
  status: enum [
    'pending', 'validating_creds', 'queued', 'in_progress',
    'paused', 'error', 'partial', 'completed', 'reviewing',
    'signed_off', 'delivered', 'cancelled'
  ]

  // Progress
  total_endpoints: integer
  completed_endpoints: integer
  failed_endpoints: integer
  total_files_collected: integer
  total_bytes_collected: bigint
  checkpoint: jsonb?                  // resumable collection state
  runtime_parameter_values: jsonb?   // values for the connector's runtimeParameters schema

  // Timestamps
  started_at: timestamp?
  paused_at: timestamp?
  completed_at: timestamp?
  reviewed_at: timestamp?
  signed_off_at: timestamp?
  delivered_at: timestamp?

  // Sign-off
  sign_off_signature: text?
  sign_off_notes: text?
  signed_off_by_id: string? (FK -> analysts.id)

  // Error tracking
  error_message: text?
  error_count: integer
  last_error_at: timestamp?

  // Delivery
  zip_file_path: string?
  zip_password: string?               // encrypted
  sas_url: string?
  sas_expires_at: timestamp?

  // Analyst test collections (temporary)
  is_analyst_test: boolean
  test_expires_at: timestamp?

  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: status, priority, (customer_id, status), (assigned_analyst_id, status), initiated_by_analyst_id, created_at, deleted_at, is_analyst_test

---

collected_files

{
  id: string (UUID)
  collection_id: string (FK -> collections.id)
  api_log_id: string? (FK -> api_logs.id)
  original_filename: string
  stored_filename: string
  file_path: string
  file_size_bytes: bigint
  mime_type: string
  sha256_hash: string
  sha256_hash_validated: boolean
  hash_validator_library: string?
  source_metadata: jsonb?
  status: enum ['collecting', 'hashing', 'validating', 'stored', 'error']
  error_message: text?
  collected_at: timestamp
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: (collection_id, status), sha256_hash, deleted_at

---

Audit & Logging Tables

chain_of_custody_events

Immutable audit trail - append only

{
  id: string (UUID)
  collection_id: string (FK -> collections.id)
  event_type: enum [
    'collection_created', 'credentials_validated', 'collection_started',
    'collection_paused', 'collection_resumed', 'collection_completed',
    'data_collected', 'endpoint_skipped', 'analyst_assigned', 'analyst_reviewed',
    'analyst_signed_off', 'zip_created', 'sas_url_generated',
    'customer_notified', 'collection_delivered', 'collection_archived',
    'error_occurred', 'credentials_revoked', 'collection_cancelled'
  ]
  actor_id: string? (FK -> users.id)
  actor_type: enum ['system', 'analyst', 'customer', 'automated']
  actor_ip: inet?
  on_behalf_of: string? (FK -> customers.id)
  event_data: jsonb?
  previous_state: string?
  new_state: string?
  timestamp: timestamp
  created_at: timestamp
}

Indexes: (collection_id, timestamp), (event_type, timestamp)

warning

This table is APPEND-ONLY. Never UPDATE or DELETE records.

---

credential_access_logs

Track every time credentials are accessed.

{
  id: string (UUID)
  credential_id: string (FK -> credentials.id)
  accessed_by: string (FK -> users.id)
  access_type: enum ['read', 'create', 'validate', 'use_in_collection', 'rotate', 'revoke', 'upload_on_behalf']
  collection_id: string? (FK -> collections.id)
  source_ip: inet
  user_agent: string?
  success: boolean
  failure_reason: text?
  metadata: jsonb?
  timestamp: timestamp
  created_at: timestamp
}

Indexes: (credential_id, timestamp), (accessed_by, timestamp)

---

api_logs

Comprehensive forensic logging of every API call.

{
  id: string (UUID)
  collection_id: string (FK -> collections.id)
  connector_id: string (FK -> connectors.id)
  chain_of_custody_sequence: integer      // ordering within collection
  data_custodian_id: string? (FK -> analysts.id)

  // Request details
  request_timestamp: timestamp
  request_method: string
  request_url: text
  request_headers_sanitized: jsonb?
  request_body: jsonb?
  request_body_hash: string?
  user_agent: string

  // Network details
  source_ip: inet
  destination_ip: inet
  dns_resolution: jsonb?
  tls_cert_fingerprint: string?

  // Latency breakdown (ms)
  latency_dns_ms: integer?
  latency_tcp_ms: integer?
  latency_tls_ms: integer?
  latency_ttfb_ms: integer?
  latency_download_ms: integer?

  // Response details
  response_code: integer
  response_size_bytes: bigint
  response_time_total_ms: integer
  response_headers: jsonb?
  response_error_message: text?
  response_body_hash: string
  response_body_hash_validated: boolean
  response_body_hash_validator: string?

  // Forensic metadata
  forensic_hash_algorithm: string         // default: SHA256
  forensic_hash_validator: string
  collection_context: jsonb
  integrity_verified_at: timestamp?
  integrity_verification_method: string?

  // Collection context
  endpoint_category: string?
  api_request_trace_id: string?
  retry_attempt: integer
  pagination_cursor: string?
  parallel_execution_group_id: string?

  // Metrics
  time_to_file_hashed_ms: integer?
  expected_response_size_bytes: bigint?
  data_yield_score: decimal?

  created_at: timestamp
}

Indexes: (collection_id, request_timestamp), (collection_id, chain_of_custody_sequence), response_code, created_at

Storage

This table grows fast (one row per API call). Consider partitioning by month after 6 months of operation.

---

Supporting Tables

notifications

{
  id: string (UUID)
  user_id: string (FK -> users.id)
  collection_id: string? (FK -> collections.id)
  notification_type: enum [
    'collection_started', 'collection_progress', 'collection_completed',
    'collection_error', 'action_required', 'credentials_expiring', 'delivery_ready'
  ]
  title: string
  message: text
  sent_email: boolean
  sent_in_app: boolean
  read_at: timestamp?
  acted_on_at: timestamp?
  created_at: timestamp
  updated_at: timestamp
}

Indexes: (user_id, read_at), created_at

---

connector_requests

Track customer requests for new connectors.

{
  id: string (UUID)
  customer_id: string (FK -> customers.id)
  platform_name: string
  platform_url: string?
  use_case: text
  priority: enum ['low', 'medium', 'high']
  status: enum ['requested', 'reviewing', 'in_progress', 'completed', 'rejected']
  assigned_to: string? (FK -> analysts.id)
  estimated_completion: date?
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: (status, priority), customer_id, assigned_to, deleted_at

---

Connector Automation Tables

connector_generation_logs

Track automated connector generation from API documentation.

{
  id: string (UUID)
  documentation_url: text
  spec_type: string                       // openapi_3, swagger_2, postman, html
  platform_name: string
  platform_url: string?

  // Parsing results
  parsed_spec: jsonb?
  endpoints_parsed: integer

  // Analysis results
  draft_endpoints: jsonb?
  draft_auth_config: jsonb?
  credential_guide: jsonb?
  endpoints_kept: integer
  endpoints_discarded: integer

  // LLM tracking
  llm_model: string?
  llm_tokens_used: integer
  llm_batches_completed: integer
  llm_batches_total: integer

  // Status
  status: enum ['parsing', 'analyzing', 'review', 'completed', 'failed', 'cancelled']
  current_step: string?
  error_message: text?

  created_by: string (FK -> analysts.id)
  connector_id: string? (FK -> connectors.id)
  created_at: timestamp
  updated_at: timestamp
}

Indexes: created_by, status, created_at

---

draft_test_credentials

Temporary test credentials for dependency mapper (24h expiry).

{
  id: string (UUID)
  generation_log_id: string (FK -> connector_generation_logs.id)  // @unique, cascade delete
  secret_arn: string
  credential_type: enum [...]
  analyst_id: string (FK -> analysts.id)
  expires_at: timestamp
  created_at: timestamp
}

Indexes: expires_at

---

endpoint_test_results

Endpoint test execution audit trail for dependency mapper.

{
  id: string (UUID)
  generation_log_id: string (FK -> connector_generation_logs.id)  // cascade delete
  endpoint_id: string
  request_path: text
  request_body: text?
  status_code: integer
  response_time_ms: integer
  response_preview: text?
  pagination_info: jsonb?
  success: boolean
  error_message: text?
  analyst_id: string (FK -> analysts.id)
  created_at: timestamp
}

Indexes: (generation_log_id, endpoint_id), created_at

---

connector_test_data

Persisted test data for published connectors.

{
  id: string (UUID)
  connector_id: string (FK -> connectors.id)  // @unique
  endpoint_results: jsonb                     // { [endpointId]: { response, statusCode, ... } }
  dependency_mappings: jsonb                  // array of mapping configs
  pagination_configs: jsonb                   // { [endpointId]: { enabled, type, ... } }
  tested_by: string (FK -> analysts.id)
  created_at: timestamp
  updated_at: timestamp
}

Indexes: connector_id, tested_by

Storage quirk

Variables, body configs, recursion configs, etc. are packed into pagination_configs under __-prefixed keys (e.g. __reusableVariables, __staticVariables). The test-data GET handler extracts these back into separate fields.

---

connector_phases

Server-persisted execution phases for a connector — ordered groups of endpoint stages. Persisted so phases survive across devices and browser-cache clears.

{
  id: string (UUID)
  connector_id: string (FK -> connectors.id)  // cascade delete
  name: string
  position: integer                            // 0-indexed; ordering
  stages: jsonb                                // default []
  created_at: timestamp
  updated_at: timestamp
}

Indexes: (connector_id, position)

---

connector_audit_logs

Granular audit trail for connector & endpoint changes made by analysts (distinct from the collection-level chain_of_custody_events).

{
  id: string (UUID)
  connector_id: string (FK -> connectors.id)
  analyst_id: string (FK -> analysts.id)
  event_type: string
  endpoint_id: string?
  endpoint_path: string?
  payload: jsonb
  created_at: timestamp
}

Indexes: (connector_id, created_at DESC), analyst_id

---

Analyst Communication & Platform Tables

analyst_messages

Persistent 1:1 messages between analysts (Comm Hub).

{
  id: string (UUID)
  from_analyst_id: string (FK -> analysts.id)
  to_analyst_id: string (FK -> analysts.id)
  content: text
  read_at: timestamp?
  created_at: timestamp
}

Indexes: (to_analyst_id, read_at), (from_analyst_id, to_analyst_id, created_at)

---

analyst_presence

Heartbeat-based online/offline presence for analysts (1:1 with analyst).

{
  analyst_id: string (PK, FK -> analysts.id)
  last_seen_at: timestamp
  updated_at: timestamp
}

---

changelog_entries

Changelog / release-notes entries.

{
  id: string (UUID)
  title: string
  body: text
  version: string?
  published_at: timestamp
  created_by_id: string (FK -> users.id)
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: published_at, deleted_at

---

error_logs

Persistent error log for API-route and system errors.

{
  id: string (UUID)
  timestamp: timestamp
  error_code: string
  message: text
  details: jsonb?
  http_status: integer?
  route_path: string?
  request_method: string?
  connector_id: string? (FK -> connectors.id)
  collection_id: string? (FK -> collections.id)
  user_id: string? (FK -> users.id)
  severity: enum ['warning', 'error', 'critical']      // default: error
  source: enum ['api_route', 'frontend', 'system', 'background_job']  // default: api_route
  created_at: timestamp
}

Indexes: timestamp, error_code, severity, source, connector_id, collection_id, user_id

---

feature_requests

Internal feature-request board.

{
  id: string (UUID)
  description: text
  submitted_by_id: string (FK -> users.id)
  submitted_by_name: string
  status: enum ['received', 'acknowledged', 'building', 'implemented', 'denied']  // default: received
  implemented_at: timestamp?
  deleted_at: timestamp?
  created_at: timestamp
  updated_at: timestamp
}

Indexes: status, submitted_by_id, deleted_at

---

Roadmap Tables

roadmap_entries

Connectors planned for development (the "Up Next" roadmap todo list).

{
  id: string (UUID)
  name: string
  documentation_url: string
  description: text?
  reason: text?
  priority: enum ['low', 'medium', 'high', 'critical']  // default: medium
  complexity: enum ['low', 'medium', 'high']            // default: medium
  ideal_start_date: date
  created_by: string (UUID, FK -> analysts.id)
  created_at: timestamp
  updated_at: timestamp
  deleted_at: timestamp?
}

Indexes: ideal_start_date, priority, deleted_at

---

roadmap_audit_logs

Audit trail for roadmap entry create/update/delete actions.

{
  id: string (UUID)
  entry_id: string (FK -> roadmap_entries.id)
  analyst_id: string (FK -> analysts.id)
  event_type: string
  payload: jsonb
  created_at: timestamp
}

Indexes: (entry_id, created_at DESC), analyst_id

---

Indexes Summary

Key indexes for performance:

Table	Index
All tables	Foreign keys
collections	(customer_id, status), (assigned_analyst_id, status)
api_logs	(collection_id, request_timestamp), (collection_id, chain_of_custody_sequence)
chain_of_custody_events	(collection_id, timestamp)
collected_files	sha256_hash
credential_access_logs	(credential_id, timestamp)

---

Soft Delete Pattern

Most tables carry a nullable deleted_at column and are soft-deleted (marked, never physically removed). Append-only audit/log tables (chain_of_custody_events, credential_access_logs, api_logs, connector_audit_logs, roadmap_audit_logs, error_logs) have no deleted_at column.

Soft-delete and the "exclude deleted rows" filter are applied explicitly in application code / route handlers (e.g. where: { deletedAt: null }), not via a global Prisma middleware. Each query that should hide soft-deleted rows must include that filter itself.

---

Foreign Key Behavior

Most foreign keys use Prisma's default behavior:

• Maintains referential integrity
• Application handles cascading deletes explicitly

Exceptions with CASCADE (deleting the parent deletes the children):

• draft_test_credentials → connector_generation_logs
• endpoint_test_results → connector_generation_logs
• connector_phases → connectors

---

Security Considerations

Encryption at Rest

• PostgreSQL native encryption
• All credential data in Doppler, encrypted with AES-256-GCM (only a secret reference stored in DB)
• File storage encrypted (S3 server-side encryption)

Row-Level Security (Planned)

-- Customers can only see their own collections
CREATE POLICY customer_collections ON collections
  FOR ALL TO customer_role
  USING (customer_id = current_user_customer_id());

-- Analysts can see all collections
CREATE POLICY analyst_collections ON collections
  FOR ALL TO analyst_role
  USING (true);

Audit Logging

• ALL mutations logged to chain_of_custody_events
• Credential access logged to credential_access_logs
• Database query logs monitored for anomalies

---

Next Steps

• State Machine
• Security
• Chain of Custody