Design spec for duckdb general index for dascore 0.2

[d-chambers]

DuckDB Spool Indexer

Schema

meta_data

Index-level metadata and compatibility information.

• what_is_this
  • Constant identity string for quick sanity checks, eg dascore_duckdb_index.
• index_version
  • Schema/index version used to validate compatibility.
• dascore_version
  • DASCore version used to create or last update the index.
• last_indexed_ns
  • Epoch nanoseconds for the last successful index mutation.

sources

One row per indexed source. A source is one FiberIO scan unit. It will usually be a file, but may also be a directory or another non-overlapping FiberIO-backed entity.

• source_id
  • Surrogate primary key for joins and cleanup.
• base_uri
  • Nullable common root/prefix for this source when one exists. Useful for remote/common-URI sources. Informational only for local spools.
• source_path
  • Path identifying the source. Relative when a stable base exists, otherwise absolute.
• source_format
  • FiberIO format name used to read/scan the source.
• format_version
  • Format version understood by the selected FiberIO.
• mtime_ns
  • Source-level modified time in epoch nanoseconds. V1 incremental updates depend on this being meaningful.
• last_indexed_ns
  • Epoch nanoseconds when this source was last indexed.

Notes:

• Natural source identity is source_path, interpreted relative to base_uri when base_uri is present.
• Incremental update() in v1 is only defined for source types that can provide a meaningful source-level mtime_ns.

patches

One row per patch summary emitted by a source.

• patch_id
  • Surrogate primary key for joins.
• source_id
  • Foreign key to sources.
• source_patch_id
  • Patch identity within a source. Empty string is allowed for single-patch sources.
• n_dims
  • Number of dimensions in the patch.
• sample_count_total
  • Total number of samples/elements represented by the patch.
• dims
  • Comma-separated dimension names, no spaces.
• shape
  • Serialized patch shape.
• station
  • Promoted patch attr for fast equality filtering.
• network
  • Promoted patch attr for fast equality filtering.
• channel
  • Promoted patch attr for fast equality filtering once standardized in PatchAttrs.
• tag
  • Promoted patch attr for fast equality/glob filtering.
• data_type
  • Promoted patch attr for fast equality filtering.
• data_category
  • Promoted patch attr for fast equality filtering.
• time_min
  • Promoted lower bound for the time coord.
• time_max
  • Promoted upper bound for the time coord.
• time_step
  • Promoted sampling interval for the time coord when available.
• distance_min
  • Promoted lower bound for the distance coord.
• distance_max
  • Promoted upper bound for the distance coord.
• distance_step
  • Promoted sampling interval for the distance coord when available.

Constraint:

• Unique (source_id, source_patch_id).

Notes:

• Natural patch identity is (source_id, source_patch_id).
• Promoted attrs live only on patches; they are excluded from attr_index.

attr_index

Typed key/value index for non-promoted patch attrs.

• patch_id
  • Foreign key to patches.
• attr_name
  • Attr key name.
• value_kind
  • Dispatcher for which typed value column is active.
• units
  • Nullable normalized unit string for unit-bearing attrs and scalar quantities.
• value_str
  • String value storage.
• value_int
  • Integer value storage.
• value_num
  • Floating-point value storage.
• value_bool
  • Boolean value storage.
• value_time_ns
  • Absolute time attr stored as epoch nanoseconds.
• value_duration_ns
  • Duration attr stored as nanoseconds.

Constraint:

• Primary key (patch_id, attr_name).

Notes:

• attr_index stores only non-promoted, non-private, non-history attrs.
• Complex attrs are skipped rather than serialized ambiguously.
• Serialization contract:
  • str -> value_kind = str, value_str
  • bool -> value_kind = bool, value_bool
  • int -> value_kind = int, value_int
  • float -> value_kind = float, value_num
  • datetime-like -> value_kind = time, value_time_ns
  • timedelta-like -> value_kind = duration, value_duration_ns
  • pure unit -> value_kind = unit, value_str and units
  • scalar quantity -> value_kind = quantity, value_num and units

coord_index

Dispatch table for coord summaries.

• coord_entry_id
  • Surrogate primary key for one coord representation row.
• patch_id
  • Foreign key to patches.
• coord_name
  • Logical coord name, eg time, distance, lag_time.
• coord_dtype
  • Coord dtype summary string.
• coord_dims
  • Comma-separated coord dims.
• coord_len
  • Coord length from CoordSummary.
• coord_hash
  • Optional coord-defined semantic hash after normalization.
• units
  • Nullable normalized unit string for the coord representation.
• payload_table
  • Dispatch target table name: coord_time, coord_numeric, or coord_str.
• payload_id
  • Row id in the dispatched payload table.

Constraint:

• Unique (patch_id, coord_name, payload_table).

Notes:

• coord_hash is optional and not required for v1 query behavior.
• payload_table is a physical dispatch field, not an extra semantic type system.

coord_time

Summary-only payload table for time-like coords.

• id
  • Primary key referenced by coord_index.payload_id.
• min
  • Lower bound of the coord.
• max
  • Upper bound of the coord.
• step
  • Nullable time step; null means do not assume even sampling.
• count
  • Number of coord elements represented by the summary.
• is_monotonic
  • Whether the coord is monotonic.
• is_relative
  • false for absolute epoch-based time, true for duration-like relative time.

coord_numeric

Summary-only payload table for numeric coords.

• id
  • Primary key referenced by coord_index.payload_id.
• min
  • Lower bound of the coord.
• max
  • Upper bound of the coord.
• step
  • Nullable numeric step; null means do not assume even sampling.
• count
  • Number of coord elements represented by the summary.
• is_monotonic
  • Whether the coord is monotonic.

coord_str

Summary-only payload table for string coords.

• id
  • Primary key referenced by coord_index.payload_id.
• min
  • Lower lexical bound when meaningful.
• max
  • Upper lexical bound when meaningful.
• count
  • Number of coord elements represented by the summary.

Relations

meta_data

sources
  1
  |
  | source_id
  v
patches
  1 ----< attr_index
  |
  | 1 ----< coord_index ----> coord_time
  |                    \----> coord_numeric
  |                    \----> coord_str

Notes

• DuckDB is the replacement backend, not an experimental side path.
• The index is summary-only. Exact coord membership can be resolved by reading the patch.
• dc.scan(..., full_coords=True) is the cleaner future extension point for exact coord extraction.
• Common spool predicates should use promoted patches columns where possible.
• Attr predicates use attr_index.
• Coord predicates use coord_index plus the resolved payload table.
• SQL pushdown follows DASCore selector semantics on a best-effort basis.
• If pushdown cannot faithfully express a selector, filtering can fall back to pandas/post-query filtering.
• String matching semantics:
  • str selector -> Unix glob semantics
  • re.Pattern selector -> regex semantics
  • collection of strings -> exact membership semantics
• Summary-based coord filtering may produce false positives but should avoid false negatives for summary-representable predicates.
• Final query results are projected back into one flat patch-row relation before converting to pandas for DirectorySpool.
• The flat patch-row relation is reconstructed on demand from the normalized tables and is not stored as a separate materialized table.
• update() should stay cheap and should not perform full archive reconciliation.
• reconcile() is a distinct operation:
  • compare sources on disk to the sources table
  • add missing indexed sources
  • remove stale indexed sources no longer present
  • do not rescan existing sources just because mtime_ns changed
• Cleanup is explicit, source-scoped, and driven from source_id.
• Deleting a source removes dependent patch, attr, coord, and payload rows in one transaction.
• Concurrent writes to the same index are not supported.
• update(), reconcile(), and rebuild() assume exclusive access to the index.
• Schema compatibility rule:
  • if the stored schema is older than the indexer’s minimum readable version, rebuild automatically with warning
  • if the stored schema is newer than the running code supports, warn that compatibility is not guaranteed
• Path normalization rule:
  • local directory spool: persist relative source_path, resolve against current spool root, do not rely on persisted local base_uri
  • remote/common-URI spool: persist base_uri, resolve source_path relative to it
  • no-common-root case: store absolute source_path, base_uri = null
• Ingest behavior:
  • successful source scans should still index patches when some metadata is missing
  • missing scalar summary values become nulls where allowed
  • unsupported attrs and unsupported coord representations are skipped
  • promoted fields may be null when unavailable
  • malformed patches within a multi-patch source may be skipped with warning while the rest of the source is indexed
  • source-level scan failure fails the source
• Multi-patch source updates must be source-scoped and transactional:
  • rescan one source
  • replace all patch rows for that source
  • replace all attr/coord rows for those patches
  • never update patch-by-patch

TODO

• Add channel to the standard PatchAttrs defaults and treat it as a first-class attr, like station, network, and tag.
• Ensure channel participates consistently in scan summaries, querying, and promoted patches columns once added to PatchAttrs.