timeclock/migrate_db.sh

#!/usr/bin/env bash

#
# SQLite Timestamp Migration
#
# Converts legacy naive timestamps:
#
#   2026-05-29 08:15:00
#
# into timezone-aware ISO-8601 timestamps:
#
#   2026-05-29T08:15:00-04:00
#
# Existing timezone-aware timestamps are preserved.
#

set -euo pipefail

DATABASE="${1:-database/timeclock.db}"

#
# Timezone offset to apply to old naive timestamps.
#
# Examples:
#   -04:00   Eastern Daylight Time
#   -05:00   Eastern Standard Time
#   +00:00   UTC
#
DEFAULT_OFFSET="-04:00"

echo "========================================="
echo "Timeclock Timestamp Migration"
echo "========================================="
echo
echo "Database: ${DATABASE}"
echo "Assumed legacy offset: ${DEFAULT_OFFSET}"
echo

if [ ! -f "${DATABASE}" ]; then
    echo "ERROR: Database not found"
    exit 1
fi

BACKUP="${DATABASE}.backup.$(date +%Y%m%d%H%M%S)"

echo "Creating backup:"
echo "  ${BACKUP}"

cp "${DATABASE}" "${BACKUP}"

echo
echo "Beginning migration..."
echo

sqlite3 "${DATABASE}" <<SQL
BEGIN TRANSACTION;

--
-- Convert naive timestamps to ISO8601 with timezone
--
-- Only modify timestamps that:
--   1. Do NOT already contain timezone offsets
--   2. Do NOT already contain a trailing Z
--

UPDATE entries
SET ts =
    REPLACE(ts, ' ', 'T') || '${DEFAULT_OFFSET}'
WHERE
    ts NOT LIKE '%+__:__'
    AND ts NOT LIKE '%-__:__'
    AND ts NOT LIKE '%Z';

COMMIT;
SQL

echo
echo "Migration complete."
echo

echo "Verifying results..."
echo

sqlite3 "${DATABASE}" <<SQL
.headers on
.mode column

SELECT
    id,
    user_id,
    entrytype,
    ts
FROM entries
ORDER BY id DESC
LIMIT 10;
SQL

echo
echo "Done."