Construction Site Sensor Partitioning Script

Role: Principal Site Reliability Engineer (Database Infrastructure) specializing in heavy civil engineering telematics.

Context

• Database technology stack: {{database_flavor}}
• Sensor readings target table: {{telemetry_table_name}}
• Temporal partition window: {{partition_interval_unit}}
• Active hot storage threshold: {{active_retention_days}}
• Long-term cold archive endpoint: {{cold_archive_location}}
• Operational alert webhook: {{alert_webhook_url}}

Task

Deliver an automated shell and SQL maintenance script that provisions forward partitions for {{telemetry_table_name}}, detaches aged partitions exceeding {{active_retention_days}}, compresses them, and moves historical site telemetry to {{cold_archive_location}}.

Method

1. Establish database connection variables, lock timeout limits, and failure handling parameters for {{database_flavor}}.
2. Calculate future time boundaries and generate upcoming partition tables dynamically based on {{partition_interval_unit}}.
3. Query metadata catalogs to identify telemetry tables older than {{active_retention_days}}.
4. Detach identified historical partitions without holding exclusive metadata table locks during peak construction hours.
5. Export detached partitions into columnar parquet or compressed dump files targeted for {{cold_archive_location}}.
6. Drop or truncate detached partitions once export integrity is checksum-verified.
7. Dispatch execution status and telemetry volume metrics to {{alert_webhook_url}} upon script completion or error.

Constraints

• MUST include explicit LOCK_TIMEOUT settings to prevent blocking continuous IoT sensor writes.
• MUST NOT drop any raw data partition prior to receiving an archival storage success response.
• All database commands MUST execute idempotently to tolerate scheduled cron retries.
• Script must contain error traps that notify {{alert_webhook_url}} immediately upon failure.

Output format

A unified Bash script encapsulating embedded SQL commands, divided into:

1. Configuration Variables and Safety Flags (max 25 lines)
2. Forward Partition Provisioning Function (max 45 lines)
3. Historical Partition Detach and Export Routine (max 50 lines)
4. Retention Cleanup and Metric Dispatcher (max 35 lines)

Self-review

• Check that all partition naming conventions follow standard UTC date suffix patterns.
• Validate that lock acquisition timeouts are set low enough to prevent queuing live sensor feeds.
• Verify error exit codes and webhook alert payloads contain actionable failure context.