sense-bases

A .base file is YAML: filters selecting notes, formulas computing values, and views ordering and grouping them. Obsidian evaluates it against its own metadata cache; sense holds the same data in SQL tables. Every Bases construct that selects or computes rows has a SQL equivalent. What has none is presentation (columnSize, card layout), those change pixels, not rows, so a translation loses nothing by ignoring them.

Translate one view to one query: base-level filters AND the view's filters, the view's order/sort/limit as the SELECT list, ORDER BY, and LIMIT. A base with four views becomes four queries; saving them under the base's name in sense.config.json's queries block keeps them runnable as sense <name>. The general query surface is the sense skill; this one only maps Bases constructs onto it.

Properties

file.ctime is the filesystem's creation time. A git clone or a copy resets it, so on a tree that arrived as a checkout, every ctime is the checkout date and age formulas are wrong about authorship without being wrong about the filesystem. map warns when modification times have this shape; creation times share it.

Predicates

A field no note in the tree declares has no column at all, so a filter naming it errors with no such column instead of treating every row as empty. Obsidian's evaluator returns empty for unknown properties; SQL does not. Dropping the clause states the same thing the error did, and SELECT name FROM pragmatableinfo('frontmatter') lists what exists.

isEmpty() has four true cases because empty is stored three ways: NULL (key absent), '' (empty string value), '[]' (a list written []), and '[null]' (a list key above a bare -). Obsidian's isEmpty() is true for all of them; IS NULL alone finds only the first. The IN list is the whole test. jsonarraylength() is not: it reads '[null]' as length 1 and throws on plain strings. The same trap inside a list: jsoneach hands a string member to value as plain text, so jsontype(value) throws malformed JSON on it; the scan's own type column is the discriminator.

contains(link("Movies")) compares against a link value. In frontmatter, a list of links holds the written text, so the json_each comparison value is the literal [[Movies]]. hasLink compares resolved paths: links.dst is the resolved target (NULL for dead links), so the comparison value is the target's path, not its display name.

sense's own has(field, x) is looser than both contains variants: exact membership on JSON arrays but substring on strings, so has(f.status, 'active') also matches inactive. It reads shorter when the field is known to be a list; the json_each form is the exact translation.

Formulas

Formulas are SELECT expressions. The Bases functions map onto SQLite's:

Bases durations are typed; SQL date arithmetic is julianday day-fractions. A formula chaining duration fields (.days.round()) flattens to arithmetic on the julianday difference.

A formula referencing another formula becomes a CTE layer: SQL cannot read a SELECT alias in the same SELECT list, so each dependency level computes its formulas as columns and the next level reads them (WITH t AS (SELECT ..., <level-1 formulas> FROM frontmatter) SELECT ..., <level-2 formulas> FROM t). A five-formula chain is however many levels it has, not five CTEs. Formulas that only read base columns share one layer.

Views

• sort: (multi-key, each with direction) -> ORDER BY a DESC, b ASC. limit: -> LIMIT.
• groupBy does not collapse rows. Obsidian shows every row bucketed under headers with

per-group summaries. The SQL producing the same rows and numbers is ordering plus window functions, not GROUP BY:

``sql SELECT f.path, f.status, f.days, AVG(f.days) OVER (PARTITION BY f.status) AS groupavg, COUNT(*) OVER (PARTITION BY f.status) AS groupn FROM frontmatter f WHERE f.days IS NOT NULL ORDER BY f.status, f.days DESC ``

A collapsed one-row-per-group report is plain GROUP BY. That is a different result than the Bases view shows.

• View summaries (Sum, Average, Median via ordering, Unique, Filled, Checked) are the

matching aggregates, windowed as above to keep the rows, or a separate aggregate query.

this

this is the note the base is evaluated against: the embedding note, or Obsidian's active pane. sense has no pane, so the caller supplies the path as a bound parameter: sense sql "..." <path>, or a saved query run as sense <name> <path>. A CTE keeps it single-bind:

WITH me AS (SELECT ? AS p)

and every this.file.* expression joins me. The full Related.base translation in EXAMPLES.md is the worked case: link overlap, mutual-link filters, and the shared-target list via GROUP_CONCAT, all from one bound path.

Not translatable, and why it costs nothing

• columnSize, card/table/list/map chrome: pixel layout over the same rows.
• Obsidian choosing this from the active pane: there is no pane; the path parameter is the

resolution.

• Live re-evaluation as files change: every sense query reconciles against the filesystem

first, so the freshness is already there; nothing re-renders on its own.