Collections and Edges

Resources in an OmniData container are not flat. They exist in hierarchies (collections), have relationships with each other (edges), and carry labels (tags). These tables live in memory.db and provide the graph structure that turns a bag of resources into an organized knowledge base.

Database location

Collections, edges, and tags are stored in memory.db, not index.db. This separation keeps the knowledge graph independent from the resource index, allowing independent compaction, backup, and migration of each database.

Because resources live in index.db and graph structures live in memory.db, references between them are logical — not enforced by SQLite foreign keys. The resource_id columns in these tables refer to omnidata_resources.id in index.db, but no cross-database FK constraint exists. Implementations must handle referential integrity at the application layer.

Collections

The omnidata_collections table represents named groupings — folders, threads, projects, albums, or any organizational container.

CREATE TABLE omnidata_collections (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    parent_id   TEXT REFERENCES omnidata_collections(id),
    description TEXT,
    metadata    TEXT DEFAULT '{}',
    created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    deleted_at  TEXT
);

Collections support nesting via the parent_id self-reference. A collection with parent_id = NULL is a root collection. This allows arbitrary depth: a project contains folders, a folder contains subfolders.

Resources are placed into collections using edges (see below) with the contains relationship type.

Edges

The omnidata_edges table stores typed, directed relationships between resources and/or collections.

CREATE TABLE omnidata_edges (
    id          TEXT PRIMARY KEY,
    source_id   TEXT NOT NULL,
    target_id   TEXT NOT NULL,
    edge_type   TEXT NOT NULL,
    metadata    TEXT DEFAULT '{}',
    created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    deleted_at  TEXT
);

The source_id and target_id columns may reference either a collection ID (within memory.db) or a resource ID (in index.db). The edge type determines the expected referent types.

Edge types

Edges are directed: source_id -> target_id. The edge_type determines the semantic meaning of the direction. For example, a contains edge flows from collection to resource; a replies_to edge flows from the reply to the original message.

Cross-database queries

Since edges in memory.db reference resource IDs in index.db, querying the full graph requires opening both databases. In SQLite, use ATTACH DATABASE to join across them:

ATTACH DATABASE 'index.db' AS idx;

-- Find all resources in a collection
SELECT r.* FROM idx.omnidata_resources r
JOIN omnidata_edges e ON e.target_id = r.id
WHERE e.source_id = ? AND e.edge_type = 'contains' AND e.deleted_at IS NULL;

Or in application code, query memory.db for the edge set, then look up matching resources in index.db:

# Step 1: get resource IDs from memory.db
edge_rows = memory_conn.execute("""
    SELECT target_id FROM omnidata_edges
    WHERE source_id = ? AND edge_type = 'contains' AND deleted_at IS NULL
""", (collection_id,)).fetchall()
resource_ids = [row[0] for row in edge_rows]

# Step 2: fetch resources from index.db
placeholders = ",".join("?" * len(resource_ids))
resources = index_conn.execute(f"""
    SELECT * FROM omnidata_resources
    WHERE id IN ({placeholders}) AND deleted_at IS NULL
""", resource_ids).fetchall()

Tags

The omnidata_tags table provides flat labels for resources. Tags are simpler than collections — no hierarchy, no nesting, just a string attached to a resource.

CREATE TABLE omnidata_tags (
    id          TEXT PRIMARY KEY,
    resource_id TEXT NOT NULL,
    tag         TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    deleted_at  TEXT
);

Note that resource_id references omnidata_resources.id in index.db. The same cross-database reference pattern applies here — no FK constraint, application-layer integrity.

Tags are useful for cross-cutting concerns: "important", "follow-up", "quarterly-review", "shared-with-team". A resource can have many tags, and the same tag string can appear on many resources.

ATTACH DATABASE 'index.db' AS idx;

-- Find all resources tagged "important"
SELECT r.* FROM idx.omnidata_resources r
JOIN omnidata_tags t ON t.resource_id = r.id
WHERE t.tag = 'important' AND t.deleted_at IS NULL AND r.deleted_at IS NULL;