wulf-pulse/tasks/tasks-prd-postgres.md

# Task List: PostgreSQL Autotask Sync Implementation

Generated from: `prd-postgres.md`

## Relevant Files

### Infrastructure & Configuration
- `docker-compose.yml` - ✅ Added PostgreSQL service, volumes, and health checks
- `.env.local` - ✅ Added PostgreSQL connection environment variables
- `.env.example` - ✅ Created with PostgreSQL configuration documentation
- `migrations/` - ✅ Created directory for database migration files
- `migrations/001_initial_schema.sql` - ✅ Initial database schema with all 13 entity tables, audit fields, foreign keys, and sync_history
- `migrations/002_add_indexes.sql` - ✅ Additional performance indexes for common query patterns
- `migrations/003_fix_resource_type.sql` - ✅ Fix data type mismatches for resources table (resource_type, travel_availability_pct)

### Database & Services
- `lib/services/postgres-client.ts` - ✅ PostgreSQL connection pool with query methods, upsert, bulk operations
- `lib/services/sync-service.ts` - ✅ Core sync orchestration service with full/incremental/entity-specific sync
- `lib/services/entity-sync.ts` - ✅ Entity-specific sync logic for all 13 Autotask entities
- `lib/services/rate-limiter.ts` - ✅ Rate limiting with 10 req/sec throttling and queue
- `lib/types/sync.ts` - ✅ TypeScript types for SyncConfig, SyncStatus, SyncHistory, EntityType enum
- `lib/types/database.ts` - ✅ TypeScript interfaces for all 13 entity tables
- `package.json` - ✅ Added pg and @types/pg dependencies

### API Routes
- `app/api/sync/full/route.ts` - ✅ POST endpoint for full sync
- `app/api/sync/incremental/route.ts` - ✅ POST endpoint for incremental sync
- `app/api/sync/entity/route.ts` - ✅ POST endpoint for entity-specific sync
- `app/api/sync/history/route.ts` - ✅ GET endpoint for sync history
- `app/api/sync/last-sync/route.ts` - ✅ GET endpoint for last sync times
- `app/api/data/companies/route.ts` - ✅ GET endpoint for querying companies from PostgreSQL
- `app/api/data/tickets/route.ts` - ✅ GET endpoint for querying tickets from PostgreSQL
- `app/api/data/tasks/route.ts` - GET endpoint for querying tasks from PostgreSQL (pending)
- `app/api/data/configuration-items/route.ts` - GET endpoint for querying config items from PostgreSQL
- `app/api/data/contacts/route.ts` - GET endpoint for querying contacts from PostgreSQL
- `app/api/data/contracts/route.ts` - GET endpoint for querying contracts from PostgreSQL
- `app/api/data/projects/route.ts` - GET endpoint for querying projects from PostgreSQL
- `app/api/data/resources/route.ts` - GET endpoint for querying resources from PostgreSQL
- `app/api/data/billing-items/route.ts` - GET endpoint for querying billing items from PostgreSQL

### Admin UI Components
- `app/layout.tsx` - ✅ Added Toaster component for toast notifications
- `app/admin/sync/page.tsx` - ✅ Main admin sync page with responsive layout
- `components/admin/SyncControlPanel.tsx` - Sync control buttons and entity selector
- `components/admin/SyncDashboard.tsx` - Sync status and history dashboard
- `components/admin/EntitySelector.tsx` - Checkbox component for entity selection
- `components/admin/SyncProgressBar.tsx` - Real-time progress indicator
- `components/admin/SyncHistoryTable.tsx` - Table displaying past sync operations
- `components/admin/SyncStatusBadge.tsx` - Status indicator badge component

### Utilities & Helpers
- `lib/utils/db-helpers.ts` - ✅ Database utility functions (bulk upsert, soft delete, sync stats, etc.)
- `lib/utils/sync-helpers.ts` - ✅ Sync utility functions (dependency ordering, entity helpers, filters)
- `lib/utils/entity-mapper.ts` - ✅ Map Autotask API responses to PostgreSQL schema format
- `lib/utils/logger.ts` - ✅ Structured logging utility for sync operations
- `lib/utils/api-helpers.ts` - ✅ API utilities for query parameter parsing, pagination, filtering, and error handling
- `lib/types/errors.ts` - ✅ Custom error types and error categorization utilities

### Testing
- `dev/test-postgres-connection.ts` - ✅ Test script for PostgreSQL connection and basic CRUD operations
- `dev/test-rate-limiter.ts` - ✅ Test script for rate limiter functionality with various scenarios
- `dev/test-full-sync.ts` - ✅ Test script for full sync with Autotask API (integration test)
- `dev/test-incremental-sync.ts` - ✅ Test script for incremental sync with modified records detection
- `dev/test-entity-specific-sync.ts` - ✅ Test script for entity-specific sync with dependency ordering
- `dev/ui-interaction-tests.md` - ✅ Comprehensive UI interaction test plan for admin sync interface
- `dev/ERROR_HANDLING_GUIDE.md` - ✅ Comprehensive guide for error handling and logging

### Notes

- Database migrations should be run automatically on PostgreSQL container startup via `/docker-entrypoint-initdb.d`
- Use existing `lib/services/autotask-client.ts` for Autotask API integration
- Use existing `lib/services/cache.ts` and `lib/services/redis-client.ts` for Redis caching
- All TypeScript files should include proper type definitions
- Follow existing project structure and naming conventions
- **Environment Configuration**: Both `.env` and `.env.local` are used:
  - `.env` - Loaded by docker-compose for variable substitution in docker-compose.yml
  - `.env.local` - Loaded by containers via `env_file` directive and by Next.js at runtime
  - Keep both files in sync for PostgreSQL credentials
- **Autotask API Data Types**: The API returns some fields as strings that were initially assumed to be integers/decimals:
  - `resource_type` returns "Employee", "Contractor" (not integer IDs)
  - `travel_availability_pct` returns "up to 25%", "up to 50%" (not decimal percentages)
  - Schema has been updated to accommodate actual API response formats
- **Autotask Query API**: Must use POST method (not GET) for `/query` endpoints with filter in request body
- **Date Range Filtering**: To manage rate limits and sync times, time-based entities (Tickets, Tasks, Projects, Billing Items) are limited by date range (default: 2 years). Users can adjust this in the admin UI from 1 year to "All Time" to sync historical data during off-hours.

## Tasks

- [ ] **1.0 Set up PostgreSQL infrastructure and database schema**
  - [x] 1.1 Add PostgreSQL service to `docker-compose.yml` with health checks, volumes, and environment variables
  - [x] 1.2 Create `.env.local` entries for PostgreSQL connection (host, port, database, user, password, DATABASE_URL)
  - [x] 1.3 Update `.env.example` with PostgreSQL configuration documentation
  - [x] 1.4 Create `migrations/` directory for SQL migration files
  - [x] 1.5 Create `migrations/001_initial_schema.sql` with all 13 entity tables (companies, tickets, tasks, projects, resources, statuses, issue_types, sub_issue_types, work_types, billing_items, configuration_items, contacts, contracts)
  - [x] 1.6 Add audit fields to each table (created_at, updated_at, synced_at, is_deleted, deleted_at)
  - [x] 1.7 Create `sync_history` table with all required fields (id, entity_type, sync_type, status, started_at, completed_at, records_added, records_updated, records_deleted, error_message, triggered_by)
  - [x] 1.8 Add foreign key constraints between related tables (tickets→companies, tasks→resources, configuration_items→companies, etc.)
  - [x] 1.9 Create `migrations/002_add_indexes.sql` with indexes on foreign keys and frequently queried fields (company_id, assigned_resource_id, status, is_deleted)
  - [x] 1.10 Test PostgreSQL container startup and migration execution

- [x] **2.0 Implement core sync service and Autotask API integration**
  - [x] 2.1 Install required dependencies (`pg`, `@types/pg`) via npm
  - [x] 2.2 Create `lib/services/postgres-client.ts` with connection pool setup and basic query methods
  - [x] 2.3 Create `lib/types/sync.ts` with TypeScript interfaces for SyncConfig, SyncStatus, SyncHistory, EntityType enum
  - [x] 2.4 Create `lib/types/database.ts` with TypeScript interfaces matching all database table schemas
  - [x] 2.5 Create `lib/services/rate-limiter.ts` implementing 10 requests/second throttling with queue
  - [x] 2.6 Create `lib/utils/entity-mapper.ts` to map Autotask API responses to PostgreSQL schema format
  - [x] 2.7 Create `lib/utils/sync-helpers.ts` with dependency ordering function (companies first, then tickets/tasks/etc.)
  - [x] 2.8 Create `lib/utils/db-helpers.ts` with upsert, soft delete, and bulk insert functions
  - [x] 2.9 Test PostgreSQL connection and basic CRUD operations
  - [x] 2.10 Test rate limiter with mock API calls

- [x] **3.0 Build sync operations (full, incremental, entity-specific)**
  - [x] 3.1 Create `lib/services/sync-service.ts` with main sync orchestration class
  - [x] 3.2 Implement `createSyncHistory()` method to create sync_history record with status 'started'
  - [x] 3.3 Implement `updateSyncHistory()` method to update sync progress and status
  - [x] 3.4 Create `lib/services/entity-sync.ts` with entity-specific sync methods for each of the 13 entities
  - [x] 3.5 Implement `syncCompanies()` - fetch all companies from Autotask, upsert to PostgreSQL
  - [x] 3.6 Implement `syncTickets()` - fetch all tickets, handle pagination, upsert with foreign keys
  - [x] 3.7 Implement `syncTasks()` - fetch all tasks, handle pagination, upsert with foreign keys
  - [x] 3.8 Implement `syncProjects()` - fetch all projects, upsert to PostgreSQL
  - [x] 3.9 Implement `syncResources()` - fetch all resources (users), upsert to PostgreSQL
  - [x] 3.10 Implement `syncConfigurationItems()` - fetch all config items, upsert with foreign keys
  - [x] 3.11 Implement `syncContacts()` - fetch all contacts, upsert with company foreign keys
  - [x] 3.12 Implement `syncContracts()` - fetch all contracts, upsert with company foreign keys
  - [x] 3.13 Implement `syncBillingItems()` - fetch all billing items, upsert to PostgreSQL
  - [x] 3.14 Implement `syncStatuses()` - fetch all status picklist values, upsert to PostgreSQL
  - [x] 3.15 Implement `syncIssueTypes()` - fetch all issue type picklist values, upsert to PostgreSQL
  - [x] 3.16 Implement `syncSubIssueTypes()` - fetch all sub-issue type picklist values, upsert to PostgreSQL
  - [x] 3.17 Implement `syncWorkTypes()` - fetch all work type picklist values, upsert to PostgreSQL
  - [x] 3.18 Integrate entity sync methods into main sync orchestration service
  - [x] 3.19 Add error handling and logging for each sync operation
  - [x] 3.20 Test full sync with small dataset from Autotask
  - [x] 3.21 Test incremental sync with modified records
  - [x] 3.22 Test entity-specific sync for individual entities

- [x] **4.0 Create admin UI for sync control and monitoring**
  - [x] 4.1 Create `app/admin/sync/page.tsx` as main admin sync page layout
  - [x] 4.2 Create `components/admin/EntitySelector.tsx` with checkboxes for all 13 entities
  - [x] 4.3 Create `components/admin/SyncControlPanel.tsx` with Full Sync, Incremental Sync, and Sync Selected buttons
  - [x] 4.4 Add sync mode toggle (full/incremental) to control panel for entity-specific syncs
  - [x] 4.5 Create `components/admin/SyncDashboard.tsx` showing last sync time per entity with status badges
  - [x] 4.6 Display total records synced (added, updated, deleted) in dashboard cards
  - [x] 4.7 Create `components/admin/SyncProgressBar.tsx` showing real-time sync progress (optional for MVP)
  - [x] 4.8 Create `components/admin/SyncStatusBadge.tsx` for status indicators (started, in_progress, completed, failed)
  - [x] 4.9 Create `components/admin/SyncHistoryTable.tsx` with paginated sync history from sync_history table
  - [x] 4.10 Add record count display (total, added, updated, deleted) to history table
  - [x] 4.11 Implement auto-refresh (every 5 seconds) for dashboard during active sync
  - [x] 4.12 Add confirmation dialog before triggering full sync
  - [x] 4.13 Add download logs functionality (export as JSON/CSV)
  - [x] 4.14 Style all components using TailwindCSS and shadcn/ui to match existing Pulse design
  - [x] 4.15 Test UI responsiveness on desktop and tablet
  - [x] 4.16 Test all user interactions (button clicks, entity selection, progress updates)

- [ ] **5.0 Implement API endpoints and data query layer**
  - [x] 5.1 Create `app/api/sync/full/route.ts` - POST endpoint accepting optional entities array, returns syncId
  - [x] 5.2 Create `app/api/sync/incremental/route.ts` - POST endpoint accepting optional entities array, returns syncId
  - [x] 5.3 Create `app/api/sync/entity/route.ts` - POST endpoint to trigger entity-specific sync with entity array in body
  - [x] 5.4 Create `app/api/sync/history/route.ts` - GET endpoint with pagination (page, limit) and entity filter
  - [x] 5.5 Create `app/api/sync/last-sync/route.ts` - GET endpoint returning last sync timestamp per entity
  - [x] 5.6 Create `app/api/data/companies/route.ts` - GET endpoint querying companies from PostgreSQL with pagination
  - [x] 5.7 Create `app/api/data/tickets/route.ts` - GET endpoint querying tickets with filters, pagination, includeDeleted option
  - [x] 5.8 Create `app/api/data/tasks/route.ts` - GET endpoint querying tasks with filters and pagination
  - [x] 5.9 Create `app/api/data/configuration-items/route.ts` - GET endpoint querying config items with company filter
  - [x] 5.10 Create `app/api/data/contacts/route.ts` - GET endpoint querying contacts with company filter
  - [x] 5.11 Create `app/api/data/contracts/route.ts` - GET endpoint querying contracts with pagination
  - [x] 5.12 Create `app/api/data/projects/route.ts` - GET endpoint querying projects with company filter
  - [x] 5.13 Create `app/api/data/resources/route.ts` - GET endpoint querying resources (users)
  - [x] 5.14 Create `app/api/data/billing-items/route.ts` - GET endpoint querying billing items with company filter
  - [x] 5.15 Add query parameter support for all data endpoints (page, limit, includeDeleted, filters, sort, order)
  - [x] 5.16 Implement default behavior to exclude soft-deleted records (is_deleted=false)
  - [ ] 5.17 Add authentication/authorization checks to all sync and data endpoints
  - [ ] 5.18 Test all API endpoints with Postman or similar tool
  - [ ] 5.19 Test pagination, filtering, and sorting functionality

- [ ] **6.0 Add error handling, logging, and notifications**
  - [ ] 6.1 Add comprehensive error logging to sync service (API errors, database errors, validation errors)
  - [ ] 6.2 Log all errors to sync_history table with full error message and stack trace
  - [ ] 6.3 Implement exponential backoff for Autotask API 429 (rate limit) responses
  - [ ] 6.4 Add error context logging (request details, response details, SQL query context)
  - [ ] 6.5 Implement toast notification component for success messages (using shadcn/ui toast)
  - [ ] 6.6 Implement toast notification component for error messages with error summary
  - [ ] 6.7 Add notification triggers in sync API endpoints (success/failure)
  - [ ] 6.8 Store error logs for minimum 90 days (add cleanup job or retention policy)
  - [ ] 6.9 Add Redis cache invalidation after successful sync (clear cached entities)
  - [ ] 6.10 Implement cache invalidation for affected entities only (not all cache)
  - [ ] 6.11 Update Redis cache strategy to use PostgreSQL data when available (fallback to API)
  - [ ] 6.12 Add sync cancellation functionality (admin can cancel running sync)
  - [ ] 6.13 Ensure partial sync progress is preserved on failure (committed transactions)
  - [ ] 6.14 Test error handling with various failure scenarios (network timeout, auth failure, constraint violation)
  - [ ] 6.15 Test notification display in UI for success and failure cases

- [ ] **7.0 Testing, documentation, and deployment**
  - [ ] 7.1 Write integration tests for sync service with test database
  - [ ] 7.2 Write unit tests for rate limiter functionality
  - [ ] 7.3 Write unit tests for entity mapper and sync helpers
  - [ ] 7.4 Write API endpoint tests for all sync and data routes
  - [ ] 7.5 Test full sync with production-like data volume (1000+ records per entity)
  - [ ] 7.6 Test incremental sync accuracy (verify only changed records are updated)
  - [ ] 7.7 Test entity-specific sync with various entity combinations
  - [ ] 7.8 Test soft delete functionality (verify records marked as deleted, not removed)
  - [ ] 7.9 Test foreign key relationships and data integrity
  - [ ] 7.10 Test rate limiting under high load (verify 10 req/sec limit)
  - [ ] 7.11 Test sync cancellation and partial progress preservation
  - [ ] 7.12 Perform load testing on PostgreSQL queries (verify <500ms response time)
  - [ ] 7.13 Create README documentation for sync feature (setup, usage, troubleshooting)
  - [ ] 7.14 Document all API endpoints with request/response examples
  - [ ] 7.15 Document database schema and entity relationships (ERD diagram)
  - [ ] 7.16 Create runbook for common sync issues and resolutions
  - [ ] 7.17 Update main project README with PostgreSQL setup instructions
  - [ ] 7.18 Build and test Docker containers locally
  - [ ] 7.19 Deploy to staging environment and perform end-to-end testing
  - [ ] 7.20 Deploy to production and monitor first sync operation
  - [ ] 7.21 Set up monitoring/alerting for sync failures (optional webhook integration)
  - [ ] 7.22 Create backup strategy for PostgreSQL data (automated backups)
uisng