PostgreSQL + TimescaleDB Database Design

Critical Rules

Non-negotiable. Any violation must be flagged immediately.

1. Never use SELECT *. List only needed columns.
2. Never use FLOAT / DOUBLE PRECISION for amounts. Use NUMERIC for exact precision. Blockchain amounts require exact arithmetic.
3. Every table must have a primary key.
4. Always index foreign key columns. PostgreSQL does NOT auto-create FK indexes.
5. Always use TIMESTAMPTZ, never TIMESTAMP WITHOUT TIME ZONE.
6. Always use parameterized queries. Never concatenate input into SQL.
7. Use CREATE INDEX CONCURRENTLY on live tables.
8. Use keyset (cursor) pagination, never OFFSET. This project uses cursor-based pagination.

TimescaleDB-Specific Rules

Hypertable Design

• Time-series tables (transfer_events, balance_snapshots, webhook_deliveries) MUST be hypertables
• Partition by time column: SELECT create_hypertable('table', 'time_column', chunk_time_interval => INTERVAL '7 days')
• Choose chunk interval based on query patterns: 7 days for transfer_events, 1 day for high-frequency data
• Unique constraints on hypertables MUST include the partitioning column

Continuous Aggregates

• Use for pre-computed rollups (daily metrics, volume summaries)
• Always use WITH (timescaledb.continuous) materialized views
• Set refresh policies: SELECT add_continuous_aggregate_policy(...)
• Avoid real-time aggregation on hypertables when continuous aggregates can serve the query

Compression

• Enable compression on hypertables older than N days: ALTER TABLE ... SET (timescaledb.compress)
• Choose segment_by columns wisely (chain_id, token_symbol for transfer_events)
• Order by time column for best compression ratio

Rules by Category

PG-001: Write Efficient SQL

• Bulk operations replace loops
• Existence checks use EXISTS, not COUNT(*)
• Pagination uses cursor (keyset), not OFFSET
• R2DBC queries should use DatabaseClient for complex queries, ReactiveCrudRepository for simple CRUD

PG-002: Use Correct Native Data Types

PG-003: Index Strategy for Blockchain Data

Common query patterns and their indexes:

-- Transfer lookups by address (most common query)
CREATE INDEX idx_transfer_events_to_address ON transfer_events (to_address, event_timestamp DESC);
CREATE INDEX idx_transfer_events_from_address ON transfer_events (from_address, event_timestamp DESC);

-- Filter by chain + token
CREATE INDEX idx_transfer_events_chain_token ON transfer_events (chain_id, token_symbol, event_timestamp DESC);

-- Deduplication check
CREATE UNIQUE INDEX idx_transfer_events_unique ON transfer_events (tx_hash, chain_id, log_index, event_timestamp);
-- Note: unique index on hypertable must include partition column (event_timestamp)

PG-004: R2DBC Query Patterns

Since we use R2DBC (not JPA), queries are written differently:

// GOOD: DatabaseClient for complex queries with cursor pagination
databaseClient.sql("""
    SELECT id, tx_hash, from_address, to_address, amount, classification, event_timestamp
    FROM transfer_events
    WHERE to_address = :address
    AND event_timestamp < :cursor_timestamp
    ORDER BY event_timestamp DESC
    LIMIT :limit
    """)
    .bind("address", address)
    .bind("cursor_timestamp", cursorTimestamp)
    .bind("limit", limit + 1)
    .map(row -> ...)
    .all();

// GOOD: ReactiveCrudRepository for simple CRUD
public interface WebhookRegistrationR2dbcRepository
    extends ReactiveCrudRepository<WebhookRegistrationEntity, UUID> {
    Flux<WebhookRegistrationEntity> findByStatus(String status);
}

PG-005: Migration Best Practices

• Flyway runs via JDBC (blocking) at startup — this is the one blocking operation allowed
• R2DBC URL is separate from Flyway JDBC URL in config
• TimescaleDB extension must be created in V1: CREATE EXTENSION IF NOT EXISTS timescaledb
• Hypertable creation in same migration as table creation
• Continuous aggregates in separate migration (they depend on the base table)

Output Format

# PostgreSQL/TimescaleDB Review: {scope}

## Summary
{1-2 sentence overall assessment}

## Findings

### VIOLATION: {title}
- **Location**: `path/to/file.sql:line`
- **Rule**: PG-{NNN} — {rule name}
- **Issue**: {what's wrong}
- **Fix**: {corrected SQL}

### WARNING: {title}
- **Location**: `path/to/file.sql:line`
- **Rule**: PG-{NNN}
- **Issue**: {concern}
- **Suggestion**: {recommended change}

## Checklist
- [ ] Every table has a primary key
- [ ] Every FK column has an index
- [ ] No SELECT *
- [ ] No FLOAT/DOUBLE for amounts — NUMERIC used
- [ ] TIMESTAMPTZ used everywhere
- [ ] Time-series tables are hypertables
- [ ] Hypertable unique indexes include partition column
- [ ] Continuous aggregates for rollup queries
- [ ] Cursor pagination, not OFFSET
- [ ] R2DBC queries use parameterized binding
- [ ] Migrations use CONCURRENTLY for indexes on live tables
- [ ] Block numbers use BIGINT

Severity Definitions

• VIOLATION: Breaks a critical rule. Must be fixed before merge.
• WARNING: Introduces risk or deviates from best practice. Should be fixed.
• SUGGESTION: Improvement opportunity. Nice to have.