Folder system architecture

This page describes the folder system that is currently implemented. It explains how AID guarantees folder behavior. For everyday instructions, see Using folders. For the reasoning behind the choices, see the canonical records listed under Decision sources.

System guarantees

The folder system maintains these guarantees:

• every workspace commits with exactly one structural root folder;
• every document belongs to exactly one folder;
• a folder cannot point to a parent in another workspace;
• active sibling folders and documents share one case-insensitive name space;
• creating, renaming, uploading, and trashing cannot silently overwrite an existing entry;
• trashing a folder includes its complete active subtree in one transaction;
• readable paths are for navigation, while UUIDs remain the authoritative identity for commands and relationships;
• unsupported listing cursors cause a clean restart rather than mixing two incompatible sets of pages.

Data model

workspace
  └── folder (one structural root)
        ├── folder
        │     ├── folder
        │     └── document
        └── document

active folder/document ──owns──> entry_name_claim

Folders and containment

The folder table stores one parent_folder_id per folder. This adjacency list is the structural truth. The root has a NULL parent; every other folder has a parent in the same workspace.

document.folder_id is mandatory. A composite foreign key carries the workspace id with the folder id, preventing a document or child folder from attaching to another tenant's tree.

AID does not currently store a materialized folder path. If move or permission inheritance later needs one, it will be a derived representation based on immutable ancestor ids. It will not replace the parent relationship as the source of truth.

Exactly one root

SQL Server enforces at most one root with a filtered unique index. Guard triggers prevent deleting a root, re-parenting it, changing its workspace, or turning an ordinary folder into another root. SQL Server has no deferred constraint, so provisioning owns the existence half of the invariant and must create the workspace and root in the same transaction.

The root is structural: listings do not return it as a child, and user commands cannot rename or trash it.

Names and shared ownership

Normalization and validation

Before storage, AID trims surrounding whitespace and Unicode NFC-normalizes the requested name. A valid name is non-empty, no longer than 255 Unicode scalar values, and cannot contain /, \\, control characters, . or ...

SQL Server generates a persisted name_key with lower(name). The entered spelling remains in name for display. Equality, sibling uniqueness, path resolution, listing order, listing cursors, and substring filtering use the folded key under the configured case-insensitive, accent-sensitive collation.

The API enforces the exact 255-Unicode-scalar limit. The columns are nvarchar(510) so 255 supplementary characters fit as surrogate pairs; the database check supplies a broader UTF-16 defense and uses DATALENGTH to reject trailing-space padding that LEN would ignore.

Cross-kind name claims

Folder and document records remain in separate tables, so their individual unique indexes cannot enforce one shared namespace. entry_name_claim owns that cross-kind rule.

Each active non-root entry has one claim identified by:

(workspace_id, parent_folder_id, name_key)

That key is unique. A second folder or document cannot acquire the same active sibling slot. A second unique constraint also prevents one entry from owning multiple claims.

Database triggers create, change, and release claims when an entry is created, renamed, moved, trashed, restored, or deleted. This covers application commands, imports, and direct SQL rather than relying on every caller to remember a procedural convention. Same-kind partial unique indexes remain as defense in depth.

The application role can read claims for an early user-friendly rejection but is explicitly denied direct claim DML. SQL Server security policies filter reads by workspace. Set-based sync triggers execute as the database owner, so their maintenance succeeds without any caller-controlled bypass flag.

An existing active entry owns its slot. A competing create, upload, rename, move, or future restore is rejected. Under concurrency, the claim's unique constraint decides the race: the transaction that commits the slot first wins and the other rolls back.

Command transactions

Create folder

The create command:

1. verifies workspace membership and write capability;
2. takes the short workspace entry-tree transaction lock;
3. locks and rechecks the active parent;
4. walks the parent chain to enforce the 32-level limit;
5. checks the shared name claim for an explanatory early rejection;
6. inserts the folder, allowing the database trigger and unique constraints to make the authoritative decision.

The preflight improves the response but is not trusted for integrity. A race that starts after the check still ends as nameCollision through the database constraint.

Rename

Rename locks the target row and compares the submitted expectedRevision with the current revision. A stale command returns revisionConflict instead of overwriting a colleague's newer edit.

The command checks the destination claim while excluding the target's current claim, then updates the entry. The claim trigger changes ownership inside the same transaction. A conflicting concurrent transaction produces nameCollision and rolls everything back.

Trash a subtree

Trash is one atomic command across all explicit targets and all descendants of any targeted folder.

The transaction:

1. takes the workspace entry-tree lock so an application create or upload cannot add a child during expansion;
2. locks every explicit folder and document target;
3. validates target existence, active state, root protection, and expected revisions;
4. walks and locks every active descendant folder, bounded by the hierarchy limit;
5. locks active documents directly selected or contained in the subtree;
6. marks all collected rows as trashed and increments their revisions;
7. verifies the number of updated rows before committing.

Any failed precondition or invariant rolls back the entire transaction. Claim triggers release the names as the rows become trashed. No user can observe a successfully committed half-subtree.

Restore is not implemented yet. Its accepted contract is also atomic: an active entry that owns any required name keeps it, and a conflict rejects the complete subtree restore.

Upload into a folder

Upload has an intentionally asymmetric storage and database sequence:

1. authorize the workspace and verify the active target folder before reading the request body;
2. normalize the filename and check the shared claim before blob persistence;
3. hash and persist the private blob;
4. start the authoritative transaction, take the entry-tree lock, and recheck the folder and name claim;
5. create the document and immutable first version with pending security status, then connect it as the current version.

The second check is necessary because another transaction may claim the name after the cheap preflight. A deterministic rollback such as a vanished folder, same-kind unique violation, or cross-kind UploadNameCollision deletes the persisted orphan blob.

An infrastructure failure near COMMIT is different: SQL Server may have committed even when the application did not receive confirmation. AID retains that blob for reconciliation rather than risk leaving a committed document pointing to deleted content. Retention and any failed cleanup attempt are logged for operators.

Listing and cursor recovery

Listings are one deterministic sequence with two regions:

1. folders ordered by (name_key, id);
2. documents ordered by (name_key, id).

The requested direction applies inside both regions. The UUID is the final tie-breaker so equal folded names cannot make ordering unstable. Filtering by name uses the same folded expression and is therefore case-insensitive and accent-sensitive.

The opaque v3 cursor records the region, last folded key, and UUID. It is bound to the workspace, folder, direction, filter, and format version. The server validates its length, shape, UUID, version, and listing scope before using it in SQL.

A malformed, foreign, or retired cursor returns HTTP 400 with:

{ "error": "invalidCursor" }

The HTTP adapter converts that envelope to a typed recovery signal. The listing hook requests page one without a cursor, removes all pages from the old cursor era from the TanStack Query cache, and continues from the restarted listing. It never combines old and new cursor formats in one visible result.

Listings are not snapshots. If an entry moves across a cursor boundary during paging it may temporarily be repeated or absent. The client reconciles repeated UUIDs by keeping the highest revision, and normal refresh discovers entries that moved ahead of the cursor.

Identity, authorization, and tenant isolation

Readable folder paths make navigation understandable, but they do not grant authority. The server verifies active workspace membership and the required capability before resolving names or executing commands.

Application commands and relationships use UUIDs. A stale readable path may stop resolving after a rename, while the folder UUID remains the target of an in-flight command. Document UUID links remain stable across folder renames and future moves.

Workspace context is set for each database transaction. Folder, document, and claim tables use row-level security, while composite foreign keys prevent cross-workspace parentage even in privileged data paths.

Failure behavior

Product-facing rejections are result values, not generic infrastructure errors. The web application displays trash and naming conflicts instead of silently refetching an unchanged screen.

Migration and rollout

The pre-production database is a clean MSSQL baseline: one generated schema migration followed by one custom migration for security policies, runtime grants, claim synchronization, timestamps, and root guards. No legacy data is converted.

From the first production deployment onward, database changes must support N-1 application instances through expand, backfill, and contract phases unless a separate maintenance-window decision approves otherwise. SQL Server or collation changes require case-comparison regression tests and a collision audit before rollout.

Verification coverage

Automated coverage includes:

• schema shape, tenant foreign keys, folded indexes, and claim constraints;
• v3 cursor encoding, binding, validation, and old-version rejection;
• structured invalid-cursor translation, cache replacement, and restart;
• upload cleanup for deterministic cross-kind collisions and retention for ambiguous commit outcomes;
• mixed-kind trash preconditions and atomic subtree behavior;
• case-only folder/document collisions;
• visible trash conflict notices.

The complete chain is rehearsed on a clean SQL Server 2022 database. The database-backed suite also executes as a non-privileged runtime principal and covers RLS isolation, owner-executing claim triggers, bypass attempts, Unicode name limits, and trailing-space rejection.

Current boundaries

• Folder move is not implemented. It must prevent cycles, enforce destination claims, revalidate subtree depth, and commit the complete subtree change or nothing.
• Restore is not implemented. Its name ownership and atomicity behavior is already settled.
• Derived path encoding and permission-inheritance traversal remain open until move and access query requirements provide evidence for the representation.
• Retention, legal hold, hard disposal, and DB-aware blob reconciliation are separate lifecycle work.

Decision sources

The canonical records remain the authority when this summary and an implementation detail disagree:

• docs/adr/0001-identifier-based-resource-addressing.md
• docs/adr/0002-single-file-upload-ingestion-slice.md
• docs/adr/0004-folder-hierarchy-first-slice.md
• docs/adr/0005-case-insensitive-entry-names.md
• docs/design/file-explorer-data.md
• docs/design/document-ingestion-integrity.md
• docs/design/production-principles.md

The Drizzle schemas and migrations under apps/api define the currently implemented database shape. Consultant blueprints are historical input for open decisions and are not implementation authority.