Task S5T01: stats.hourly_activity Table¶

Sprint Context¶

• Sprint index: Sprint 5 Task Index
• Tags: database, database-refactor, sprint-5, task-index

Source¶

• DBA source: task-S5T01.dba.md
• Specification source: task-S5T01.specification.md

Purpose & Scope¶

Creates stats.hourly_activity, which aggregates move counts by calendar date, UTC hour (0–23), and move type (0–5). This is the sub-daily granularity complement to Sprint 2's daily_move_counts. It enables:

• UC8: Seasonal/time-of-day heatmap ("when do people log moves?")
• Peak usage hour analytics
• Move type distribution per hour

The table is append-optimized: each unique (activity_date, hour_utc, move_type) triplet has exactly one row, incremented by trigger.

Stores sub-daily, per-move-type counters indexed by (date, hour_utc, move_type). Each cell holds the total count of moves of a given type logged during a specific hour on a specific date. The table powers the UC8 time-of-day heatmap and peak usage analytics.

Scope: DDL only. Trigger population is in S5T07 (covers Sprint 5 analytics triggers). Backfill in Sprint 6.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310500000_create_hourly_activity.php

Full SQL DDL¶

CREATE TABLE stats.hourly_activity (
  activity_date  DATE       NOT NULL,
  hour_utc       SMALLINT   NOT NULL,
  move_type      SMALLINT   NOT NULL,
  move_count     BIGINT     NOT NULL DEFAULT 0,
  PRIMARY KEY (activity_date, hour_utc, move_type),
  CONSTRAINT chk_hourly_activity_hour   CHECK (hour_utc   BETWEEN 0 AND 23),
  CONSTRAINT chk_hourly_activity_mtype  CHECK (move_type  BETWEEN 0 AND 5)
);

COMMENT ON TABLE stats.hourly_activity
  IS 'Aggregate move count by date, UTC hour (0-23), and move type; powers UC8 heatmap';
COMMENT ON COLUMN stats.hourly_activity.activity_date
  IS 'Calendar date (UTC) of the moves';
COMMENT ON COLUMN stats.hourly_activity.hour_utc
  IS 'Hour of day in UTC (0=midnight, 23=11pm)';
COMMENT ON COLUMN stats.hourly_activity.move_type
  IS 'Move type: 0=DROP, 1=GRAB, 2=COMMENT, 3=SEEN, 4=ARCHIVE, 5=DIP';
COMMENT ON COLUMN stats.hourly_activity.move_count
  IS 'Number of moves of this type logged at this date/hour';

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateHourlyActivity extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE TABLE stats.hourly_activity (
  activity_date  DATE       NOT NULL,
  hour_utc       SMALLINT   NOT NULL,
  move_type      SMALLINT   NOT NULL,
  move_count     BIGINT     NOT NULL DEFAULT 0,
  PRIMARY KEY (activity_date, hour_utc, move_type),
  CONSTRAINT chk_hourly_activity_hour   CHECK (hour_utc   BETWEEN 0 AND 23),
  CONSTRAINT chk_hourly_activity_mtype  CHECK (move_type  BETWEEN 0 AND 5)
);

COMMENT ON TABLE stats.hourly_activity
  IS 'Aggregate move count by date, UTC hour (0-23), and move type; powers UC8 heatmap';
SQL
        );
    }

    public function down(): void
    {
        $this->execute('DROP TABLE IF EXISTS stats.hourly_activity;');
    }
}

Data Contract¶

Constraints:

• chk_hourly_activity_hour: hour_utc BETWEEN 0 AND 23
• chk_hourly_activity_mtype: move_type BETWEEN 0 AND 5

SQL Usage Examples¶

-- UC8: Heatmap — total moves per hour across all dates (last 12 months)
SELECT hour_utc, SUM(move_count) AS total
FROM stats.hourly_activity
WHERE activity_date >= NOW() - INTERVAL '12 months'
GROUP BY hour_utc
ORDER BY hour_utc;

-- UC8: Move type distribution by hour (last 30 days)
SELECT hour_utc, move_type, SUM(move_count) AS total
FROM stats.hourly_activity
WHERE activity_date >= NOW() - INTERVAL '30 days'
GROUP BY hour_utc, move_type
ORDER BY hour_utc, move_type;

-- Peak days
SELECT activity_date, SUM(move_count) AS daily_total
FROM stats.hourly_activity
GROUP BY activity_date
ORDER BY daily_total DESC
LIMIT 10;

TimescaleDB Assessment¶

CANDIDATE for hypertable (time dimension: activity_date). With years of data and potentially 24×6 = 144 rows per day, volume grows manageable but chunk-based queries would benefit. Chunk by activity_date 1-month intervals.

-- Optional hypertable conversion (run after table creation and before loading data):
SELECT create_hypertable('stats.hourly_activity', 'activity_date',
  chunk_time_interval => INTERVAL '1 month',
  if_not_exists => TRUE
);

Trade-off: TimescaleDB hypertables break standard ON CONFLICT DO UPDATE for some PostgreSQL versions. If using vanilla PostgreSQL, standard table is sufficient given small row volume (~144 rows/day max).

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create migration 20260310500000_create_hourly_activity.php
• 2. phinx migrate — no errors
• 3. \d stats.hourly_activity — 4 columns, 3-part PK, 2 CHECKs
• 4. Test hour_utc = 24 violation
• 5. Test move_type = 6 violation
• 6. Run pgTAP T-5.1.001 through T-5.1.010 — all pass

• 7. phinx rollback — table gone

• 1. Write 20260310500000_create_hourly_activity.php

• 2. phinx migrate — no errors
• 3. 4 columns with correct types and 3-part PK
• 4. Both CHECK constraints verified
• 5. Run pgTAP T-5.1.001 through T-5.1.010 — all pass
• 6. phinx rollback — table gone

Table Created¶

stats.hourly_activity (activity_date, hour_utc, move_type, move_count)