When I started building Alexandrie, I just wanted a fast, offline Markdown note-taking app I could rely on daily. But as the project grew — with nested categories, shared workspaces, and access permissions — it evolved into something much more: a open-source knowledge management platform powered by Nuxt 4 and Go.

This article walks through one of the toughest challenges I faced: how to model and manage hierarchical data efficiently, from the database to a reactive Pinia store.

1. Unified Data Model: “Nodes” Over Multiple Tables

Early in development, I realized that managing categories, documents, and files as separate entities was becoming painful.

Every new feature — especially sharing and permissions — required deeper joins and complex recursive queries.

Here’s what the early structure looked like conceptually:

Workspace
├── Category A
│    ├── Document 1
│    └── Document 2
└── Category B
├── Subcategory
│    └── Document 3

1.1 The problem with separate tables

Having multiple tables (categories, documents, resources) worked fine until I introduced access control.

At that point, even simple questions like “what can this user see in this subtree?” required multiple recursive joins.

Performance and maintainability started to suffer.

1.2 The unified “nodes” approach

The solution was to merge everything into a single table — a unified model where everything is a node.

nodes (
id BIGINT PK,
user_id BIGINT,
parent_id BIGINT NULL,
role SMALLINT,  // workspace=1, category=2, document=3, resource=4
name VARCHAR,
content LONGTEXT NULL,
content_compiled LONGTEXT NULL,
order INT,
created_at TIMESTAMP,
updated_at TIMESTAMP
)

With this model, every element — document, folder, file — became a node. This made the hierarchy recursive but uniform, enabling simple queries like:

“What can user X access in this subtree?”

Now, permission propagation and tree traversal both use a single recursive CTE or indexed path column, drastically improving maintainability and speed.

2. Scalable Permission System

Building permissions on top of the nodes table required careful thought. I adopted a hybrid approach:

A separate permissions table maps user_id, node_id, permission_level.

On access checks, the system checks:

• If the user owns the target node
• If a direct permission exists
• If any ancestor node grants sufficient permission

This balances fine-grained control and inheritance — users get access to everything under a node they’ve been granted permission for, without repeated recursive checks.

3. Backend Stack: Go + REST + Modular Design

Language & framework: Go (Gin) — lightweight and performant for API endpoints.

• Language: Go (Gin) — for its simplicity, performance, and clean REST design.
• Database: MySQL (or compatible) — raw SQL for critical queries like subtree retrieval.
• File storage: S3-compatible (MinIO, RustFS) — abstracted via a pluggable service for self-hosted setups.

The API uses a flat structure (/nodes, /permissions, /users) — simple, predictable, and easy to version.

I separated business logic from data access (DAO layer) to keep the backend maintainable and extensible. This paid off when refactoring: new storage backends or permission engines can now be added with minimal changes.

4. Frontend Architecture: Data management

Surprisingly, the hardest part of building Alexandrie’s frontend wasn’t the UI — it was the data layer. Representing thousands of interlinked notes in a reactive, permission-aware tree required careful design.

In Alexandrie, everything is a Node. A node can be a workspace, category, document, or resource — and each can contain others. That means infinite nesting, partial hydration, and live updates when any part of the tree changes.

The Challenge

In Alexandrie, everything is a Node. Each node can be a workspace, category, document, or resource, and every node can contain others — effectively forming a tree structure that must remain reactive, searchable, and permission-aware across the app.

The main challenges:

• Nested data: Users can nest documents/categories/resources infinitely deep.
• Partial hydration: Nodes are often fetched lazily or shared publicly, so the store must handle both partial and fully-hydrated nodes.
• Permission inheritance: Access rights propagate through parent nodes.
• Real-time reactivity: Any node update must immediately reflect across trees, search results, and UI components.
• Performance: Traversing large trees shouldn’t cause noticeable slowdowns.

The Solution

I built a dedicated Pinia store (useNodesStore) combined with a TreeStructure utility that keeps all nodes in a flat Collection (essentially a reactive Map), and reconstructs the hierarchical tree on demand.

export const useNodesStore = defineStore('nodes', {
state: () => ({
nodes: new Collection<string, Node>(),
allTags: [] as string[],
isFetching: false,
}),
getters: {
getById: state => (id: string) => state.nodes.get(id),
getChilds: state => (id: string) => state.nodes.filter(c => c.parent_id === id),
getAllChildrens: state => (id: string) => { /* recursive logic */ },
},
actions: {
async fetch() { /* lazy hydration of nodes */ },
async update(node) { /* merges partial and full states */ },
async delete(id) { /* removes entire subtree */ }
}
});

Then, a dedicated TreeStructure class handles building the actual trees efficiently:

export class TreeStructure {
private itemMap = new Map<string, Item>();
private childrenMap = new Map<string, Item[]>();

constructor(items: Item[]) {
for (const item of items) {
if (!this.childrenMap.has(item.parent_id || ''))
this.childrenMap.set(item.parent_id || '', []);
this.childrenMap.get(item.parent_id!)!.push(item);
this.itemMap.set(item.id, item);
}
}

public generateTree(): Item[] {
return this.items
.filter(item => !item.parent_id)
.map(root => this.buildTree(root, new Set()));
}
}

This approach gives:

• O(1) access to any node via itemMap
• Efficient subtree generation (only rebuild what’s needed)
• A clean way to filter, search, or recompute derived data (tags, permissions, etc.)
• Integration with Pinia’s reactivity: the entire graph updates live when a node changes.

Lessons & Trade-offs

Here are some high-level takeaways:

Unify your data model early. Splitting multiple tables will bite you when adding features like permissions and sharing.

Favor simplicity in API contracts. Flat endpoints scale better than deeply nested resource structures.

Design for extensibility, not just immediate features. Adding plugin-like syntax blocks or alternate storage later becomes much easier.

Permissions and hierarchy are hard. Caching accessible node sets and flattening ancestors helps avoid recursive query bottlenecks.

What’s Next & How to Contribute

Alexandrie is open-source and welcomes contributors. Areas where help is most welcome:

• UI/UX improvements
• Implement full offline support
• Add some cool new features

If you’re interested in self-hosted knowledge tools or modern note apps, feel free to star or contribute!

GitHub: https://github.com/Smaug6739/Alexandrie