trading-platform-database-v2/migrations/README-timestamp-standardization.md

# Timestamp Standardization Plan

## Issue: CONF-004
Some tables use TIMESTAMP instead of TIMESTAMPTZ, which can cause timezone issues when dealing with distributed systems and multiple time zones.

## Standard
All timestamp columns **MUST** use `TIMESTAMPTZ` (timestamp with time zone) to ensure proper timezone handling and consistency across the platform.

### Why TIMESTAMPTZ?
- Preserves timezone information in the database
- Prevents ambiguity when dealing with DST (Daylight Saving Time)
- Enables accurate time-based queries across different regions
- Required for financial and trading data accuracy
- Critical for audit logs and compliance

## Current Status: COMPLIANT ✓

**Last Audit:** 2026-02-03

After comprehensive audit of all DDL files in the trading-platform database schema, we found that **ALL timestamp columns are already using TIMESTAMPTZ**.

### Audited Schemas (31 total)
All columns across all schemas conform to the TIMESTAMPTZ standard:
- `auth` schema (11 tables)
- `audit` schema (7 tables)
- `education` schema (19 tables)
- `feature_flags` schema
- `financial` schema (11 tables)
- `investment` schema (10 tables)
- `llm` schema (5 tables)
- `market_data` schema (4 tables)
- `ml` schema (12 tables)
- `portfolio` schema (5 tables)
- `trading` schema (13 tables)

### Timestamp Columns Verified
The following timestamp column patterns were verified across all tables:

1. **System Timestamps:**
   - `created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
   - `updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
   - `deleted_at TIMESTAMPTZ` (for soft deletes)

2. **Business Timestamps:**
   - `expires_at TIMESTAMPTZ`
   - `verified_at TIMESTAMPTZ`
   - `processed_at TIMESTAMPTZ`
   - `completed_at TIMESTAMPTZ`
   - `failed_at TIMESTAMPTZ`
   - `reviewed_at TIMESTAMPTZ`
   - `filled_at TIMESTAMPTZ`
   - `cancelled_at TIMESTAMPTZ`
   - `locked_until TIMESTAMPTZ`
   - `suspended_at TIMESTAMPTZ`
   - `deactivated_at TIMESTAMPTZ`

3. **Data Timestamps:**
   - `timestamp TIMESTAMPTZ` (market data, OHLCV)
   - `last_login_at TIMESTAMPTZ`
   - `email_verified_at TIMESTAMPTZ`
   - `phone_verified_at TIMESTAMPTZ`

## Migration Strategy

### Phase 1: Monitoring (Ongoing)
- Monitor new DDL files for timestamp compliance
- Add pre-commit hook validation (optional enhancement)
- Document any edge cases discovered

### Phase 2: Documentation (COMPLETE)
- ✓ Create this standardization plan
- ✓ Document current state and audit results
- ✓ Create migration template for future use

### Phase 3: Enforcement (Future)
If any new tables or modifications are needed:
1. Use the provided migration template
2. Create new column with TIMESTAMPTZ type
3. Copy data with timezone conversion
4. Drop old column and rename new
5. Update application code if needed
6. Test thoroughly in staging

## Reference Examples

### Audit Results by Table Type

#### Audit Tables
- `audit.audit_logs.created_at` → TIMESTAMPTZ ✓
- `audit.security_events.created_at` → TIMESTAMPTZ ✓
- `audit.compliance_logs.created_at` → TIMESTAMPTZ ✓
- `audit.compliance_logs.reviewed_at` → TIMESTAMPTZ ✓
- `audit.api_request_logs.created_at` → TIMESTAMPTZ ✓

#### Authentication Tables
- `auth.users.created_at` → TIMESTAMPTZ ✓
- `auth.users.email_verified_at` → TIMESTAMPTZ ✓
- `auth.users.last_login_at` → TIMESTAMPTZ ✓
- `auth.email_verifications.expires_at` → TIMESTAMPTZ ✓
- `auth.sessions.expires_at` → TIMESTAMPTZ ✓

#### Financial/Trading Tables
- `financial.wallet_transactions.created_at` → TIMESTAMPTZ ✓
- `financial.wallet_transactions.processed_at` → TIMESTAMPTZ ✓
- `financial.wallet_transactions.completed_at` → TIMESTAMPTZ ✓
- `trading.orders.created_at` → TIMESTAMPTZ ✓
- `trading.orders.filled_at` → TIMESTAMPTZ ✓
- `trading.orders.cancelled_at` → TIMESTAMPTZ ✓

#### Market Data Tables
- `market_data.ohlcv_5m.timestamp` → TIMESTAMPTZ ✓
- `market_data.ohlcv_5m.created_at` → TIMESTAMPTZ ✓
- `market_data.ohlcv_15m.timestamp` → TIMESTAMPTZ ✓

#### ML/Investment Tables
- `ml.predictions.created_at` → TIMESTAMPTZ ✓
- `investment.transactions.created_at` → TIMESTAMPTZ ✓

## Migration Template

See: `2026-02-03_standardize_timestamps_template.sql`

## Best Practices

1. **Always use TIMESTAMPTZ** for timestamp columns
2. **Default to NOW()** for automatic current timestamp
3. **Use UTC** internally in application code
4. **Convert to user timezone** in UI layer
5. **Document timezone handling** in application code
6. **Test DST transitions** in your time conversion logic

## Validation Queries

To verify TIMESTAMPTZ compliance on any table:

```sql
-- Check all timestamp columns in a table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'schema_name'
  AND table_name = 'table_name'
  AND data_type LIKE '%timestamp%'
ORDER BY ordinal_position;

-- Verify entire database compliance
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type LIKE '%timestamp%'
ORDER BY table_schema, table_name;
```

## Priority: P0 (Complete - No Action Required)

This is a documentation of the existing compliant state. All code adheres to TIMESTAMPTZ standards.

## Change Log

| Date | Change | Status |
|------|--------|--------|
| 2026-02-03 | Initial audit and documentation | COMPLETE |

## Related Documentation

- PostgreSQL TIMESTAMPTZ docs: https://www.postgresql.org/docs/current/datatype-datetime.html
- @ESTANDAR-DATABASE: docs/40-estandares/ESTANDAR-DATABASE-PROFESIONAL.md
- @TRIGGER-COHERENCIA: orchestration/directivas/triggers/TRIGGER-COHERENCIA-CAPAS.md