sense-bases

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

Bases	sense	notes
note.field / bare field	f.<field>	frontmatter column, same name
file.name	basename(f.path, '.md')	the Name column: no directory, no extension
a name with extension	basename(f.path)	the suffix arg is Unix basename's: present, stripped
file.folder	f.path LIKE 'Folder/%'	for inFolder("Folder")
file.ext	'md'	sense indexes only markdown
file.size	f._size	bytes
file.mtime	datetime(f._mtime / 1000, 'unixepoch')	stored as ms
file.ctime	datetime(f._ctime / 1000, 'unixepoch')	see the caveat below
file.tags	the tags table	frontmatter + inline #tags, deduplicated, Obsidian's grain
file.links	links WHERE embed = 0	one row per distinct written target
file.embeds	links WHERE embed = 1
file.backlinks	links WHERE dst = f.path
this	a bound ?	see below

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

Bases	sense
== != > < >= <=	same
&& || !	AND OR NOT
and: / or: / not: filter blocks	parenthesized AND / OR / NOT
field.isEmpty()	(f.field IS NULL OR f.field IN ('', '[]', '[null]'))
file.hasTag("book")	EXISTS (SELECT 1 FROM tags WHERE tags.path = f.path AND (tag = 'book' OR tag LIKE 'book/%'))
file.hasLink("Note")	EXISTS (SELECT 1 FROM links WHERE src = f.path AND dst = 'Note.md')
list.contains(x)	EXISTS (SELECT 1 FROM json_each(f.list) WHERE value = x)
string.contains(x)	instr(f.string, x) > 0
list.containsAny(...)	the json_each EXISTS with value IN (...)
/regex/.matches(x)	no SQLite regex; LIKE/GLOB cover anchored and wildcard shapes
value.isType("object")	json_each's own type column: ... FROM json_each(f.field) j WHERE j.type = 'object'

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 pragma_table_info('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. json_array_length() is not: it reads '[null]' as length 1 and throws on plain strings. The same trap inside a list: json_each hands a string member to value as plain text, so json_type(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	sense
if(c, a, b)	iif(c, a, b) or CASE WHEN
now() / today()	datetime('now') / date('now')
date(x)	datetime(x)
(date1 - date2).days	julianday(date1) - julianday(date2)
(now() - acquired).months	(julianday('now') - julianday(f.acquired)) / 30.44
x.round(n) / x.toFixed(n)	round(x, n) / printf('%.2f', x)
d.format("YYYY-MM-DD")	strftime('%Y-%m-%d', d)
x.toString()	CAST(x AS TEXT)
list.length	json_array_length(f.list) on a JSON column, a COUNT subquery on a table
list(a).filter(list(b).containsAny(value)).unique()	an EXISTS-joined subquery; worked example in EXAMPLES.md

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:

  SELECT f.path, f.status, f.days,
         AVG(f.days)  OVER (PARTITION BY f.status) AS group_avg,
         COUNT(*)     OVER (PARTITION BY f.status) AS group_n
  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: default CLI queries incrementally prepare the needed capabilities; --no-build reads the last completed generation and requires its requested readiness without scanning live files. Nothing re-renders on its own.