Obsidian Bases (.base) on published sites

Obsidian semantics

A .base file is YAML with a two-level structure: a set of top-level keys
(filters / formulas / properties) that apply to the whole base, and a
views[] array where each view inherits the base-level query and adds its own
filters (ANDed with the base ones), column order, sort, and limit. Real
example from docs/demo/example_base.base — note the two views in one file:

# --- base level: shared by every view below ---
formulas:
  content: formula.content
  Untitled: ""
properties:
  formula.content:
    displayName: content
# --- per-view level: each inherits the base level, adds its own ---
views:
  - type: table
    name: Table
    filters:
      and:
        - telegram_publish_at != ""
  - type: table
    name: telegram notes
    filters:
      and:
        - "!telegram_publish_at.isEmpty()"
    order:                       # selected/projected columns, in order
      - formula.Untitled
      - file.backlinks
      - title
    sort:
      - property: file.name
        direction: DESC

Key parts of the schema:

So "make two tables out of one query" is the native Obsidian model: two entries
in views[], both inheriting the base-level filter/properties. There is no
cross-file inheritance in Obsidian (no extends: [[other.base]]); that is a
deliberate non-goal here too (see Inheritance),
which keeps .base files portable.

How it'll be used on a published trip2g site

• A standalone page at /tasks.base showing a live table of notes that match
  a filter (e.g. "all posts with status: published, sorted by date"). Static,
  server-rendered, crawlable.
• JSON for the same data at the same path with Accept: application/json (or
  /tasks.base.json) — the owner explicitly wants ".json access to the data".
  This also feeds pagination for large embedded tables.
• Embedded inside an article via ![[tasks.base]] — an SSR table the author
  drops into a post, JS layered on top for client-side sort/filter.
• As a magazine data source — magazine_source: [[tasks.base]] so the
  magazine renders that query's rows as cards.

Query engine that already exists (and why we still add an index)

internal/templateviews/NoteQuery (query.go:21-249) is a lazy in-memory query
builder over the whole note set:

• NVS.ByGlob(pattern) / NVS.Query() entry points (nvs.go:192,200).
• SortBy(field) (reflection over Note methods) and SortByMeta(field) (sort
  by arbitrary frontmatter property, query.go:36-42,197-208).
• Limit / Offset paging (query.go:60,66).
• compareValues (query.go:213-248) handles string/int/int64/float64/time.Time.

internal/defaulttemplate/magazine.go already renders a glob-filtered +
property-sorted + property-included/excluded note collection as cards
(MagazineItems() at :29), driven by magazine_* frontmatter (template.go:305-344).

Why this isn't enough for bases: frontmatter is opaque in SQLite —
note_versions.content (db/schema.sql:83-91) is raw text; the frontmatter is only
parsed into an in-memory RawMeta map[string]interface{} (model.NoteView.RawMeta,
populated at loader.go:756). There is no frontmatter column and no way to SQL
filter, sort, group, or page by property. The in-memory NoteQuery does all of
this in Go by reflecting over the whole note set on every request, with no
WHERE-style predicate. The owner-chosen architecture closes that gap with a
persistent property index (next section), so bases run as real SQL queries with
cheap LIMIT/OFFSET pagination. NoteQuery stays as-is for magazine and other
in-memory consumers; bases get a new SQL-backed query path (we are not rewriting
all of NoteQuery).

Data flow for the standalone page

1. Request /tasks.base hits rendernotepage.
2. Resolve finds the note by path (extension preserved).
3. endpoint.go detects .base, but instead of the stub it now: reads
   resp.Note.Base, builds a SQL query from the (base-level ∧ view-level) spec
   against the type-appropriate per-type value table(s), applies the per-viewer
   access-control predicate, runs it (with LIMIT/OFFSET), and renders a Bases
   template func (WriteBase) into the default-template Ctx.
4. The page emits a static <table> + a cards section + a JSON data-island
   <script type="application/json"> and a conditionally-injected glue script.

Chosen schema — five per-type tables (mirror form_*_values)

Two shapes matched the form_* precedent. The owner chose (A), per-type tables,
to mirror the established house pattern:

• (A) per-type tables (note_string_values / note_int_values / …) — direct
  extension of form_*_values. Chosen. string/int/bool are the exact mirror
  of form_string_values/form_int_values/form_bool_values (db/schema.sql:737-754);
  real (floats) and date (a unix-epoch integer, for clean date sort/range — the
  magazine "sort by date" use case) are the frontmatter additions. The new column the
  form_* tables lack is seq, a list index for multi-valued props (tags etc.):
  one row per element, so contains becomes an equality probe.
• (B) one table with a value_kind discriminator + typed columns — one row per
  (note version, property), the kind tells which typed column is live. Rejected.
  Its one merit was co-locating a property's heterogeneous values in a single row, so
  a value that compares as string or number or date lives in one place. It was
  reconsidered for that flexibility, then dropped: the owner prefers to mirror the
  established form_*_values house pattern rather than introduce a one-off
  discriminator shape. See the trade-off this choice accepts below.

-- PROPOSED — DO NOT CREATE YET (pending owner confirmation)
-- A single migration file (db/migrations/<ts>_create_note_property_values.sql)
-- creates all five tables + five indexes.
CREATE TABLE note_string_values (
  version_id integer not null references note_versions(id) on delete cascade,
  prop       text    not null,
  value      text    not null,
  seq        integer not null default 0,
  primary key (version_id, prop, seq)
);
CREATE TABLE note_int_values (
  version_id integer not null references note_versions(id) on delete cascade,
  prop       text    not null,
  value      integer not null,
  seq        integer not null default 0,
  primary key (version_id, prop, seq)
);
CREATE TABLE note_real_values (
  version_id integer not null references note_versions(id) on delete cascade,
  prop       text    not null,
  value      real    not null,
  seq        integer not null default 0,
  primary key (version_id, prop, seq)
);
CREATE TABLE note_bool_values (
  version_id integer not null references note_versions(id) on delete cascade,
  prop       text    not null,
  value      integer not null,            -- 0 | 1
  seq        integer not null default 0,
  primary key (version_id, prop, seq)
);
CREATE TABLE note_date_values (
  version_id integer not null references note_versions(id) on delete cascade,
  prop       text    not null,
  value      integer not null,            -- unix epoch seconds
  seq        integer not null default 0,
  primary key (version_id, prop, seq)
);
CREATE INDEX idx_note_string_values_prop on note_string_values(prop, value);
CREATE INDEX idx_note_int_values_prop    on note_int_values(prop, value);
CREATE INDEX idx_note_real_values_prop   on note_real_values(prop, value);
CREATE INDEX idx_note_bool_values_prop   on note_bool_values(prop, value);
CREATE INDEX idx_note_date_values_prop   on note_date_values(prop, value);

Keying by version_id (referencing note_versions(id)) matches the established
per-version pattern of note_version_assets, note_version_embeddings, etc. — the
index is content-addressed by note version, so a content change naturally produces a
new key set.

Why these indexes: each (prop, value) composite index makes WHERE prop = ? AND value <op> ? an index range scan over the type-appropriate table, and serves
ORDER BY value / GROUP BY value for the queried property. LIMIT/OFFSET
pagination then walks the index without a full scan. Multi-valued props (lists like
tags) get one row per element via seq, so contains is an equality probe.

Accepted trade-off — type lives in the table, not in the row

The per-type split has a cost the single-table design avoided: the query path must
pick the table by the filter's value type, and one property can land in different
tables across notes. If priority: 2 is an int in one note but priority: "2" is a
string in another, the two values sit in note_int_values and note_string_values
respectively — so priority > 2 queries note_int_values and misses the
string-typed row. (The discriminator design sidestepped this by keeping a property's
values co-located.)

Resolution:

• Classify each value deterministically at ingest — decide its type once when the
  index is populated, and write it to exactly one of the five tables.
• Pick the table by the comparison literal's type in the query path: a numeric
  literal (> 2) targets note_int_values/note_real_values, a quoted literal
  targets note_string_values, a date literal targets note_date_values, etc.
• Warn on type-mismatched props — when the same property is observed with
  inconsistent types across notes, surface it as an indexing/parse warning so authors
  can normalize their frontmatter, rather than silently dropping rows.

Where the index is populated

Frontmatter is parsed at note load into the in-memory RawMeta map
(loader.go:756, pp.RawMeta = rawMeta). The in-memory note set itself is rebuilt
on every reload via noteloader.Load(), driven by the app's PrepareLatestNotes /
PrepareLiveNotes (cmd/server/main.go:492,508) — which are called after writes
(pushnotes → insertnote → PrepareLatestNotes, pushnotes/resolve.go:83) and
after every admin mutation that touches notes.

Populate the index in the same reload, treating it as derived data — rebuilt
from the in-memory set, never hand-maintained:

• After Load() produces the NoteViews, write each note's RawMeta into the
  appropriate per-type table — classify each value (string/int/real/bool/date) and
  route it to note_string_values / note_int_values / note_real_values /
  note_bool_values / note_date_values, keyed by its version_id (one batch per
  note, fanning out across the five tables).
• Keep it in sync the way other derived data is: a reload replaces the rows for the
  affected versions (delete-by-version across all five tables, then re-insert), so the
  index can never drift from the loaded content. A full reload rebuilds the whole
  index; a partial reload rebuilds only the changed versions.
• The .base files themselves go through registerRawFile (loader.go:270) and
  carry RawMeta = {} — they are query definitions, not query subjects, so they
  contribute nothing to the index. The index is built from the markdown notes
  that finishPage produces.

This is the realistic, low-risk population point: it reuses the existing reload
lifecycle (the same one the magazine already depends on for fresh data) rather than
threading index writes through every individual mutation case.

sqlc workflow

Queries are split read/write per CLAUDE.md: read queries go in queries.read.sql,
writes in queries.write.sql (both at repo root; sqlc.yaml points at them), then
make sqlc regenerates internal/db/queries.read.sql.go / queries.write.sql.go.
For bases:

• write (queries.write.sql): per-type delete + insert pairs, e.g.
  DeleteNoteStringValuesByVersion / InsertNoteStringValue (and the same for int,
  real, bool, date) — batch-friendly.
• read (queries.read.sql): the base query is dynamic (filters/sort/columns
  come from the spec, and the target table depends on the value type), so it can't be
  a single static sqlc query. Build it as parameterized SQL in the new query path, or
  express the common predicates as a few composable sqlc queries the path stitches
  together. Static helpers like per-type counts can still live in queries.read.sql.

The query layer that becomes SQL-backed is a new path for bases — not a rewrite
of templateviews.NoteQuery. The magazine (MagazineItems()) could later migrate to
the same index, but that is out of scope for v1.

1. New parser package internal/obsidianbase

Mirror internal/obsidiancanvas/canvas.go structurally: pure parsing, no
business logic, no query execution. Crucially, the parser must model the native
two-level Obsidian structure — base-level filters/formulas/properties plus a
views[] array, where each view inherits the base level (see
What a .base is).

// internal/obsidianbase/base.go
package obsidianbase

type ViewType string
const (
    ViewTable ViewType = "table"
    ViewCards ViewType = "cards"
)

// Base is the two-level spec: top-level keys shared by every view, plus Views.
type Base struct {
    Filters    *FilterNode               // top-level filter, inherited by all views
    Properties map[string]PropertyConfig // top-level displayName etc., inherited
    Formulas   map[string]string         // top-level raw exprs; not evaluated in v1
    Views      []View
}

type View struct {
    Name    string
    Type    ViewType
    Filters *FilterNode   // view-specific; effective filter = Base.Filters AND View.Filters
    Columns []string      // from `order`: projected props + file.* built-ins
    Sort    []SortKey     // {Property, Desc}
    GroupBy string
    Limit   int           // becomes SQL LIMIT; 0 = unlimited
}

type FilterNode struct {
    And  []*FilterNode
    Or   []*FilterNode
    Not  *FilterNode
    Expr *FilterExpr      // leaf: property op value
}

type FilterExpr struct {
    Property string
    Op       FilterOp     // Eq, Ne, Exists, NotEmpty, Contains, Gt, Lt, ...
    Value    string
}

func Parse(raw []byte) (*Base, error) { /* yaml.Unmarshal + normalize */ }

// EffectiveFilter ANDs the base-level filter with a view's own filter.
func (b *Base) EffectiveFilter(v View) *FilterNode { /* AND(b.Filters, v.Filters) */ }

Inheritance is the parser's job: EffectiveFilter (or the query path) combines
Base.Filters with each View.Filters via AND, and base-level Properties apply to
every view unless a view overrides a column's config. There is no extends:
key — cross-file inheritance is a non-goal (see
Inheritance).

Parsing notes:

• Use the YAML lib already in the tree (frontmatter uses goldmark-meta; check
  go.mod for yaml.v3 before adding a dep).
• The expression mini-language (!prop.isEmpty(), prop != "",
  status == "done") needs a small tokenizer. v1: support the common shapes
  (==, !=, prop != "" → NotEmpty, prop.isEmpty() → empty/exists). Document
  unsupported expressions as a parse warning, not a hard error.
• file.* built-ins (file.name, file.backlinks, file.mtime) map to Note
  methods, not RawMeta.

2. Store the spec on the note

// internal/model/note.go (next to Canvas at :282-284)
// Base holds the parsed Obsidian .base spec for .base files.
Base *obsidianbase.Base `json:"-"`

// internal/mdloader/loader.go:286-293  (add a sibling branch)
if strings.HasSuffix(strings.ToLower(src.Path), ".base") {
    base, err := obsidianbase.Parse(src.Content)
    if err != nil {
        ldr.log.Warn("base parse failed", "path", src.Path, "error", err)
    } else {
        nv.Base = base
    }
}

3. SQL query path for bases (against the per-type value tables)

The base spec compiles to SQL over the property index (see Property
index), not to in-memory
NoteQuery predicates. Add a small query builder (e.g. internal/basesquery or a
helper in the renderer's package) that takes *obsidianbase.Base + a view + the
viewer and produces a parameterized SQL statement. Each predicate selects the table
that matches the comparison literal's type (see the trade-off
above):

• Filter → WHERE — walk the EffectiveFilter tree (base ∧ view) into a
  WHERE clause, using EXISTS/JOIN subqueries per property predicate against the
  type-appropriate table. Map operators: Eq/Ne → =/<>, Exists/NotEmpty →
  EXISTS (SELECT 1 FROM note_string_values … WHERE prop = ? AND value <> ''),
  Contains → equality probe against the multi-valued seq rows, ordered comparisons
  → note_int_values/note_real_values/note_date_values depending on the literal.
• Sort → ORDER BY on value in the type-appropriate table for each sort key;
  the table choice makes dates and numbers order correctly (the index avoids the
  in-memory compareValues coercion problem — types are decided once at ingest, not
  per comparison).
• GroupBy → GROUP BY on value in the grouped property's type table.
• Limit/Offset → LIMIT ? OFFSET ? straight from the view's Limit and the
  page request — cheap paging for both the page and embeds.
• Projection — select the row identity (version_id → resolve to *Note) plus
  the projected columns named in order; file.* built-ins resolve from the
  *Note after the query, not from the index.

Type coercion that the old in-memory plan had to do at compare time
(compareValues, query.go:213-248, only handled same-type branches) now happens
once at ingest: when a property is indexed, classify its value (date →
note_date_values as unix epoch, float → note_real_values, int →
note_int_values, etc.) and write it to one table, so the SQL comparison is already
type-correct. If the in-memory v0 spike is built first, keep the coerce(a, b any)
helper note from that path; it disappears once the index lands.

4. Render-hook wiring (the insertion point)

Replace the .base arm of the stub at endpoint.go:96-103. Keep the .canvas /
.excalidraw stub behavior; branch .base to a real renderer:

if ext := unsupportedFileExt(resp.Note.Path); ext != "" {
    if ext == ".base" && resp.Note.Base != nil {
        // resolveBaseRows runs the SQL query against the per-type value tables
        // with the per-viewer access-control predicate, paged by LIMIT/OFFSET.
        rows := resolveBaseRows(ctx, env, resp)
        dtCtx := buildDefaultTemplateCtx(req, layoutParams, resp, env)
        dtCtx.Base = &defaulttemplate.BaseView{Spec: resp.Note.Base, Rows: rows}
        defaulttemplate.WriteRender(ctx, dtCtx)
        return nil, nil
    }
    // unchanged stub for .canvas / .excalidraw (and unparsed .base)
    ...
}

Add Ctx.Base + a WriteBase quicktemplate func (see Frontend) and gate the JSON
content-negotiation here (see JSON access).

5. Narrow accessor for conditional glue (copy the chart/mermaid pattern)

Add Note.HasBaseEmbed() / Note.IsBase() accessors on templateviews/note.go
(next to HasCharts() at :169). In buildDefaultTemplateCtx
(endpoint.go:475; the chart.js / mermaid.js / codeblock.js appends are at
:507-513), append /assets/base.js only when the page is a .base page or
contains a ![[*.base]] embed:

// endpoint.go ~:513 (after the codeblock.js append)
if note.IsBase() || note.HasBaseEmbed() {
    jsURLs = append(jsURLs, env.AssetURL("/assets/base.js"))
}

Glue bundle assets/base/ (copy assets/mermaid/)

Each widget bundle in the repo is the same shape: src/ + esbuild.browser.mjs

• a dedicated npm script + an entry in embed.go. assets/base/ follows suit:

v1 is intentionally light — most work is server-side. The .base table is fully
rendered server-side; JS only enhances (sort/filter/pagination). No heavy lib,
so no lib.ts global like mermaid's echarts.min.js. Theme: follow
window.trip2g_theme_listeners only if the widget needs theme-reactive styling
(table is CSS-themed, so likely unnecessary).

Build wiring

// package.json scripts (next to "mermaid", "codeblock" at :18-20)
"base": "node assets/base/esbuild.browser.mjs"

// assets/embed.go:8 — add base.js to the //go:embed list
//go:embed ... mermaid.js mermaid.min.js codeblock.js base.js ...

npm run base builds the artifact. Not npm run build (that's tsc+vite for
the $mol admin app, per the mermaid doc and CLAUDE.md). Commit assets/base.js.

Templates + CSS

• internal/defaulttemplate/views.html: add WriteBase / Base(ctx)
  quicktemplate funcs that render the <table data-base> (thead from
  Columns, tbody from Rows) and a cards section. Reuse the magazine card
  markup (magazine.go items → same .magazine/card classes) for the cards
  view so they share styling. Emit a JSON data-island
  <script class="base__data" type="application/json">…</script> exactly like the
  chart widget data-island (<script type="application/json" class="widget__data">,
  generated at views.html.go:839).
• After editing views.html, run go generate ./internal/defaulttemplate/...
  and commit the regenerated views.html.go (the stub UnsupportedFile lives at
  views.html:527, generated StreamUnsupportedFile at views.html.go:2314).
• CSS for the table goes in assets/defaulttemplate/src/index.scss; compile with
  npm run defaulttemplate-css. Cards reuse existing magazine SCSS.

(a) magazine_source: [[tasks.base]]

Let the magazine take a .base as its data source — decouple what data
(the base query) from how it looks (magazine cards). Today
MagazineItems() (magazine.go:29) reads magazine_include_files,
magazine_sort_property, etc. (template.go:305-344) and builds its own in-memory
query.

Plan: if magazine_source resolves to a .base note, build the magazine's item
list from that base's resolved rows instead of the magazine_* knobs. The
base owns the filter/sort; the magazine owns the card layout. The existing
magazine_* properties remain as the no-base fallback. (Longer term, the magazine's
own query could move to the property index too — out of scope for v1.)

(b) Magazine presets (product cleanup)

The owner finds the current magazine config "too for enthusiasts" — the
magazine_* knobs (template.go:305-344) are too much tuning. Propose named
presets:

magazine_layout: blog   # blog | grid | list | masonry

Each preset sets sensible defaults (featured-first vs. uniform grid, card density,
image ratio). Authors pick a preset instead of hand-tuning. Combined with (a):
bases = data, preset = presentation. The granular knobs stay available for
power users but stop being the default surface.

Resolved by the owner

• Query architecture → persistent SQL property index, five per-type value tables
  (note_string_values / note_int_values / note_real_values /
  note_bool_values / note_date_values), mirroring the form_*_values house
  pattern — not a single value_kind table and not in-memory NoteQuery. The
  in-memory approach is kept only as a possible v0 spike. See Property
  index. (Carries a new SQL
  migration — pending owner confirmation before creation.)
• Locked rows → hidden by default, per viewer. Anonymous/unauthorized viewers
  see only readable rows; locked rows are dropped (no teaser, no title leak). An
  optional paywall_rows: hide | show-locked knob (default hide) is recorded for
  later but is not v1. See Access control.
• "Two tables" → two views in one .base via native in-file inheritance; no
  cross-file extends. See Inheritance.

Still open (with recommendations)

1. Draft vs. live note-set semantics for the index/query.
   The page already picks LatestNoteViews() vs LiveNoteViews() per request
   (resolve.go:196-211, set on response.Notes at :235/:259). The index is
   keyed by version_id, so it can hold both draft and live versions. Recommendation:
   a base queries the same version set as the page it renders on — filter the
   SQL by the same version ids the page would expose, so a published base never
   surfaces unpublished drafts to visitors, and admins previewing ?version=latest
   see drafts.

2. Filter expression coverage in v1.
   Recommendation: support ==, !=, exists/isEmpty()/!…isEmpty(),
   contains (list props), and ordered comparison (typed at ingest). Emit a parse
   warning for unsupported expressions (e.g. regex, arbitrary formulas) rather
   than failing the whole base.

3. JSON delivery shape.
   Recommendation: ship /tasks.base.json (suffix) first; add Accept-based
   negotiation once template_content_type.md's ResponseWriter lands.

4. Dynamic vs. static base query in sqlc.
   The base query is spec-driven, so it can't be one static sqlc query.
   Recommendation: build parameterized SQL in the new query path (static sqlc
   queries only for the index write/delete and simple counts). Revisit if the
   dynamic SQL gets unwieldy.