Smart Meter Database Schema Hardening Checklist

Role: Lead Database Administrator & Security Engineer for Utility Billing and Meter Data Systems.

Context

• Database Engine: {{meter_data_database_type}}
• Daily Ingestion Volume: {{daily_meter_read_volume}}
• Cryptographic Protocol: {{encryption_standard}}
• Partitioning Method: {{partition_strategy}}
• Billing Calculation Window: {{billing_cycle_sla}}
• Customer Data Types: {{pii_data_types}}

Task

Produce an actionable database hardening and indexing review checklist to ensure the smart meter schema can withstand high-concurrency ingestion during the {{billing_cycle_sla}} window while enforcing strict data security over {{pii_data_types}}.

Method

1. Analyze table partition boundaries under {{partition_strategy}} to avoid cross-partition query scans during peak bill runs.
2. Review primary key, composite index, and foreign key definitions across high-volume meter read tables in {{meter_data_database_type}}.
3. Verify that column-level and tablespace-level encryption using {{encryption_standard}} does not degrade writes during {{daily_meter_read_volume}} peaks.
4. Design explicit checks for row-level security and access control lists protecting {{pii_data_types}}.
5. Audit autovacuum, statistics collection, and index bloat mitigation routines for high-churn interval tables.
6. Evaluate dead-lock probability and lock escalations during concurrent ingestion and settlement batch jobs.
7. Construct a validation checklist for database maintenance scripts, role-based grants, and backup immutability.

Constraints

• Checkpoints MUST explicitly address performance impacts on the {{billing_cycle_sla}} deadline.
• All security checklist items MUST reference {{encryption_standard}} and protection of {{pii_data_types}}.
• You MUST NOT recommend turning off integrity constraints or foreign keys to gain write speed.
• Checklist items MUST use the markdown task format - [ ].

Output format

Structure the output checklist into three distinct sections:

1. Indexing & Partitioning Integrity (4-6 checklist items)
2. Security, Access Control & Encryption (4-6 checklist items)
3. Concurrency & High-Volume Maintenance (4-5 checklist items) Each item must follow: - [ ] [Category] Task detail | Risk Addressed: <risk statement>.

Self-review

• Ensure all variables ({{meter_data_database_type}}, {{daily_meter_read_volume}}, {{encryption_standard}}, {{partition_strategy}}, {{billing_cycle_sla}}, {{pii_data_types}}) are applied in context.
• Verify that each section contains the required number of actionable markdown checklist items.
• Check that no generic placeholders exist and formatting follows the required schema.