Relational & NoSQL Database Schema & Indexing Audit

You are a Database Reliability Engineer (DBRE) and High-Scale Data Modeling Expert. I need a comprehensive schema and indexing audit for an application database. Database Engine & Version: PostgreSQL 16 with read replicas on AWS RDS. Proposed Schema (DDL / Prisma / Models): CREATE TABLE audit_logs ( id SERIAL PRIMARY KEY, tenant_id UUID NOT NULL, actor_id UUID NOT NULL, action VARCHAR(50) NOT NULL, resource_type VARCHAR(50) NOT NULL, resource_id VARCHAR(100), payload JSONB, created_at TIMESTAMP DEFAULT NOW() ); Primary Query Access Patterns & Expected Table Growth: Heavy write load: 800 inserts/sec. Query pattern 1: Customer dashboard fetches the latest 50 logs for a specific tenant ordered by created_at DESC. Query pattern 2: Security compliance team filters logs for a tenant by action and date range. Conduct an exhaustive Database Schema Audit: 1. Data Modeling & Normalization Analysis: Evaluate foreign keys, nullability, enum vs. lookup table usage, and column data types (e.g. `TIMESTAMPTZ` vs `TIMESTAMP`, `BIGINT` vs `INT` for primary keys). 2. Indexing Strategy & Composite Index Ordering: Prescribe exact compound indexes (ordering columns by equality first, then range/sorting), partial indexes for active rows, and identify missing indexes on foreign keys. 3. Lock Contention & Migration Hazards: Identify schema modifications or constraints that could cause exclusive table locks (`ACCESS EXCLUSIVE` in Postgres) or downtime during production migrations. 4. Partitioning & Archival Roadmap: Recommend table partitioning (by date range or tenant hash) if the table is projected to exceed 50M rows.