# ADR-141 — The Database Renders Time in IST; Instants Are Still Stored Absolutely

| | |
|---|---|
| **Status** | Accepted |
| **Date** | 2026-08-14 |
| **Decision maker** | hkl — *"In future time you or db should refer should be IST only."* |
| **Applies to** | the whole `lm360` database, therefore every PostgREST client — all 360LM PWAs — and every report or query I produce |

## Context

`lm360` ran with `timezone = UTC`. Every column that matters is `timestamptz`, so the
stored value has always been an unambiguous instant; only the *rendering* was UTC. That
rendering reached three audiences:

- **PostgREST responses** — `"2026-08-14T07:41:17.092+00:00"` to every PWA.
- **psql output** — what I read and quote back in reports.
- **Anything that treats the ISO string as text** rather than parsing it.

The third is the problem. A grep across `/var/www/360lm` found live code slicing the
first ten characters off a timestamp to get a date:

```
expense/index.html:1543   (sh.submitted_at || sh.created_at || '').slice(0, 10)
expense/index.html:3330   t.created_at.slice(0,10)
sales/index.html:1383     fmtDate(j.created_at?.slice(0,10))
sales/index.html:1471     fmtDate(job.created_at?.slice(0,10))
```

Under UTC rendering that is **already wrong** for a large part of the working day:
anything entered after 05:30 IST… is fine, but anything entered between 00:00 and 05:30
IST renders as the *previous* UTC day, and the slice therefore shows the wrong date. A
late-night expense entry was being filed under yesterday. Nobody had reported it, which
is characteristic — it is off by exactly one day, only sometimes, and only for people
working late.

The business operates in one timezone. There is no second locale to serve.

## Decision

```sql
ALTER DATABASE lm360 SET timezone = 'Asia/Kolkata';
```
followed by a PostgREST restart so its connection pool picks the setting up.

**Storage is unchanged.** `timestamptz` still stores an absolute instant; `now()` still
records the same moment. The only change is the offset the value is *rendered* with:

```
before   "start_at": "2026-07-31T16:36:00+00:00"
after    "start_at": "2026-07-31T22:06:00+05:30"     ← the same instant
```

And I report IST in everything I write, converting explicitly when quoting raw output.

## Why

**This makes the naive parses correct rather than breaking them.** That is the crux, and
it is why the change is safe rather than merely tolerable:

- Code doing `new Date(s)` is unaffected — both forms parse to the identical instant.
- Code doing `s.slice(0,10)` now yields the **IST calendar date**, which is what every
  one of those four call sites actually wanted. The change fixes a live off-by-one-day
  bug as a side effect.
- Lexicographic string comparison on timestamps still works: every value now carries the
  same `+05:30` offset, so ordering within the column is unchanged. (Mixed offsets would
  have been a real hazard — there are none, because the setting is database-wide.)

The alternative — teaching each client to localise — means auditing and fixing every PWA
and getting it right again in every future one. Setting it once at the database makes
the correct thing the default and the naive thing accidentally right.

## Consequences

- Every PostgREST response now carries `+05:30`. Verified same-instant.
- Anything that hard-codes `Z`, assumes a `+00:00` suffix, or compares a stored string
  against a UTC literal it built itself would now mismatch. None was found, but the
  search was a grep, not a proof.
- Reports, exports and screenshots change appearance. Values are the same moments.
- A future genuinely-multi-timezone requirement would need per-user rendering; this
  decision would then be the wrong default.
- **Not covered by this change:** `date` columns (`start_date`, `end_date` on tours and
  the like) have no timezone and were never affected.

## Does NOT govern

- **How PWAs display time to users.** They use JS `toLocaleString`, which follows the
  device — already IST on Indian phones. Untouched.
- **The four naive `.slice(0,10)` call sites.** They are now correct by accident, not by
  construction. They remain latent bugs against any future timezone change and should be
  rewritten to parse properly. **Open, deliberately out of scope.**
- **Server OS / container clocks.** Unchanged.
- **The Apps Script bridge and other non-PostgREST surfaces.** They have their own
  timezone handling.

## Revisit If

- **Any client is found comparing timestamp strings against self-built UTC literals** —
  that breaks silently and would need fixing at the call site.
- **The business ever operates across timezones** — a single database-wide render locale
  stops being right and rendering must move to the client.
- **A PWA starts writing timestamps as naive strings** (no offset) — those would now be
  interpreted as IST rather than UTC on insert, which is the correct reading but a change
  from before.
- **A historical report has to be reconciled against a pre-2026-08-14 export** — the
  numbers are the same instants, but the printed offsets differ.

## Verification

- `show timezone` → `Asia/Kolkata` on a fresh session; PostgREST restarted and returning
  `+05:30`; the same row's instant confirmed unchanged before and after
  (`2026-07-31T16:36:00+00:00` → `2026-07-31T22:06:00+05:30`).
- **Cross-PWA regression after the change: `expense`, `sales`, `hub`, `smoke`, `finance`,
  `dispatch`, `activity` → 74 passed, 0 failed.**
- The three `rentveh` suites → **22 passed / 4 failed**, the 4 being the pre-existing
  submit-requires-a-tour cascade that predates all of today's work.
- No failure anywhere was attributable to the timezone change.