Driving Impact and Delivering Results Questions

Approach: clip each incident to the week bounds, then per service/week merge overlapping intervals and sum their durations. Uptime% = (1 - downtime_minutes / 10080) * 100.

WITH weeks AS (
  -- generate weeks covering data range (adjust range as needed)
  SELECT generate_series(date_trunc('week', min(start_ts))::timestamp,
                         date_trunc('week', max(end_ts))::timestamp,
                         interval '1 week') AS week_start
  FROM incidents
),
clipped AS (
  -- clip incidents to each week they intersect
  SELECT
    w.week_start,
    i.service_id,
    GREATEST(i.start_ts, w.week_start)                 AS s,
    LEAST(i.end_ts, w.week_start + interval '7 days')  AS e
  FROM incidents i
  JOIN weeks w
    ON i.start_ts < w.week_start + interval '7 days'
   AND i.end_ts   > w.week_start
),
ordered AS (
  -- order per service/week for merging
  SELECT
    week_start,
    service_id,
    s AS start_ts,
    e AS end_ts,
    LAG(e) OVER (PARTITION BY week_start, service_id ORDER BY s, e) AS prev_end
  FROM clipped
),
flagged AS (
  -- mark new groups where current start is after previous end
  SELECT *,
    CASE WHEN prev_end IS NULL OR start_ts > prev_end THEN 1 ELSE 0 END AS new_group
  FROM ordered
),
grp AS (
  -- cumulative group id to merge overlaps/adjacent intervals
  SELECT *,
    sum(new_group) OVER (PARTITION BY week_start, service_id ORDER BY start_ts, end_ts) AS grp_id
  FROM flagged
),
merged AS (
  -- merge intervals per group
  SELECT
    week_start,
    service_id,
    MIN(start_ts) AS m_start,
    MAX(end_ts)   AS m_end
  FROM grp
  GROUP BY week_start, service_id, grp_id
),
downtime AS (
  -- total downtime minutes per service/week
  SELECT
    week_start,
    service_id,
    SUM(EXTRACT(EPOCH FROM (m_end - m_start)) / 60.0) AS downtime_minutes
  FROM merged
  GROUP BY week_start, service_id
)
SELECT
  w.week_start,
  s.service_id,
  ROUND((1 - COALESCE(d.downtime_minutes, 0) / 10080.0) * 100, 4) AS uptime_percentage
FROM (
  -- ensure we return rows for all service/week combinations (even 100% uptime)
  SELECT DISTINCT week_start, service_id
  FROM weeks CROSS JOIN (SELECT DISTINCT service_id FROM incidents) sv(service_id)
) w
LEFT JOIN downtime d
  ON w.week_start = d.week_start AND w.service_id = d.service_id
ORDER BY w.week_start, w.service_id;

Key points:

• Clip incidents to week boundaries so partial-week incidents contribute only their overlap.
• Merge overlapping/adjacent intervals per service/week using window functions (LAG + cumulative sum) then aggregate to compute distinct downtime.
• Use 10080 total minutes per week; uptime% = (1 - downtime/10080)*100.
  Edge cases:
• Incidents spanning many weeks are split by the clip join.
• Services with no incidents that week return 100% uptime (downtime NULL handled with COALESCE).
• If you need inclusive/exclusive bounds for exact-minute handling, adjust comparisons (<= vs <) accordingly.

Approach (short): compute for each service a rolling 30-day window of total downtime (sum of incident overlap with that window, after coalescing overlapping incidents), convert to uptime = 1 - downtime/30d, and produce a 95% CI for the uptime proportion using the Wilson score or normal approximation on time-weighted “successes” (treat uptime seconds as successes out of total seconds). Flag services with point estimate < 0.999. Assumptions: timestamps in UTC, incidents may overlap (we coalesce), deployments table used only if you want deployment-aligned windows; here we compute calendar rolling windows per service (daily series). For high-precision, compute in seconds.

Postgres query (per service, rolling window ending at now()):

WITH params AS (
  SELECT now() AS window_end, now() - interval '30 days' AS window_start
),
-- normalize and coalesce incident intervals per service
norm AS (
  SELECT service_id,
         start_ts,
         end_ts
  FROM incidents
  WHERE end_ts > (SELECT window_start FROM params) AND start_ts < (SELECT window_end FROM params)
),
coalesced AS (
  SELECT service_id,
         MIN(start_ts) AS s,
         MAX(end_ts)   AS e
  FROM (
    SELECT service_id,
           start_ts,
           end_ts,
           sum(new_group) OVER (PARTITION BY service_id ORDER BY start_ts, end_ts) grp
    FROM (
      SELECT service_id, start_ts, end_ts,
             CASE WHEN lag(end_ts) OVER (PARTITION BY service_id ORDER BY start_ts) >= start_ts THEN 0 ELSE 1 END AS new_group
      FROM norm
    ) t1
  ) t2
  GROUP BY service_id, grp
),
-- compute overlap seconds per coalesced incident with window
overlap AS (
  SELECT service_id,
         EXTRACT(EPOCH FROM (LEAST(e, (SELECT window_end FROM params)) - GREATEST(s, (SELECT window_start FROM params)))) AS downtime_seconds
  FROM coalesced
  WHERE e > (SELECT window_start FROM params) AND s < (SELECT window_end FROM params)
),
agg AS (
  SELECT d.service_id,
         COALESCE(SUM(o.downtime_seconds), 0) AS total_downtime_seconds,
         EXTRACT(EPOCH FROM ((SELECT window_end FROM params) - (SELECT window_start FROM params))) AS window_seconds
  FROM (SELECT DISTINCT service_id FROM deployments UNION SELECT DISTINCT service_id FROM incidents) d
  LEFT JOIN overlap o USING (service_id)
  GROUP BY d.service_id
)
SELECT
  service_id,
  (1 - total_downtime_seconds::double precision / window_seconds) AS uptime,
  -- Wilson score interval treating uptime_seconds as successes out of window_seconds (approx)
  ( (phat := (1 - total_downtime_seconds::double precision / window_seconds)) ) AS phat,
  (
    (phat + z*z/(2*window_seconds) - z*sqrt( (phat*(1-phat) + z*z/(4*window_seconds)) / window_seconds ))
  ) AS ci_lower,
  (
    (phat + z*z/(2*window_seconds) + z*sqrt( (phat*(1-phat) + z*z/(4*window_seconds)) / window_seconds ))
  ) AS ci_upper,
  (1 - total_downtime_seconds::double precision / window_seconds) < 0.999 AS flag_below_99_9
FROM (
  SELECT *, 1.96 AS z FROM agg
) q;

(Note: Postgres requires the phat and z expressions written inline or via lateral; for clarity variables shown — translate to valid SQL accordingly.)

Python alternative (compute per service; handles coalescing and Wilson CI):

from datetime import datetime, timedelta
from math import sqrt

def coalesce(intervals):
    intervals = sorted(intervals)
    out=[]
    for s,e in intervals:
        if not out or s>out[-1][1]:
            out.append([s,e])
        else:
            out[-1][1]=max(out[-1][1], e)
    return out

def uptime_and_wilson(intervals, window_end=None):
    window_end = window_end or datetime.utcnow()
    window_start = window_end - timedelta(days=30)
    total_window = (window_end - window_start).total_seconds()
    # intervals: list of (start, end)
    clipped = []
    for s,e in intervals:
        if e<=window_start or s>=window_end: continue
        clipped.append((max(s,window_start), min(e,window_end)))
    co = coalesce(clipped)
    downtime = sum((e-s).total_seconds() for s,e in co)
    up = 1 - downtime/total_window
    # Wilson score for proportion p = up (treat seconds as n)
    n = total_window
    p = up
    z = 1.96
    denom = 1 + z*z/n
    center = p + z*z/(2*n)
    margin = z * sqrt((p*(1-p) + z*z/(4*n)) / n)
    lower = (center - margin) / denom
    upper = (center + margin) / denom
    return up, lower, upper

Notes, trade-offs:

• We approximate CI treating seconds as Bernoulli trials; valid for large n (30 days -> many seconds), Wilson is conservative and numerically stable.
• For streaming or per-deployment aligned SLAs, compute rolling windows per timestamp (generate_series) and store pre-aggregated downtime per service per minute for performance.
• Coalescing is critical to avoid double-counting overlapping incidents.
• Use partitioned tables and pre-aggregates for scale.

Requirements & constraints:

• Zero-downtime for many services, overall error rate must stay <1%.
• Deploy major schema change across dozens of services with independent release schedules.
• Ability to safely roll forward/backward, verify data correctness, and monitor real-time.

Strategy overview: Use expand-then-contract schema migrations + feature-flagged application changes + phased rollout with canaries and automated safeguards.

Migration pattern (expand → backfill → switch → contract):

1. Expand: Add new columns/tables/etc. that are additive and backward-compatible (nullable columns, new tables, new indices). Avoid destructive operations.
2. Dual-write/backfill: Introduce dual-write or shadow-write at application/service layer (write to old and new schema) or run background backfill jobs that populate new fields from existing data.
3. Read-path feature flag: Deploy app code that reads from the old schema by default; behind a feature flag enable reads from the new schema for a small canary percentage.
4. Verify: Run end-to-end validation and reconciliation jobs comparing old vs new reads (row counts, checksums, business-critical aggregates).
5. Switch: Incrementally increase traffic to new read path to 100% once validation metrics pass.
6. Contract: After a safe wait and verification, remove old columns/tables in a separate, planned migration.

Deployment coordination and governance:

• Define schema owners, a migration runbook, and a change window if needed.
• Use migration tooling (Flyway/Liquibase/Sqitch or DB-native) that supports transactional, idempotent migrations and versioning.
• Coordinate service deployments via a release orchestrator (Spinnaker/ArgoCD/Jenkins pipelines) with the ability to stage per-service toggles.
• Maintain a compatibility matrix mapping which services need code changes vs only DB changes.

Feature-flag strategy:

• Use robust feature-flag system (e.g., LaunchDarkly, Flagsmith) with:
  • Canary flags for a small % of users/services.
  • Targeting by service, region, or user cohort.
  • Quick kill switch and audit logs.
• Separate flags for read-path, write-path (dual-write), and backfill control.

Testing:

• Unit & integration tests in CI that validate migrations against schema fixtures.
• Contract/consumer-driven tests (PACT) between services to ensure new schema satisfies consumers.
• Staging environment with production-sized subset of data (sanitized) to run:
  • Shadow traffic replay tests.
  • Backfill and reconciliation jobs.
  • Load tests focusing on new indices and query plans.
• Preflight migrations dry-run and explain-plan checks to detect long-running queries.
• Chaos tests: flip flags and simulate partial failures to validate rollback.

Monitoring & validation:

• Instrument metrics: application error rate, 5xx counts, latency, DB error/retry rates, per-service success/failure rates, backfill progress, reconciliation diffs (row-level checksum mismatches), index usage and lock contention.
• Business KPIs: conversion rates, queue depth, downstream success metrics.
• Alerts with thresholds: immediate rollback if error rate >0.5% for 5 mins or >1% for 1 min (tunable based on SLA).
• Real-time dashboards and automated canary analysis to compare canary vs baseline.

Rollback & safe recovery:

• Prefer configuration rollback over DB rollback: flip feature flags to route reads/writes back to old schema instantly.
• Keep old schema until contract phase; avoid destructive operations in the same release.
• If data corruption is detected:
  • Pause writes (via flag/circuit-breaker), switch to read-only if possible.
  • Revert application flags to old path; continue serving from the stable schema.
  • Run reconciliation and repair scripts; use point-in-time backups for severe cases.
• For irreversible steps (e.g., dropping columns), require explicit sign-off and scheduled maintenance window with backups and tested restore plan.

Operational runbook (short):

• Predeploy: migration dry-run, backup snapshot, notify stakeholders.
• Deploy step 1: apply expand migration; monitor DB.
• Deploy step 2: enable dual-write in canary services; run backfill.
• Deploy step 3: enable read-from-new for small canary; monitor metrics for 30–60 min.
• If metrics stable, incrementally widen rollout; else flip flags back and investigate.
• Postdeploy: full verification, schedule contract migration in future.

Trade-offs:

• Dual-write increases complexity and transient inconsistency risk—mitigated by reconciliation jobs.
• Keeping old schema longer increases maintenance cost but dramatically reduces rollback risk.

This approach minimizes blast radius using additive changes, feature flags, canary rollouts, thorough testing, and clear monitoring + runbooks so you can safely deploy and rollback while keeping errors under 1%.