Markdown Dashboards (datachart blocks)

Task A — Scheduled refresh (chart_ttl cron) — NOT DONE

Problem. chart_ttl is documented but not enforced. fetched_at is written to chart_data_cache but never compared to a TTL, and there is no cron. So url/internal data is fetched once (at note load, on a cache miss) and never refreshes on a schedule — only when the note's version_id changes (edit). Wrong for live metrics.

Already correct: the initial fetch happens at render time (noteloader.Load → chartRenderer.renderChart → chartdata.ChartRows → cache miss → EnqueueChartDataRefresh → job → cache → debounced reload), not on reader request (the page serves cached HTML).

Build:

1. Cronjob internal/case/cronjob/refreshstalecharts/{job.go,resolve.go} (pattern: internal/case/cronjob/cleanupapikeylogs/), ~every minute; register in internal/cronjobs/jobs.go.
2. Each run: iterate the loaded notes' url/internal charts (NoteView.Charts + the note's RawMeta["chart_ttl"]). For each: read the cache row's fetched_at, parse chart_ttl (Go duration; absent = no auto-refresh), and if now - fetched_at > ttl → EnqueueChartDataRefresh. Job caches → reloadLoop re-renders.
3. Put the staleness logic in chartdata (e.g. func (c *ChartData) RefreshStale(...)), don't duplicate in the cron. Extend chartdata.Env so it can read fetched_at (today CachedChartData returns only the data string). Add a compile-time check.
4. Per-chart TTL: read RawMeta["chart_ttl"] per note, or attach the parsed TTL to each NoteViewChart in extractCharts.

Accept: url chart with chart_ttl: 1m re-fetches ~every minute (new fetched_at); without it, no scheduled refresh.

Task B — internal SQL source — NOT DONE

Problem. data.source = "internal" renders a loader only. chartdata.ChartRows enqueues a refresh only for ChartSourceURL; the job only does HTTP. Full design is the Internal SQL source section below — implement it.

Build (per that section):

1. note_version_frontmatters(version_id, data json) — persist RawMeta as JSON at push/sync (queryable via SQLite JSON1; shared with Obsidian Bases).
2. internal_sql_tables(table_name, min_sync_interval_secs, synced_at) — admin-managed allowlist + cadence.
3. internal/internalsql/ — non-durable filtered read replica: lazy per-table sync (ATTACH main read-only → CREATE TABLE AS SELECT), debounced by synced_at. Security by construction (only allowlisted tables exist).
4. Internal fetch path: run data.sql against the replica (SELECT-only + LIMIT + timeout), cache rows.
5. Extend chartdata.ChartRows miss-enqueue to cover ChartSourceInternal.
6. Admin CRUD for internal_sql_tables.

Accept: internal chart renders notes-by-status; queries can't reach non-allowlisted tables (e.g. secrets).

Task C — Obsidian in-editor preview — NOT DONE

Why. The fenced ```datachart block was chosen over a <DataChart />-style
component precisely because a fenced block is Obsidian's native extension point
(registerMarkdownCodeBlockProcessor). That payoff is only realized once the
plugin actually renders it: authors should see the chart in Obsidian —
WYSIWYG, identical to the published page — instead of staring at raw JSON. This
closes the loop (write in Obsidian → preview in Obsidian → publish unchanged) and
turns the editor into the test harness for charts.

Build (component #9 below):

1. In obsidian-sync/src/main.ts, register
   registerMarkdownCodeBlockProcessor("datachart", …).
2. Parse the block's JSON, then reuse the shared widget from assets/chart/
   so in-editor rendering is byte-for-byte the site's — don't re-implement ECharts
   drawing in the plugin.
3. Resolve data.source in-editor:
   • inline → draw immediately from rows.
   • frontmatter → resolve the [[link]] against the vault and read the local
     file (.csv/.json) directly — no presigned URL needed inside Obsidian.
   • url/internal → fetch via requestUrl if reachable, else draw a
     placeholder ("server-fetched on publish"); these depend on trip2g's cache/
     replica, which the plugin can't fully reproduce.
4. (Optional) a .datachart.json file view for standalone preview of data assets.

Accept: opening a note with a ```datachart block in Obsidian shows the
rendered chart (at least for inline/frontmatter), matching the published page;
no separate "view on site" round-trip needed to check a chart.

frontmatter source — data from a vault file (client-fetched)

The data file is referenced from frontmatter, not inside the code block,
because Obsidian gives frontmatter [[links]] real link support — highlighting,
autocomplete, graph edges — which a link buried in a ```code fence does not
(verified in Obsidian). The block just names the key:

---
chart_sales: "[[sales.datachart.csv]]"      # Obsidian-native link property
---

At render trip2g reads RawMeta["chart_sales"], extracts the wikilink target,
resolves it to the asset's presigned URL via currentPage.AssetReplaces[target]
(the same ResolveWikilink mechanism, internal/mdloader/link_resolver.go:33),
and emits <div class="chart" data-src="<url>" data-format="csv|json"> + the
inline config. The browser fetches the data — no server-side read, no
GetAssetContent, no memory/streaming concerns (a .csv/.json is served like
any other asset, gated by the same presigned URL as the note's images).

• .json referenced file → array used directly as dataset.source.
• .csv → the widget parses it (header row + numeric coercion → 2D source);
  chart type comes from the block's config.series[].type.
• Dependency tracking: the asset must be marked as a note dependency so it
  gets a presigned URL. Body [[links]] are marked by findAssets; frontmatter
  links need an explicit mark (open item — verify whether frontmatter wikilinks
  are already registered as asset deps; if not, add it during extractCharts).

Dropped: the standalone ![[x.datachart.json]] embed and the server-side
GetAssetContent read. Superseded by data.source = "frontmatter" +
client-fetch — simpler, Obsidian-friendly, and avoids reading asset bytes
through the app.

Security by construction: a filtered read replica

Raw SELECT against the main DB is unacceptable — it holds secrets, users,
payment data for the whole instance. Instead of sandboxing queries (SQL parsing,
view whitelists — fragile), isolate by data: internal SQL runs against a
separate replica SQLite file that contains only allowlisted tables. A query
physically cannot reach secrets because that table isn't in the replica.

The allowlist lives in a DB table (admin-manageable at runtime, no env/restart):

CREATE TABLE internal_sql_tables (
  table_name             text    primary key,
  min_sync_interval_secs integer not null default 300,
  synced_at              integer not null default 0,   -- unix secs, last replica sync
  created_at             datetime not null default current_timestamp
);

Empty table → nothing queryable → feature off. No separate enable flag needed:
the rows are both the allowlist and the on-switch.

Lazy sync (no timer — rides the chart refresh)

The replica is rebuilt inside the refresh_chart_data job, just before an
internal query, debounced per table by synced_at:

for row in internal_sql_tables:
  if now - row.synced_at > row.min_sync_interval_secs:
     BEGIN;
       replica: DROP TABLE IF EXISTS t; CREATE TABLE t AS SELECT * FROM main.t;  -- main ATTACHed read-only
     COMMIT;                                                                      -- atomic swap, no torn reads
     row.synced_at = now

• Demand-driven: tables sync only when an internal chart actually needs them;
  a burst of refreshes shares one sync (per-table synced_at guard).
• Full copy is fine at target scale (10–50k rows): CREATE TABLE AS SELECT
  ≈ 10–50 ms, auto-handles schema drift. Reconsider incremental only at millions
  of rows / hundreds of MB / seconds-scale cadence.
• Replica is disposable → open it non-durable for speed:
  PRAGMA journal_mode=MEMORY; PRAGMA synchronous=OFF; (rebuildable from main).
• Isolation bonus: heavy GROUP BY runs on the replica, never locking the
  main write DB — the same R/W-split principle as docs/dev/sqlite.md, one step
  further.

Mixed freshness: a chart joining two tables with different intervals sees one
fresher than the other (fine for dashboards; align intervals if a chart needs
cross-table consistency).

Frontmatter index — the content substrate

To chart over note metadata (status, tags, category), frontmatter must be
queryable, but today note_versions stores only raw content; frontmatter
is parsed in-memory only. Persist it:

CREATE TABLE note_version_frontmatters (
  version_id integer primary key references note_versions(id) on delete cascade,
  data       text    not null   -- RawMeta serialized as JSON
);

Populated at push/sync (where the YAML is already parsed). Query with SQLite
JSON1: json_extract(data,'$.status'), json_each(data,'$.tags'). Add
note_version_frontmatters to internal_sql_tables and dashboards over content
work:

SELECT json_extract(f.data,'$.status') AS status, count(*) AS n
  FROM note_version_frontmatters f
  JOIN <live-version filter>
 GROUP BY status

This index is shared with Obsidian Bases (docs/dev/obsidian_bases.md), which
also needs a queryable frontmatter index — build it once, both features win.

Config

Render-time semantics

Look up (version_id, chart_hash):

Refresh is triggered by exactly two events: a version bump (job enqueued at
publish time) and a render that observes staleness. Nobody views the page →
nothing refetches. There is no polling cron for freshness.

No feedback loop: the job writes fetched_at = now before publishing the
noteChange. The triggered reload then sees a fresh row and does not re-enqueue.
This ordering is covered by a test.

Cache cleanup (cron)

Delete chart_data_cache rows whose version_id is no longer the live version
of any note. Daily schedule (e.g. "0 0 2 * * *"). Exact "live version" join to
be finalized against the note-version source of truth during implementation
(detail flagged, not blocking).

Live update

No new infrastructure — reuse the existing noteChanges GraphQL SSE subscription
and notebus fan-out (docs/dev/obsidian_sse_pulls.md; resolver
internal/graph/* NoteChanges :289). The public page already subscribes via
$trip2g_user_live (assets/ui/user/live/live.view.ts:4), gated by the existing
opt-in toggle $trip2g_user_live_reload_toggler (persisted in $mol_state_local
as trip2g_live_reload). Step 3's job publishing a noteChange{update} for the
note path is the entire wiring; the page reloads and re-reads the fresh cache.

Internal-source track (optional, can land independently)

10. Frontmatter index — note_version_frontmatters table + populate at
    push/sync (RawMeta → JSON). Unlocks content queries; shared with Bases.
11. Filtered replica — internal_sql_tables table + non-durable replica +
    lazy per-table sync (internal/internalsql/). Tests first: allowlist
    enforcement, atomic swap, debounce by synced_at.
12. internal data source — wire dataSource:"internal" + dataSql into the
    refresh job (SELECT-only guard, LIMIT, timeout); same cache/TTL/notify.
13. Admin CRUD for internal_sql_tables (with synced_at display).