# Persistence & Architecture (/docs/developer-guide/development/architecture) All data lives in a single shared PostgreSQL database, used by `apps/server`. Schema follows ArchiMate 3 Open Exchange XSDs (`models/xsd/`). The test suite runs against [PGlite](https://pglite.dev) (Postgres compiled to WASM, in-memory) — full Postgres fidelity, no Docker required. ## Database schema [#database-schema] `packages/db/src/schema.ts` defines every table in one place, following an **Organisation → Workspace** hierarchy: `organizations` (`slug`, `name`, `isPersonal`, `enabled` — a suspension flag settable only by an Admin (`platform_admin`)), `organizationMembers` (`organizationId`, `userId` — a local (`local:`) or Keycloak (`sub`) identity id — and `role`: `"owner" | "editor" | "viewer"`), `organizationInvitations` (email-based invitations — `email`, `role`, `tokenHash` (SHA-256 of a random token, the clear-text token itself is never persisted), `expiresAt`/`sentAt`/`acceptedAt`/`revokedAt`; a partial unique index on `(organizationId, email)` restricted to rows where `acceptedAt IS NULL AND revokedAt IS NULL` enforces at most one active invitation per organization/e-mail pair — see [Organization invitations by e-mail](../reference/authentication.md#organization-invitations-by-e-mail)), `userActiveOrganization` (per-user pointer to the organization currently in use), `siteSettings` (login/banner messages), `apiTokens` (personal API tokens, each scoped to one `organizationId` and optionally pinned to one `workspaceId`), `workspaces` (each belonging to exactly one organization — `organizationId`; `createdById`, a local or Keycloak identity id, is traceability only and never used for access control), `users` (local username/password accounts, id `local:` — see [Local accounts](../reference/authentication.mdx#local-accounts)), `userActiveWorkspace` (per-user, per-organization pointer to the workspace currently in use), and the ArchiMate content tables (`elements`, `relationships`, `propertyDefinitions`, `elementProperties`, `relationshipProperties`, `views`, `nodes`, `connections`, `bendpoints`), all keyed by `workspace_id` with cascading foreign keys. A fourth role, Admin (Keycloak identifier `platform_admin`), is a Keycloak **realm** role (not an `organization_members` row) — it administers organizations, users, plugins and the image library (`/platform/**`, `/api/platform/**`, metadata only), gated by `withSuperAdmin` and independent of organization membership. For an organization's actual content (workspaces, elements, views, dashboards), platform\_admin has no special access: it goes through the exact same [`apps/server/lib/archimate/access.ts`](https://github.com/archispark/archispark/blob/main/apps/server/lib/archimate/access.ts) role/suspension checks as any other user, and must be a real `organization_members` row (which it can create for itself from `/platform/organizations/[id]`'s member management) to access anything. See [Authentication](../reference/authentication) for the full role matrix. Identities live either in the `users` table (id `local:`) or in Keycloak (id = the bare Keycloak `sub`) — the `local:` prefix keeps the two id spaces from ever colliding. `apiTokens.userId`/`organizationMembers.userId`/`workspaces.createdById` are FK-less text columns holding either form. `apiTokens.organizationId`/`workspaces.organizationId` are nullable at the DB level only during the expand→backfill→contract migration window (see [`packages/db/src/backfill-organizations.ts`](https://github.com/archispark/archispark/blob/main/packages/db/src/backfill-organizations.ts)); `pnpm migrate`/the self-hosted `migrate` job applies the backfill together with the schema migration (see [Deployment](../deployment)) — run before deploying code that assumes the column is always populated, so every row the application ever reads has one. To generate a migration after a schema change: ```bash cd packages/db npx drizzle-kit generate # writes to drizzle-pg/ ``` ## `apps/server` [#appsserver] `apps/server` is the single application — a Next.js app combining the workspace UI, the REST API, and the MCP server in one process and one deployment. It owns authentication (`requireAuth`, verifying a local or Keycloak access token — see [Local accounts](../reference/authentication.mdx#local-accounts) — or a personal API token), personal settings (`/api/me`, `/api/settings/api-tokens`, `/api/settings/messages`), organization/member management (`/api/organizations*`, `/api/platform/organizations*`), every ArchiMate modeling route (`/api/workspaces`, `/api/elements`, `/api/relationships`, `/api/views`, `/api/property-definitions`, `/api/export`, `/api/export/neo4j`, `/api/import`, `/api/openapi.json`, `/api/docs`), and the MCP transport (`/mcp/`, \~37 tools). REST routes are Next.js App Router Route Handlers (`app/api/**/route.ts`); the MCP transport is a Pages Router route (`pages/api/mcp.ts`) — the only exception, required because the MCP SDK's `StreamableHTTPServerTransport` needs a raw Node `http.IncomingMessage`/ `ServerResponse`, which only the Pages Router exposes. Every workspace/organization route resolves access through the single authorization gateway, [`apps/server/lib/archimate/access.ts`](https://github.com/archispark/archispark/blob/main/apps/server/lib/archimate/access.ts) (`resolveActiveContext`/`assertOrgAccess`/`assertWorkspaceAccess`) — a user sees and acts on every workspace of every organization they belong to, subject to their role (`owner`/`editor`: read+write, `viewer`: read-only). platform\_admin follows the exact same rule, based on its own real `organization_members` rows (see above) — none, by default. The MCP tools (`apps/server/lib/mcp/`) import `lib/archimate/store.ts`/ `registry.ts` directly — same process, same module graph, no HTTP hop and no package boundary to cross — authenticated via the same personal API tokens as the REST API. Self-hosted Docker: `apps/server` is the only application Compose service, reached through Traefik. Vercel: `apps/server` is its own project (root directory `apps/server`) — see [Vercel](deployment.md#vercel). ## Neo4j export [#neo4j-export] `POST /api/export/neo4j` (`apps/server/app/api/export/neo4j/route.ts`) reads the active workspace's model from PostgreSQL (`loadModel` / [`modelFromDb`](https://github.com/archispark/archispark/blob/main/packages/db/src/model-io.ts)) and rewrites it into a Neo4j graph, for reporting use cases that need graph queries rather than the relational model. Postgres stays the single source of truth — Neo4j is a disposable read-side copy, rebuilt on demand. The same logic is also reachable outside the HTTP API, without a session/token, via `pnpm import:workspace -- ` (one workspace) or `pnpm import:workspaces` (every workspace, one at a time — a single failure doesn't stop the others, and the script exits non-zero if any import failed). The write logic lives in `packages/db-neo4j` (`@workspace/db-neo4j`), a package mirroring `packages/db`'s shape (driver singleton, versioned schema migrations, `migrate:prod` script) but for the Neo4j service instead of Postgres: | Module | Role | | ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `mapping.ts` | Pure `(ArchiModel, organization) → Cypher parameters` transform. Resolves element/relationship property names via `propertyDefinitions` (keyed by `propertyDefUuid` in Postgres), flattens each view's node tree into the set of element ids it contains, rejects any relationship type unsafe to interpolate into a Cypher relationship type (not parameterizable in Cypher), and stamps every param with the workspace's Postgres `organizationId`. | | `import-model.ts` | Writes `Model`/`Element`/`Property`/`View` nodes and ArchiMate relationships as native Neo4j relationship types (`COMPOSITION`, `AGGREGATION`, `ASSIGNMENT`, `REALIZATION`, `SERVING`, `ACCESS`, `INFLUENCE`, `TRIGGERING`, `FLOW`, `SPECIALIZATION`, `ASSOCIATION` — same set as `RELATIONSHIP_TYPES` in `apps/server/lib/archimate/schemas.ts`), linked by `CONTAINS`. Element properties become `Property` nodes (`HAS_PROPERTY`); relationship properties are set natively on the relationship (`SET r += rel.properties`), since a Neo4j relationship can't be the endpoint of another relationship. Each import wipes and reloads only the subgraph reachable from `(:Model {id: workspace.uuid})` — another workspace's data is never touched. | | `schema/migrations/*.cypher` | Versioned, numbered Cypher migrations (constraints/indexes), tracked via `(:SchemaMigration {version})` nodes — same append-only convention as `packages/db/drizzle-pg/`, applied by `schema/migrate.ts` (no `drizzle-kit` equivalent for Neo4j); `ensureNeo4jSchema()` applies pending migrations once per process before every import. `0002_organization_index.cypher` adds the `organizationId` indexes (one per node label plus one per relationship type) and the `Organization.id` uniqueness constraint. | * **Multi-tenancy**: `importModelToNeo4j(model, organization)` takes the workspace's Postgres organization (`{id, slug, name}`) as an explicit second argument — it's not part of `ArchiModel`, which stays a pure Postgres representation. Every write merges an `(:Organization {id})` node (shared across that organization's workspaces, `MERGE`d — never deleted by a single workspace's wipe-and-reload) linked to the `:Model` via `HAS_MODEL`, and additionally stamps `organizationId` directly on `Model`/`Element`/`View`/`Property` nodes and on every relationship, so tenant-scoped reporting queries (`MATCH (n:Element {organizationId: $id})`) can filter by index without traversing from `:Model` first. Both call sites (`POST /api/export/neo4j` and the two CLI scripts) resolve the organization from Postgres before calling `importModelToNeo4j`. * Both scripts are root `package.json` scripts (delegating to `@workspace/db-neo4j`, the same pattern as `seed:demo`/`setup:realm` delegating to `@workspace/db`). They read `DATABASE_URL`/`NEO4J_*` from the environment, falling back to `.env` at the repo root, then to `.env.$ENV` (`.env.dev` by default) — so `pnpm import:workspaces` works against the local dev stack with no extra flags. Pass an explicit env file as the last argument to target another environment (`pnpm import:workspace -- .env.prod`) — same convention as `migrate:prod`/`backfill:prod` in `packages/db`. A relative path resolves against the repo root (not the current directory), and `${VAR}` references inside the file expand against vars already loaded earlier in the same file. Configured via `NEO4J_URI` (defaults to `bolt://localhost:7687`), `NEO4J_USER`, `NEO4J_PASSWORD`. Set `NEO4J_ENABLED=false` to disable the integration entirely — the `migrate` command skips the Neo4j migration and `getDriver()` refuses to connect, so a deployment with no reachable Neo4j instance never attempts one. ## Dashboards [#dashboards] Configurable reporting dashboards — composed of panels (graph/table/metric), each embedding its own query (Cypher or SQL), parameters and visualization inline (no external panel/query catalogue) — built on top of ArchiSpark's own data and access model. Business logic lives in `apps/server/lib/dashboards/`: | Module | Role | | ---------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `contracts.ts` | Zod schemas for `DashboardDefinition`, `PanelContent`, `PanelResult`, `PanelVisualizationMetadata`, and the two native `DatasourceDefinition`s (`ARCHITECTURE_DATASOURCE`, `POSTGRES_DATASOURCE`). `ELEMENT_TYPES`/layers reuse `lib/archimate-helpers.ts` (`getLayer`, `ALL_ELEMENT_TYPES`) rather than a separate ArchiMate domain module. | | `repository.ts` | CRUD over `dashboards`/`dashboard_revisions` (`packages/db/src/schema.ts`), on Drizzle/Postgres like every other table. Every method takes `workspaceId`, which resolves to either a dashboard owned by that workspace (`workspaceId` set, created from the admin UI) or a system dashboard shared by every workspace (`workspaceId IS NULL`, see below) — `listLatestRevisions`/`listForAdministration` merge both scopes. Edits create a new immutable revision, deletes are soft (`deletedAt`). System dashboards can be neither edited nor deleted — `createRevision()`/`deleteDashboard()` throw a `ValidationError`, the same convention as `propertyDefinitions.isSystem`/`imagePacks.isSystem`. | | `panel-execution.ts` | Resolves a panel instance's parameters and runs its query, normalizing the result to the `graph`/`table`/`metrics` shape the frontend expects. | | `datasource-executors/neo4j.ts` | Executes a panel's Cypher against the same Neo4j graph as [the Neo4j export](#neo4j-export) (`@workspace/db-neo4j`'s driver singleton). **Multi-tenant scoping**: `organizationId` is injected into the query's bound parameters by the executor — never trusted from the query text — and `assertPanelQuerySafe` statically requires every panel's Cypher to reference `$organizationId`, checked both when a dashboard revision is saved and again before every execution (defense in depth). `Element` nodes also carry a `layer` property (added by the Neo4j export, see `packages/db-neo4j/src/layer.ts`) so panels can filter by ArchiMate layer without recomputing it from `type`. | | `datasource-executors/postgres.ts` | Executes a panel's read-only SQL against ArchiSpark's own application database (`@workspace/db`, `postgres-app-db`). Same `$organizationId` scoping contract as Neo4j, plus defense in depth the Neo4j graph doesn't need — the application database also holds every other organization's business data — the query runs inside a `READ ONLY` transaction with a short `statement_timeout`, and only a single `SELECT` statement is accepted (rejecting `;`, DDL/DML keywords). Named `$parameter` placeholders are bound through Drizzle's `sql` template, never string-interpolated. Panels are capped to 500 rows via an outer `LIMIT`, same as Cypher panels. For a `graph` panel, `nodeMetadata`/`inducedEdges` resolve the query's `nodeIds` into `elements`/`relationships` rows scoped to the caller's organization via their workspace — same role as `neo4j.ts`'s namesakes, queried separately from the panel's own transaction. | | `datasource-executors/index.ts` | Routes a panel's query to the Cypher or SQL executor based on `query.language`, and validates every panel's query (`assertDashboardQueriesSafe`) at dashboard-save time (`app/api/dashboards/**`). | | `explore.ts` | The ad hoc Cypher query page (`/explore`) runs arbitrary read-only queries, so its text can't be statically validated the way a saved panel's can. Scoping is instead enforced on the *result*: any row containing a Neo4j node or relationship whose `organizationId` doesn't match the caller's is dropped in its entirety, regardless of the query's own `WHERE` clause. | A fixed set of system dashboards (`dashboards.isSystem`) is shared by every workspace rather than duplicated into one row per workspace: `workspaceId` is `NULL` for them, the same "system row, `NULL` scope" convention as `imagePacks.organizationId` (`0023_image_packs.sql`). Because there is a single row, a system dashboard is visible to every workspace automatically — no per-workspace seeding step — but can be neither edited nor deleted through any workspace; `createRevision()` also rejects creating a workspace-owned dashboard whose id collides with a system dashboard's, rather than silently shadowing it. Migration `0032_dashboard_system_seed.sql` is the only place these rows are written — there is deliberately no runtime seed/reseed function or script (the `seed-demo.yml` GitHub Actions workflow's `truncateApplicationTables()` step wipes them along with every other table and does not restore them). If `packages/db/seeds/dashboards.sql` changes later (new dashboard, new revision), regenerate the JSON with `parseSourceRevisions()` (`packages/db/src/seed-dashboards-data.ts`) and hand-write a new numbered backfill migration, the same way `0032_dashboard_system_seed.sql` was produced. Six such backfills exist so far, migrating every system dashboard's panels from `architecture-neo4j` (Cypher) to `postgres-app-db` (SQL) — `architecture-neo4j` remains fully functional (`/explore`, `POST /api/export/neo4j`, workspace-owned dashboards can still target it), it's simply unused by any *system* dashboard today: | Migration | Dashboard | Notes | | ----------------------------------------------------------- | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------- | | `0034_demonstration_datasources_postgres.sql` | `demonstration-datasources` | Plain aggregates over `elements`/`workspaces` and the `fournisseurs` demo table. | | `0035_motivation_datasource_postgres.sql` | `motivation` | A recursive CTE over `elements`/`relationships` walking the original's relationship types and depth (1..5). | | `0036_principles_datasource_postgres.sql` | `principles` | Fixed 2-hop pattern (principle→requirement→constraint) — no recursion needed. | | `0037_rapports_application_datasource_postgres.sql` | `rapports-application` | Fixed 1..2-hop traversals plus joins through `element_properties`/`property_definitions` for the technology-layer panels. | | `0038_rapports_architecture_datasource_postgres.sql` | `rapports-architecture` | Plain counts over `elements`/`relationships`/`views`. | | `0039_voisinage_elements_datasource_postgres.sql` | `voisinage-elements` | Fixed 1-hop neighborhood plus two table panels over `element_properties` and `relationships`. | | `0040_vue_architecture_applicative_datasource_postgres.sql` | `vue-architecture-applicative` | Fixed 1-hop `Flow`/`Serving` patterns. | `0035`'s graph panel required teaching `datasource-executors/postgres.ts` to hydrate a `graph` panel's `nodes`/`edges` from a query's `nodeIds` (its `nodeMetadata`/`inducedEdges`, mirroring `neo4j.ts`'s) — Postgres graph panels had no such hydration before. Every migrated panel also had to replicate, as an inline `CASE` expression, the ArchiMate layer classification Neo4j nodes get at export time (`packages/db-neo4j/src/layer.ts`'s `getLayer`) — Postgres has no equivalent stored column, so this duplicated classification must be kept in sync with `getLayer` by hand if its rules ever change. Both dashboard lists (`app/dashboards` and `app/dashboards/admin`) show each dashboard's datasource type(s) in parentheses next to its title, derived from its panels' `query.datasourceId` — `(neo4j)`, `(postgres)`, or `(neo4j, postgres)` for a mixed dashboard (see `contracts.ts`'s `DATASOURCE_TYPES` and `datasource-badge.ts`); every system dashboard now shows `(postgres)`. Routes live under `app/api/dashboards/**`, `app/api/explore`, and `app/api/panel-visualizations`, gated by the same `resolveActiveContext`/`assertOrgAccess` gateway as every other resource — editing a dashboard requires the `owner`/`editor` role in the active organization, `viewer` is read-only. There is no separate admin login/token — the companion project's single-admin-token session (`ARCHIMATE_API_TOKEN`) isn't used here. Frontend components live under `apps/server/components/dashboards/`, restyled to ArchiSpark's shadcn/Tailwind tokens. Every ReactFlow uses the same rounded node treatment: a generic layer icon inside the node, a floating text badge for the ArchiMate component type, and labels clamped to two lines. The dashboard and relation graphs use fixed-size nodes with dagre auto-layout; `view-canvas-node.tsx` keeps the dimensions stored in the model and remains resizable so editing an imported ArchiMate view does not alter its geometry. Each graph retains its existing component color calculation. All three ReactFlow surfaces share the fullscreen overlay control implemented in `components/react-flow-fullscreen.tsx`; it locks page scrolling while active and supports Escape to exit. The React Flow canvas itself is instantiated only by `components/archispark-react-flow.tsx`, which owns the shared stylesheet, background, controls and attribution settings. Dashboard, relationship and view features supply only their domain-specific nodes, panels and handlers. ArchiMate relationship notation is defined once in `components/archimate-edge-style.ts`; shared marker definitions and the read-only edge renderer ensure that relationship types such as `Realization` use the same line pattern and endpoint symbols on every graph. Not carried over from the companion project: the XML-import worker (`apps/worker`) and its standalone demo Postgres instance (superseded by the live Neo4j export above for ArchiMate content — ArchiSpark's own native `postgres-app-db` datasource, see the table above, is unrelated: it queries ArchiSpark's own application database, not an imported dataset), the single-admin-token auth, the third-party panel plugin system (`plugins/`), and the "Reports" pages (legacy redirects to specific provisioned dashboards the companion project seeded from its own demo data — there's no equivalent seed here). Panel `transformations` (client-side result reshaping, e.g. `extractFields`) are accepted by the schema for forward-compatibility but not yet applied by the renderer.