Model directories as first-class records in the index DB (enable empty dirs, faster tree/list, cheap folder moves)

[phernandez]

Summary

Model directories as first-class records in the index DB instead of deriving them purely from file paths. Today there is no directory concept at all, which makes empty directories unrepresentable and forces a placeholder-file kludge in the cloud (see basicmachines-co/basic-memory-cloud#1014).

Background — why this is needed

DirectoryService.get_directory_tree() calls entity_repository.find_all() and synthesizes directory nodes by splitting each file's file_path. There is no directories table and no directory entity — a folder exists only as a path-prefix of some file. Consequences:

• Empty directories can't exist. No file under a prefix ⇒ no node ⇒ the folder is invisible.
• The cloud works around this by writing a placeholder note (.keep) into new folders. Because EntitySchema.file_path regenerates the filename from the (sanitized, kebab'd) title, .keep is stored as keep.md with title .keep, gets indexed as a real note, and then has to be filtered out of the UI per-call. That filter is fragile and already leaks (cloud #1014).
• Tree/list reads don't scale. get_directory_tree() loads every entity and rebuilds the whole tree in Python on each call — a full scan for large (12k–18k file) projects.
• Folder move/rename rewrites file_path on every descendant entity.

Goal

A directory is a real row in the index. The directory tree is the union of (a) directories derived from file paths and (b) explicit directory records, so empty folders are representable, reads are level-scoped, and structure ops are cheap on the index side.

Hard constraints

1. SQLite and Postgres must both be supported. basic-memory is local-first (SQLite) and cloud (per-tenant Postgres). So Postgres ltree is not a portable option — either avoid it, or treat it as a Postgres-only optimization behind a SQLite-compatible fallback (two implementations). Default to a backend-agnostic model: adjacency list (parent_id) + a materialized path text column (prefix queries + depth integer for level scoping). This works identically on both engines.
2. Files keep their actual directory paths — local and cloud. No id-addressed/opaque storage. The bucket (cloud) and the folder on disk (local) must remain a readable mirror of the human path tree. So a folder move still physically moves the underlying files; the directory model makes the index side cheap and the reads fast, it does not eliminate the file moves.

Proposed design (sketch — open to iteration)

Schema

• New directories table: id, project_id, parent_id (adjacency), name, path (materialized full path, text), depth (int). Index on (project_id, parent_id) and (project_id, path).
• entity gains directory_id FK (its containing directory). file_path stays as the real path / storage key.
• Recommend a separate table over an is_directory flag on entity: folders have no content, no frontmatter, no search vector, and different move/lifecycle semantics; mixing taxes every entity query.

Directory tree / list

• get_directory_tree() / list_directory(dir, depth) become level-scoped queries (WHERE parent_id = :dir / WHERE path LIKE :prefix AND depth = :n) instead of find_all() + full Python rebuild. Big win for large projects; enables lazy per-level loading.

Empty-directory persistence (durable across reindex/restore)

• Adopt the S3-native directory-marker convention: an empty folder is a zero-byte object whose key ends in / (e.g. notes/2024/empty/) — the same thing the S3 console writes for "New folder." Its basename is empty, so it is unambiguously a directory, not a file.
• Sync recognizes a trailing-slash key as a directory and creates a directories row — it must NOT be parsed as markdown, given a title, or indexed as a note.
• Locally, an empty directory on disk maps to the same directories row (no marker file needed; the real empty dir is the source of truth).

Move / rename

• Index side: structural update (re-point parent_id, rewrite path/depth for the moved subtree — one set-based statement).
• Storage side: still moves the underlying files (unavoidable, S3 has no rename / local is a real mv) — should run in the background, not the request path (mirrors cloud #1012).

Migration

• Convert existing keep.md placeholders into directories rows (+ trailing-slash markers in cloud buckets) and stop creating them.

Out of scope / non-goals

• Decoupling storage keys from human paths (would break local-first readability) — explicitly not doing this (constraint Memory json import #2).
• ltree as the primary model (constraint Pre release #1) — only acceptable as an optional PG optimization with a SQLite-equivalent path.

Related

• Cloud web-UI bug this unblocks: basicmachines-co/basic-memory-cloud#1014 (empty folder leaks a .keep/keep.md note). A short-term filter fix is being applied cloud-side; this issue is the real fix.
• Cloud folder-move latency: basicmachines-co/basic-memory-cloud#1012 (server-side move-directory reindex still inline).

[claude]

Claude finished @phernandez's task in 2m 27s —— View job

---

Issue Triage

• Read and understand the issue
• Explore relevant codebase areas
• Check for duplicate issues
• Apply labels
• Write detailed triage analysis

---

Classification

Attribute	Value
Type	Feature request / Architectural enhancement
Priority	High
Components	Database/sync, MCP tools, Cloud, API
Complexity	Complex
Labels applied	enhancement, cloud, complex

---

Triage Analysis

No duplicates found. This is a novel architectural proposal with a clear problem statement.

Current Implementation (confirmed by codebase review)

The issue description is accurate. The current implementation in src/basic_memory/services/directory_service.py works as follows:

• get_directory_tree() calls entity_repository.find_all() and synthesizes directory nodes entirely in Python by splitting file_path strings — a full table scan on every call.
• get_distinct_directories() in entity_repository.py is a partial optimization (used by get_directory_structure()) but still derives directories from file paths via SQL string parsing.
• The entities table has no directory_id FK — directory structure is implicit in file_path strings.
• DirectoryNode schema (src/basic_memory/schemas/directory.py) already models the concept of directory vs file nodes, but these are computed transiently, never persisted.

Why Priority: High

1. Correctness gap: Empty directories are completely unrepresentable in the current model. The .keep/keep.md placeholder workaround (cloud #1014) is fragile and already leaking into the UI.
2. Scalability: At 12k–18k files (mentioned in the issue), find_all() on every list_directory call is a real bottleneck. The fix makes reads O(depth) rather than O(total files).
3. Folder moves: Rewriting file_path on every descendant entity is a heavy write path — this blocks performant server-side move-directory (cloud #1012).

Proposed Design Assessment

The proposed adjacency list + materialized path model is the right call for dual SQLite/Postgres support. A few notes from codebase context:

• The existing DirectoryNode Pydantic schema is already structured for directory-first thinking (separate type field, optional file-only fields) — the schema layer needs minimal changes.
• The three dependency injection variants for DirectoryService (get_directory_service, get_directory_service_v2, get_directory_service_v2_external in deps/services.py) will all need updating.
• The list_directory MCP tool routes through DirectoryClient → /v2/projects/{project_id}/directory/list — the API surface is already clean and isolated; the change can be made without touching MCP tool signatures.
• An Alembic migration is needed for: (a) the new directories table, (b) entity.directory_id FK column, and (c) backfill of existing rows.

Implementation Scope (rough breakdown)

1. Schema / Migration: New directories table, entity.directory_id FK, indexes. Alembic migration with backfill from existing file_path data and conversion of any keep.md placeholders.
2. Repository layer: New DirectoryRepository (or methods on existing repositories) for CRUD on directory rows, path-prefix queries, subtree queries.
3. Sync / watcher: Recognize trailing-slash keys (cloud) and empty dirs (local) as directory markers — create directories rows instead of attempting markdown parse.
4. DirectoryService: Replace find_all() + Python rebuild with level-scoped DB queries. get_directory_tree(dir, depth) becomes a WHERE parent_id = :dir or WHERE path LIKE :prefix AND depth <= :n query.
5. Move/rename: Index-side becomes a single set-based UPDATE; file moves remain (but can be backgrounded per cloud #1012).
6. Tests: Unit tests for new repository layer; integration tests for empty-dir round-trip, tree queries, move operations.
7. Cloud compatibility: Trailing-slash marker convention for S3/Tigris; stop creating .keep placeholders.

Open Questions / Risks

• Migration correctness: Backfilling directory_id for all existing entities requires a careful, idempotent migration. Large databases (18k files → potentially thousands of directory rows) need testing.
• Sync race conditions: If a directory is created/deleted while sync is running, the directories table could diverge from the file system. The sync coordinator needs to handle this atomically.
• depth column maintenance: On move, the entire subtree's depth values need updating — a set-based UPDATE on path LIKE :prefix handles this but requires careful index design.
• Postgres ltree as opt-in: The issue mentions this as acceptable with a SQLite fallback. Given the complexity, I'd suggest deferring ltree entirely and validating the adjacency list model first.

Recommendation

This is well-scoped and actionable. I'd suggest starting with:

1. A migration + DirectoryRepository that backfills from existing file paths (no behavior change yet).
2. Switching get_directory_tree() to use the new table.
3. Adding empty-directory support.
4. Tackling the cloud trailing-slash marker convention last (to coordinate with the cloud repo).

[phernandez]

Cloud dependency to clean up when this lands.

The cloud currently fakes empty folders with a placeholder note and filters it out in the UI. When directories become first-class here, that scaffolding in basic-memory-cloud should be removed as part of the cutover:

• apps/web-v2/app/api/notes.ts — createFolder() writes a .keep placeholder note (FOLDER_PLACEHOLDER_FILE_NAME); replace with a real "create directory" op.
• apps/cloud/.../services/web_v2_runtime.py — _is_folder_placeholder_file_node() + its call sites in _flatten_directory_tree / _directory_list_candidates / _to_explorer_children; the FOLDER_PLACEHOLDER_FILE_NAME constant.
• apps/web-v2/app/api/notes.ts — client-side isFolderPlaceholderNode / normalizeExplorerNodes filtering.
• Migration: the existing keep.md placeholders already written to tenant buckets need converting to real directory records / trailing-slash markers (not just dropped, or the folders vanish).

A short-term filter fix for the leak (basicmachines-co/basic-memory-cloud#1014) keys on the placeholder's title (.keep); that's the code that should be deleted here, not extended.