feat: /pool Created column — relative age from indexer created_at #662

New issue

Closed

opened 2026-08-26 04:13:28 +00:00 by PlasticDigits · 11 comments

PlasticDigits commented 2026-08-26 04:13:28 +00:00 (Migrated from gitlab.com)

Copy link

Summary

Fill the /pool pairs-table Created column with a retail relative age (3 hours ago, 12 days ago, 2 years ago). Bundle indexer list JSON + sort alignment + dApp formatter/cell + docs/verify in one issue. Do not split “API field” vs “table cell” vs “relative-time helper.”

Related: #547 (/pool table; P547-4 deferred Created to —), #655 (v2 LP USD column — does not own Created), #534 (catalog rank), #562 (hide gems), #489 (no lectures), #653 (no nested chrome).

Out of scope: /protocol pairs_added_30d copy, GeckoTerminal /gt/ pair timestamps (already expose createdAtBlockTimestamp from pairs.created_at), Charts “Created” sort dropdown (no Created column), reconstructing on-chain genesis from factory events / first-swap block_timestamp.

Current codebase

The Created header is sortable. The Created cell is a hardcoded em-dash. The timestamp already lives on pairs in Postgres; GET /api/v1/pairs drops it. This is intentional leftover from #547, not a CSS/empty-string glitch.

Layer	Behavior today
/pool cell	PoolPairsTable.tsx data-testid="pool-row-created" always renders — (lines ~232–234). Header sortKey="created" / pool-sort-created already exists. Manage expand colSpan={6}.
Docs / playbook	docs/frontend.md P547-4: “Created cells show — because GET /api/v1/pairs list JSON has no timestamp (sort still hits indexer created).” Same sentence in skills/AGENTS_FRONTEND_POOL_TABLE.md.
List fetch	PoolPage.tsx + poolListQuery.ts: catalog default limit=500 sort=volume_24h + client rank; column click sends indexer sort=created order=desc (default for created). Production omits gems (P562-3).
Frontend type	IndexerPair has pair_address, assets, lp_token, fee_bps, is_active, code_id_frozen?, volume_quote_24h?. No created_at. IndexerPairSort already includes 'created'.
Indexer JSON	PairResponse omits timestamps. list_pairs maps PairRow → response and drops p.created_at / p.created_at_block. Same struct for GET /api/v1/pairs/{addr} and GET /api/v1/tokens/{addr}/pairs.
DB already has the clock	PairRow: created_at_block: Option<i64>, created_at: DateTime<Utc> (TIMESTAMPTZ NOT NULL DEFAULT NOW()). List SQL already SELECTs both. idx_pairs_created_block exists.
Ingest	pair_discovery.rs upsert_pair(..., created_at_block = None). First insert stamps created_at = NOW(); ON CONFLICT does not refresh created_at. So created_at = indexer first-seen, not factory CreatePair genesis (same caveat as overview pairs_added_30d / docs/runbooks/overview-global-stats-brin.md). After --fresh / DB rebuild every pair looks new.
sort=created today	ORDER BY p.created_at_block … NULLS LAST, p.id ASC. Discovery never writes created_at_block, so Created sort is effectively id order. Displaying created_at while sorting created_at_block would disagree.
Relative time	formatDate.ts has formatTime / formatDateTime only (clock / “Jan 15, 2:30 PM”). No “N hours/days/years ago” helper. Tape uses absolute formatTime.
GT already serializes the clock	indexer/src/api/gt.rs: createdAtBlockTimestamp: Some(pair.created_at.timestamp()) — same pairs.created_at, different adapter. Official /api/v1/pairs must grow its own field; do not scrape /gt/ from the dApp.
Tests	PoolPage.test.tsx / e2e/pool-table-547.spec.ts never assert Created cells. indexer/tests/api_pairs.rs asserts list 200 + invalid sort 400; no created_at key.

Why this is needed

1. The column is a lie. Users scanning /pool see a Created header and an empty age. Retail expectation is “how old is this pool?” — the same pattern as pair age on other DEX UIs. Hardcoded — reads as “unknown / broken,” not “intentionally deferred.”
2. The data is already in the list query. list_pairs_filtered SELECTs p.created_at. Shipping a second LCD/get_pair per row to invent a date would violate P547-9 / A8 (no N+1 on first paint).
3. Sort without a visible value is unusable. Clicking Created reorders (or appears to) while every cell stays —. Align JSON + cell + ORDER BY so the caret matches what people see.
4. #547 left a documented hole. P547-4 said fill the cell when list JSON includes a timestamp. That work was never filed. #655 notes the blank cell but owns v2 LP USD, not age.

Constraints / guardrails

1. One issue, both sides. Indexer field + dApp cell land together. Old indexers (field absent) → cell stays —. Do not break existing PairResponse clients: additive optional/present ISO-8601 string.
2. Use pairs.created_at (first-seen). Do not claim on-chain genesis in UI copy. Do not reconstruct age from first swap, LCD ContractInfo, or factory event scans in this issue. After --fresh, all ages look recent — document for operators in docs/frontend.md / playbook, not as a table lecture (#489).
3. Align sort=created with the displayed clock. Change ORDER BY from p.created_at_block to p.created_at (always NOT NULL) + p.id tiebreak. Default order remains desc (newest first). Catalog default (no column click) stays UST1-hub rank — no catalog overlay on Created. Search stays relevance.
4. No per-row LCD / extra HTTP on first paint (P547-9). Age comes only from list JSON already fetched by getPairs.
5. Relative copy. Human buckets: minutes / hours / days / years (months optional). Examples: 5 minutes ago, 3 hours ago, 12 days ago, 2 years ago. Singular/plural. Missing / unparsable / non-finite / Invalid Date → —. Do not print raw ISO in the cell. Optional title = absolute UTC (or formatDateTime) for hover — not a paragraph.
6. No live 1 Hz ticker across catalog (up to 100–500 rows). Format at render from Date.now(); React Query 30s stale is enough. Do not setInterval per row.
7. Parse, never interpolate as HTML. created_at is a timestamp string. Text node / React children only. javascript:, HTML, extremely large years, unix-ms vs unix-s confusion → —.
8. Clock skew. Timestamp slightly in the future (≤ ~2 min) may clamp to just now / 1 minute ago. Far-future / year 0 / negative → —.
9. Same field on all PairResponse builders (list_pairs, get_pair, get_token_pairs) so IndexerPair stays one shape. Optional created_at_block is nice for integrators; not required for the cell.
10. Do not raise PAIR_LIST_LIMIT_MAX or change catalog fetch for this column. Do not rename volume_quote_24h. Do not implement #655 liquidity_usd here.
11. #489 / #653. No “indexer first-seen vs genesis” essay in the header. No card-glass per row. Keep overflow-x-auto. Light + dark. Phone 375px: horizontal scroll OK; cell must not inject a wrapping lecture.
12. a11y. Keep existing caret + aria-sort. data-testid="pool-row-created" stays. Visible text is the relative phrase (or —).
13. Production gems (#562). Hidden pairs stay hidden. Frozen mark stays on Source.
14. Charts / GT. Do not change Charts sort labels or /gt/ field names unless a shared formatter is extracted (allowed). Official dApp must not call /gt/ to fill /pool.

Relevant files

File	Role
indexer/src/api/pairs.rs	PairResponse, list / get mapping
indexer/src/api/tokens.rs	get_token_pairs same struct
indexer/src/db/queries/pairs.rs	PairRow, sort=created ORDER BY
indexer/tests/api_pairs.rs	List JSON created_at + sort order
frontend-dapp/src/types/index.ts	IndexerPair.created_at?
frontend-dapp/src/utils/formatDate.ts	New formatRelativeAge (or equivalent)
frontend-dapp/src/utils/__tests__/formatDate.test.ts	Bucket + invalid cases
frontend-dapp/src/components/pool/PoolPairsTable.tsx	Cell render
frontend-dapp/src/pages/PoolPage.tsx	Pass-through only
frontend-dapp/src/pages/PoolPage.test.tsx	Created click + cell text
frontend-dapp/src/utils/__tests__/poolListQuery.test.ts	created still in allowlist / default desc
frontend-dapp/e2e/pool-table-547.spec.ts	Cell not all — when indexer has dates
docs/frontend.md	Update P547-4
skills/AGENTS_FRONTEND_POOL_TABLE.md	Same
docs/indexer-invariants.md	Pair list JSON now includes first-seen created_at

Recommended direction

1. Add created_at: String (RFC3339 / serde DateTime<Utc>) on PairResponse, sourced from p.created_at. Keep it on every constructor of that struct.
2. Switch PairListSort::Created to ORDER BY p.created_at {ASC\|DESC}, p.id ASC.
3. Add created_at?: string on IndexerPair (optional so mocks / old payloads still type-check).
4. Pure helper formatRelativeAge(iso: string \| null \| undefined, nowMs?: number): string in formatDate.ts — inject now in tests. Return — on failure.
5. PoolPairsTable: {formatRelativeAge(ip.created_at)} with title = absolute time when valid.
6. Rewrite P547-4: cells show relative age from list created_at; — only when missing/invalid. Note first-seen (not genesis) in the docs table, not the page.
7. Extend make verify-issue-547 or add make verify-issue-NNN that covers Created cells + formatter unit tests. Prefer extending 547 plus a focused verify if the new indexer assertion needs Postgres.

Acceptance criteria

• GET /api/v1/pairs items include created_at as an ISO-8601 UTC timestamp for seeded pairs.
• GET /api/v1/pairs/{addr} and GET /api/v1/tokens/{addr}/pairs include the same field (one PairResponse shape).
• sort=created&order=desc orders by created_at newest first; asc oldest first; invalid sort still 400.
• /pool Created cells show relative age (N minutes/hours/days/years ago) when created_at is valid.
• Missing, empty, or garbage created_at → — (no crash, no raw dump).
• Catalog default still has no active Created caret; clicking Created uses indexer sort (no catalog overlay) and default desc.
• Search mode still relevance; gems still omitted in production (P562-3).
• No per-row LCD / extra indexer GET for age. No per-row interval timer.
• Hover/title may show absolute time; visible cell is relative (or —).
• P547-4 + pool-table playbook updated. #655 can still add a column without colliding on this field.
• Light + dark; phone table still scrollable; Manage colSpan still matches column count.

Test plan (all paths)

Indexer

• list_pairs_returns_200 (or sibling): each item has created_at matching the seed row’s RFC3339 (or parseable UTC).
• Two pairs with distinct created_at: sort=created&order=desc → newer first; asc → older first.
• sort=created with one very old and one NOW() row (SQL UPDATE pairs SET created_at = …).
• GET /api/v1/pairs/{addr} and token-pairs include created_at.
• sort=bad_sort → 400; offset over max → 400 (no regression).
• EXPLAIN optional: sort=created uses pairs.created_at (seq scan on tiny test DB is OK; do not add a live SUM).

Formatter (Vitest)

• Injected now: 30s → just now or 1 minute ago (pick one, lock it).
• 5 minutes / 3 hours / 2 days / 400 days (~1 year) / 800 days (~2 years) — exact bucket strings locked in tests.
• null / undefined / '' / 'not-a-date' / '<img>' / 'javascript:alert(1)' → —.
• Far future (now + 10 years) → —. Near future (now + 30s) → just now / minute bucket (not “in 30 seconds” unless you explicitly support that; prefer not).
• Unix-ms string accidentally passed ('1710000000000') → — or documented parse; do not show “54000 years ago.”

dApp unit

• PoolPage.test.tsx: mock items with created_at → pool-row-created matches helper output (e.g. 3 hours ago).
• Mock without created_at → —.
• Click pool-sort-created → getPairs called with sort=created order=desc; second click asc.
• Catalog mode: Created aria-sort is none until click.
• poolListQuery still defaults created → desc; allowlist unchanged except docs.

E2E / verify

• e2e/pool-table-547.spec.ts (or new spec): first Created cell is not universally — when LocalTerra indexer has pairs (skip-soft if indexer down, same pattern as other pool specs).
• Phone 390 and desktop: column still present; no header lecture.
• make verify-issue-547 still green; new/extended verify covers formatter + list JSON.

Test plan (attack, hack, and abuse)

• XSS / HTML injection. created_at = <script>alert(1)</script>, "><img src=x onerror=alert(1)>, javascript:alert(1). Cell is text; no attribute breakout in title.
• Parser bombs. Extremely long string, nested JSON, +275760-09-13 (JS Date max), year 0000, NaN, Infinity, null JSON. Cell —, no throw on list render.
• Numeric confusion. Unix seconds vs ms vs nanoseconds as a bare number/string must not render multi-millennia ages. Reject non-ISO if that is the contract (preferred).
• Prototype / key spoof. Extra JSON keys (__proto__, constructor) on a pair item must not change Object.prototype or the formatter.
• Sort injection. sort=created;drop / created_at / SQL fragments still 400 (existing allowlist).
• DoS. Rendering 100 rows must not start 100 timers. Catalog fetch stays one getPairs; no N+1 getPair for dates.
• Phishing copy. Relative text is generated locally, never from a message / symbol field. Pair symbol spoof cannot appear inside the Created cell.
• Open redirect / URL. Do not turn Created into a link. No explorer href from a timestamp.
• Auth / IDOR. Public list already; do not add wallet-gated age. No new PII.
• Cache poison. Stale indexer without the field → —, not a crash that blanks the whole table.
• Clock / TZ. UTC ISO with Z vs offset +00:00 both parse. Do not treat local midnight as a second-day jump in tests (inject now).
• A8 regression. Default /pool paint still must not call getPool / getPairFeeConfig per row.

Verification criteria

Done when:

1. A reviewer on /pool (LocalTerra or production indexer that emits created_at) sees Created cells like 6 hours ago / 3 days ago / 1 year ago, not a full column of —.
2. Clicking Created toggles newest↔oldest and the visible ages agree with that order.
3. GET /api/v1/pairs JSON includes created_at; make test-frontend formatter + PoolPage tests pass; indexer api_pairs (or added test) passes with make setup-indexer-postgres + targeted cargo test.
4. Docs no longer say Created cells are always —.
5. Abuse cases above are unit-covered (formatter) or explicitly unchanged (sort allowlist / no N+1).

make test-frontend
# indexer (Postgres):
#   make setup-indexer-postgres
#   cd indexer && cargo test --test api_pairs -- --test-threads=1
make verify-issue-547

## Summary Fill the `/pool` pairs-table **Created** column with a retail relative age (`3 hours ago`, `12 days ago`, `2 years ago`). Bundle **indexer list JSON** + **sort alignment** + **dApp formatter/cell** + **docs/verify** in **one** issue. Do **not** split “API field” vs “table cell” vs “relative-time helper.” Related: [#547](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/547) (`/pool` table; **P547-4** deferred Created to `—`), [#655](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/655) (v2 LP USD column — **does not own Created**), [#534](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/534) (catalog rank), [#562](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/562) (hide gems), [#489](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/489) (no lectures), [#653](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/653) (no nested chrome). Out of scope: `/protocol` `pairs_added_30d` copy, GeckoTerminal `/gt/` pair timestamps (already expose `createdAtBlockTimestamp` from `pairs.created_at`), Charts “Created” **sort dropdown** (no Created column), reconstructing on-chain genesis from factory events / first-swap `block_timestamp`. ## Current codebase The Created **header is sortable**. The Created **cell is a hardcoded em-dash**. The timestamp already lives on `pairs` in Postgres; `GET /api/v1/pairs` drops it. This is intentional leftover from #547, not a CSS/empty-string glitch. | Layer | Behavior today | |-------|----------------| | **`/pool` cell** | [`PoolPairsTable.tsx`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/components/pool/PoolPairsTable.tsx) `data-testid="pool-row-created"` always renders `—` (lines ~232–234). Header `sortKey="created"` / `pool-sort-created` already exists. Manage expand `colSpan={6}`. | | **Docs / playbook** | [`docs/frontend.md`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/docs/frontend.md) **P547-4**: “Created **cells** show `—` because `GET /api/v1/pairs` list JSON has no timestamp (sort still hits indexer `created`).” Same sentence in [`skills/AGENTS_FRONTEND_POOL_TABLE.md`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/skills/AGENTS_FRONTEND_POOL_TABLE.md). | | **List fetch** | [`PoolPage.tsx`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/pages/PoolPage.tsx) + [`poolListQuery.ts`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/utils/poolListQuery.ts): catalog default `limit=500` `sort=volume_24h` + client rank; column click sends indexer `sort=created` `order=desc` (default for created). Production omits gems (**P562-3**). | | **Frontend type** | [`IndexerPair`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/types/index.ts) has `pair_address`, assets, `lp_token`, `fee_bps`, `is_active`, `code_id_frozen?`, `volume_quote_24h?`. **No** `created_at`. `IndexerPairSort` already includes `'created'`. | | **Indexer JSON** | [`PairResponse`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/indexer/src/api/pairs.rs) omits timestamps. `list_pairs` maps `PairRow` → response and **drops** `p.created_at` / `p.created_at_block`. Same struct for `GET /api/v1/pairs/{addr}` and `GET /api/v1/tokens/{addr}/pairs`. | | **DB already has the clock** | [`PairRow`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/indexer/src/db/queries/pairs.rs): `created_at_block: Option<i64>`, `created_at: DateTime<Utc>` (`TIMESTAMPTZ NOT NULL DEFAULT NOW()`). List SQL already SELECTs both. `idx_pairs_created_block` exists. | | **Ingest** | [`pair_discovery.rs`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/indexer/src/indexer/pair_discovery.rs) `upsert_pair(..., created_at_block = None)`. First insert stamps `created_at = NOW()`; `ON CONFLICT` does **not** refresh `created_at`. So `created_at` = **indexer first-seen**, not factory `CreatePair` genesis (same caveat as overview `pairs_added_30d` / [`docs/runbooks/overview-global-stats-brin.md`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/docs/runbooks/overview-global-stats-brin.md)). After `--fresh` / DB rebuild every pair looks new. | | **`sort=created` today** | `ORDER BY p.created_at_block … NULLS LAST, p.id ASC`. Discovery never writes `created_at_block`, so Created sort is effectively **id order**. Displaying `created_at` while sorting `created_at_block` would disagree. | | **Relative time** | [`formatDate.ts`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/utils/formatDate.ts) has `formatTime` / `formatDateTime` only (clock / “Jan 15, 2:30 PM”). **No** “N hours/days/years ago” helper. Tape uses absolute `formatTime`. | | **GT already serializes the clock** | [`indexer/src/api/gt.rs`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/indexer/src/api/gt.rs): `createdAtBlockTimestamp: Some(pair.created_at.timestamp())` — same `pairs.created_at`, different adapter. Official `/api/v1/pairs` must grow its own field; do not scrape `/gt/` from the dApp. | | **Tests** | [`PoolPage.test.tsx`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/src/pages/PoolPage.test.tsx) / [`e2e/pool-table-547.spec.ts`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/frontend-dapp/e2e/pool-table-547.spec.ts) never assert Created cells. [`indexer/tests/api_pairs.rs`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/indexer/tests/api_pairs.rs) asserts list 200 + invalid sort **400**; no `created_at` key. | ## Why this is needed 1. **The column is a lie.** Users scanning `/pool` see a Created header and an empty age. Retail expectation is “how old is this pool?” — the same pattern as pair age on other DEX UIs. Hardcoded `—` reads as “unknown / broken,” not “intentionally deferred.” 2. **The data is already in the list query.** `list_pairs_filtered` SELECTs `p.created_at`. Shipping a second LCD/`get_pair` per row to invent a date would violate **P547-9** / A8 (no N+1 on first paint). 3. **Sort without a visible value is unusable.** Clicking Created reorders (or appears to) while every cell stays `—`. Align JSON + cell + `ORDER BY` so the caret matches what people see. 4. **#547 left a documented hole.** P547-4 said fill the cell **when** list JSON includes a timestamp. That work was never filed. #655 notes the blank cell but owns **v2 LP USD**, not age. ## Constraints / guardrails 1. **One issue, both sides.** Indexer field + dApp cell land together. Old indexers (field absent) → cell stays `—`. Do not break existing `PairResponse` clients: **additive** optional/present ISO-8601 string. 2. **Use `pairs.created_at` (first-seen).** Do **not** claim on-chain genesis in UI copy. Do **not** reconstruct age from first swap, LCD `ContractInfo`, or factory event scans in this issue. After `--fresh`, all ages look recent — document for operators in `docs/frontend.md` / playbook, **not** as a table lecture (**#489**). 3. **Align `sort=created` with the displayed clock.** Change `ORDER BY` from `p.created_at_block` to `p.created_at` (always NOT NULL) + `p.id` tiebreak. Default order remains **desc** (newest first). Catalog default (no column click) stays UST1-hub rank — **no** catalog overlay on Created. Search stays `relevance`. 4. **No per-row LCD / extra HTTP on first paint (P547-9).** Age comes only from list JSON already fetched by `getPairs`. 5. **Relative copy.** Human buckets: minutes / hours / days / years (months optional). Examples: `5 minutes ago`, `3 hours ago`, `12 days ago`, `2 years ago`. Singular/plural. Missing / unparsable / non-finite / `Invalid Date` → `—`. Do **not** print raw ISO in the cell. Optional `title` = absolute UTC (or `formatDateTime`) for hover — not a paragraph. 6. **No live 1 Hz ticker** across catalog (up to 100–500 rows). Format at render from `Date.now()`; React Query 30s stale is enough. Do not `setInterval` per row. 7. **Parse, never interpolate as HTML.** `created_at` is a timestamp string. Text node / React children only. `javascript:`, HTML, extremely large years, unix-ms vs unix-s confusion → `—`. 8. **Clock skew.** Timestamp slightly in the future (≤ ~2 min) may clamp to `just now` / `1 minute ago`. Far-future / year 0 / negative → `—`. 9. **Same field on all `PairResponse` builders** (`list_pairs`, `get_pair`, `get_token_pairs`) so `IndexerPair` stays one shape. Optional `created_at_block` is nice for integrators; **not** required for the cell. 10. **Do not raise `PAIR_LIST_LIMIT_MAX` or change catalog fetch** for this column. Do not rename `volume_quote_24h`. Do not implement #655 `liquidity_usd` here. 11. **#489 / #653.** No “indexer first-seen vs genesis” essay in the header. No `card-glass` per row. Keep `overflow-x-auto`. Light + dark. Phone 375px: horizontal scroll OK; cell must not inject a wrapping lecture. 12. **a11y.** Keep existing caret + `aria-sort`. `data-testid="pool-row-created"` stays. Visible text is the relative phrase (or `—`). 13. **Production gems (#562).** Hidden pairs stay hidden. Frozen mark stays on Source. 14. **Charts / GT.** Do not change Charts sort labels or `/gt/` field names unless a shared formatter is extracted (allowed). Official dApp must not call `/gt/` to fill `/pool`. ## Relevant files | File | Role | |------|------| | `indexer/src/api/pairs.rs` | `PairResponse`, list / get mapping | | `indexer/src/api/tokens.rs` | `get_token_pairs` same struct | | `indexer/src/db/queries/pairs.rs` | `PairRow`, `sort=created` `ORDER BY` | | `indexer/tests/api_pairs.rs` | List JSON `created_at` + sort order | | `frontend-dapp/src/types/index.ts` | `IndexerPair.created_at?` | | `frontend-dapp/src/utils/formatDate.ts` | New `formatRelativeAge` (or equivalent) | | `frontend-dapp/src/utils/__tests__/formatDate.test.ts` | Bucket + invalid cases | | `frontend-dapp/src/components/pool/PoolPairsTable.tsx` | Cell render | | `frontend-dapp/src/pages/PoolPage.tsx` | Pass-through only | | `frontend-dapp/src/pages/PoolPage.test.tsx` | Created click + cell text | | `frontend-dapp/src/utils/__tests__/poolListQuery.test.ts` | `created` still in allowlist / default desc | | `frontend-dapp/e2e/pool-table-547.spec.ts` | Cell not all `—` when indexer has dates | | `docs/frontend.md` | Update **P547-4** | | `skills/AGENTS_FRONTEND_POOL_TABLE.md` | Same | | `docs/indexer-invariants.md` | Pair list JSON now includes first-seen `created_at` | ## Recommended direction 1. Add `created_at: String` (RFC3339 / serde `DateTime<Utc>`) on `PairResponse`, sourced from `p.created_at`. Keep it on every constructor of that struct. 2. Switch `PairListSort::Created` to `ORDER BY p.created_at {ASC\|DESC}, p.id ASC`. 3. Add `created_at?: string` on `IndexerPair` (optional so mocks / old payloads still type-check). 4. Pure helper `formatRelativeAge(iso: string \| null \| undefined, nowMs?: number): string` in `formatDate.ts` — inject `now` in tests. Return `—` on failure. 5. `PoolPairsTable`: `{formatRelativeAge(ip.created_at)}` with `title` = absolute time when valid. 6. Rewrite P547-4: cells show relative age from list `created_at`; `—` only when missing/invalid. Note first-seen (not genesis) in the **docs table**, not the page. 7. Extend `make verify-issue-547` **or** add `make verify-issue-NNN` that covers Created cells + formatter unit tests. Prefer extending 547 plus a focused verify if the new indexer assertion needs Postgres. ## Acceptance criteria - [ ] `GET /api/v1/pairs` items include `created_at` as an ISO-8601 UTC timestamp for seeded pairs. - [ ] `GET /api/v1/pairs/{addr}` and `GET /api/v1/tokens/{addr}/pairs` include the same field (one `PairResponse` shape). - [ ] `sort=created&order=desc` orders by `created_at` newest first; `asc` oldest first; invalid `sort` still **400**. - [ ] `/pool` Created cells show relative age (`N minutes/hours/days/years ago`) when `created_at` is valid. - [ ] Missing, empty, or garbage `created_at` → `—` (no crash, no raw dump). - [ ] Catalog default still has **no** active Created caret; clicking Created uses indexer sort (no catalog overlay) and default **desc**. - [ ] Search mode still `relevance`; gems still omitted in production (**P562-3**). - [ ] No per-row LCD / extra indexer GET for age. No per-row interval timer. - [ ] Hover/`title` may show absolute time; visible cell is relative (or `—`). - [ ] P547-4 + pool-table playbook updated. #655 can still add a column without colliding on this field. - [ ] Light + dark; phone table still scrollable; Manage `colSpan` still matches column count. ## Test plan (all paths) **Indexer** - [ ] `list_pairs_returns_200` (or sibling): each item has `created_at` matching the seed row’s RFC3339 (or parseable UTC). - [ ] Two pairs with distinct `created_at`: `sort=created&order=desc` → newer first; `asc` → older first. - [ ] `sort=created` with one very old and one `NOW()` row (SQL `UPDATE pairs SET created_at = …`). - [ ] `GET /api/v1/pairs/{addr}` and token-pairs include `created_at`. - [ ] `sort=bad_sort` → 400; `offset` over max → 400 (no regression). - [ ] `EXPLAIN` optional: `sort=created` uses `pairs.created_at` (seq scan on tiny test DB is OK; do not add a live `SUM`). **Formatter (Vitest)** - [ ] Injected `now`: 30s → `just now` or `1 minute ago` (pick one, lock it). - [ ] 5 minutes / 3 hours / 2 days / 400 days (~1 year) / 800 days (~2 years) — exact bucket strings locked in tests. - [ ] `null` / `undefined` / `''` / `'not-a-date'` / `'<img>'` / `'javascript:alert(1)'` → `—`. - [ ] Far future (now + 10 years) → `—`. Near future (now + 30s) → `just now` / minute bucket (not “in 30 seconds” unless you explicitly support that; prefer not). - [ ] Unix-ms string accidentally passed (`'1710000000000'`) → `—` or documented parse; do not show “54000 years ago.” **dApp unit** - [ ] `PoolPage.test.tsx`: mock items with `created_at` → `pool-row-created` matches helper output (e.g. `3 hours ago`). - [ ] Mock without `created_at` → `—`. - [ ] Click `pool-sort-created` → `getPairs` called with `sort=created` `order=desc`; second click `asc`. - [ ] Catalog mode: Created `aria-sort` is `none` until click. - [ ] `poolListQuery` still defaults `created` → `desc`; allowlist unchanged except docs. **E2E / verify** - [ ] `e2e/pool-table-547.spec.ts` (or new spec): first Created cell is not universally `—` when LocalTerra indexer has pairs (skip-soft if indexer down, same pattern as other pool specs). - [ ] Phone 390 and desktop: column still present; no header lecture. - [ ] `make verify-issue-547` still green; new/extended verify covers formatter + list JSON. ## Test plan (attack, hack, and abuse) - [ ] **XSS / HTML injection.** `created_at` = `<script>alert(1)</script>`, `"><img src=x onerror=alert(1)>`, `javascript:alert(1)`. Cell is text; no attribute breakout in `title`. - [ ] **Parser bombs.** Extremely long string, nested JSON, `+275760-09-13` (JS Date max), year `0000`, `NaN`, `Infinity`, `null` JSON. Cell `—`, no throw on list render. - [ ] **Numeric confusion.** Unix seconds vs ms vs nanoseconds as a bare number/string must not render multi-millennia ages. Reject non-ISO if that is the contract (preferred). - [ ] **Prototype / key spoof.** Extra JSON keys (`__proto__`, `constructor`) on a pair item must not change `Object.prototype` or the formatter. - [ ] **Sort injection.** `sort=created;drop` / `created_at` / SQL fragments still **400** (existing allowlist). - [ ] **DoS.** Rendering 100 rows must not start 100 timers. Catalog fetch stays one `getPairs`; no N+1 `getPair` for dates. - [ ] **Phishing copy.** Relative text is generated locally, never from a `message` / `symbol` field. Pair symbol spoof cannot appear inside the Created cell. - [ ] **Open redirect / URL.** Do not turn Created into a link. No explorer href from a timestamp. - [ ] **Auth / IDOR.** Public list already; do not add wallet-gated age. No new PII. - [ ] **Cache poison.** Stale indexer without the field → `—`, not a crash that blanks the whole table. - [ ] **Clock / TZ.** UTC ISO with `Z` vs offset `+00:00` both parse. Do not treat local midnight as a second-day jump in tests (inject `now`). - [ ] **A8 regression.** Default `/pool` paint still must not call `getPool` / `getPairFeeConfig` per row. ## Verification criteria Done when: 1. A reviewer on `/pool` (LocalTerra or production indexer that emits `created_at`) sees Created cells like `6 hours ago` / `3 days ago` / `1 year ago`, not a full column of `—`. 2. Clicking Created toggles newest↔oldest and the visible ages agree with that order. 3. `GET /api/v1/pairs` JSON includes `created_at`; `make test-frontend` formatter + PoolPage tests pass; indexer `api_pairs` (or added test) passes with `make setup-indexer-postgres` + targeted `cargo test`. 4. Docs no longer say Created cells are always `—`. 5. Abuse cases above are unit-covered (formatter) or explicitly unchanged (sort allowlist / no N+1). ```bash make test-frontend # indexer (Postgres): # make setup-indexer-postgres # cd indexer && cargo test --test api_pairs -- --test-threads=1 make verify-issue-547 ```

PlasticDigits commented 2026-08-26 04:13:29 +00:00 (Migrated from gitlab.com)

Copy link

marked as related to #547

marked as related to #547

PlasticDigits commented 2026-08-26 04:13:30 +00:00 (Migrated from gitlab.com)

Copy link

marked as related to #655

marked as related to #655

PlasticDigits commented 2026-08-26 07:07:01 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit e76b920897

mentioned in commit e76b920897e82214d2aea5d46f9de4a13b93a7f6

PlasticDigits commented 2026-08-26 07:09:18 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in merge request !1165

mentioned in merge request !1165

PlasticDigits commented 2026-08-26 08:24:21 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit 638e9ca59c

mentioned in commit 638e9ca59c3ac81160e05f4df685ff5398804b69

PlasticDigits commented 2026-08-26 08:24:29 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit 4da8ef0ae3

mentioned in commit 4da8ef0ae3c119f3c6bd0edd5e2d8d0c403f20a9

PlasticDigits (Migrated from gitlab.com) closed this issue 2026-08-26 08:24:29 +00:00

PlasticDigits commented 2026-08-26 09:00:57 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit d52bdf039a

mentioned in commit d52bdf039a3e57a4db6eb5a9aa0b0095b73c9c22

PlasticDigits commented 2026-08-26 09:30:59 +00:00 (Migrated from gitlab.com)

Copy link

Merged to main via !1165. PairResponse keeps both created_at and liquidity_usd (filled by #655 JOIN).

Leftover: /pool Created column shows relative age after indexer+frontend rebuild. make verify-issue-662.

Merged to `main` via !1165. `PairResponse` keeps both `created_at` and `liquidity_usd` (filled by #655 JOIN). Leftover: `/pool` Created column shows relative age after indexer+frontend rebuild. `make verify-issue-662`.

PlasticDigits commented 2026-08-26 09:31:10 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #655

mentioned in issue #655

PlasticDigits commented 2026-08-26 09:31:35 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #673

mentioned in issue #673

PlasticDigits commented 2026-08-28 05:22:09 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #692

mentioned in issue #692

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

renovate/tokio-util-0.x-lockfile

renovate/thiserror-2.x-lockfile

renovate/serde_json-1.x-lockfile

renovate/serde-monorepo

renovate/libc-0.x-lockfile

renovate/chrono-0.x-lockfile

cac-design-issue-1206

cac-design-issue-1317

cac-design-issue-1316

cac-design-issue-1311

cac-design-issue-1300

cac-design-issue-1306

cac-design-issue-1305

cac-design-issue-1302

cac-design-issue-1277

No results found.

Labels

Clear labels   

agent:fix_bugfix

  

agent:fix_conflicts

  

agent:fix_security

  

agent:gap_analysis

  

agent:implement

  

agent:implement

Queue implement gate

  

agent:implement

Queue implement gate

  

agent:open_issues

  

agent:ready

  

agent:research

  

agent:security_audit

  

agent:verify

  

architecture

Architecture

  

backend

API and services

  

blocker:hybrid

Blocks hybrid launch

  

blocker:launch

Blocks launch

  

blocker:limit-orders

Blocks limits launch

  

blocker:v2

Blocks v2

  

block:log_only

  

block:security

  

bug

  

ci

  

contracts

Smart contracts

  

correctness

  

deploy

  

dev

  

devops

  

docs

  

documentation

  

duplicate

  

e2e

  

enhancement

  

epic

Epic

  

feature

  

frontend

  

functional-completion

Functional

  

gas

  

good first issue

  

governance

  

help wanted

  

high-risk

  

hooks

  

hybrid

Hybrid swaps

  

indexer

Indexer

  

infra

CI/CD

  

infrastructure

  

integrators

  

invalid

  

launch-blocker

  

limit-orders

Limit orders

  

localnet

  

localterra

  

low priority

  

missing-implementation

Implementation

  

needs-design

Design decision required

  

ops

  

performance

  

priority

high

  

priority

medium

  

product

  

qa

  

QA

  

question

  

ready

Definition of Ready

  

ready

Definition of Ready

  

research

  

scripts

  

security

Security

  

security-hardening

Security hardening

  

smartcontracts

  

tech-debt

  

testing

Testing

  

ux

  

UX

  

v2

Pool-only swaps

  

verification

  

wontfix

No labels

agent:fix_bugfix

agent:fix_conflicts

agent:fix_security

agent:gap_analysis

agent:implement

agent:implement

agent:implement

agent:open_issues

agent:ready

agent:research

agent:security_audit

agent:verify

architecture

backend

blocker:hybrid

blocker:launch

blocker:limit-orders

blocker:v2

block:log_only

block:security

bug

ci

contracts

correctness

deploy

dev

devops

docs

documentation

duplicate

e2e

enhancement

epic

feature

frontend

functional-completion

gas

good first issue

governance

help wanted

high-risk

hooks

hybrid

indexer

infra

infrastructure

integrators

invalid

launch-blocker

limit-orders

localnet

localterra

low priority

missing-implementation

needs-design

ops

performance

priority

high

priority

medium

product

qa

QA

question

ready

ready

research

scripts

security

security-hardening

smartcontracts

tech-debt

testing

ux

UX

v2

verification

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

code/cl8y-dex-terraclassic#662

No description provided.