Database Access Layer

Relevant source files

The following files were used as context for generating this wiki page:

• PLAN.md
• backend/src/db/prisma.ts
• backend/src/seed.ts
• backend/src/modules/users/users.routes.ts
• backend/src/modules/roles/roles.routes.ts
• backend/src/modules/tables/tables.routes.ts
• backend/src/middleware/requireSuperAdmin.ts

The Database Access Layer (DAL) in the MuteTaxes project provides a structured interface for interacting with the PostgreSQL relational database. It utilizes Prisma ORM to manage data persistence for core entities such as Users, Roles, and Tables, which form the foundation of the system's relationship-based authorization demo.

The DAL serves as the primary storage for business identity and resource metadata, while working in tandem with OpenFGA for authorization logic. It handles standard CRUD operations and manages many-to-many associations between users and their assigned roles.
Sources: PLAN.md:1-25, backend/src/db/prisma.ts:1-5

Core Architecture and Schema

The data layer is built on Prisma ORM and interacts with a PostgreSQL instance defined in the project infrastructure. The schema is designed to support a multi-tenant-capable structure where users are associated with roles to determine their access levels to specific "Table" resources.

Entity Relationship Model

The following ER diagram illustrates the primary data structures maintained within the database:

The database maintains the identity of users and the metadata of roles and tables, while the actual permissions between these entities are synchronized with OpenFGA.
Sources: PLAN.md:65-98, backend/src/modules/users/users.routes.ts:11-15

Schema Definition

The database entities are summarized in the table below:

Entity	Purpose	Key Fields
User	Represents application users.	id (UUID), username (Unique)
Role	Defines access groups (e.g., superadmin).	id (UUID), name (Unique), description
UserRole	Junction table for Many-to-Many relationship.	user_id, role_id (Composite PK)
Table	Registry of resources/modules in the system.	id (UUID), slug (Unique), display_name

Sources: PLAN.md:67-93, backend/src/modules/roles/roles.routes.ts:19-24

Implementation Details

Prisma Client Initialization

The application uses a singleton Prisma client instance located in backend/src/db/prisma.ts. This client is imported across various modules to perform database queries.

import { PrismaClient } from "@prisma/client";

export const prisma = new PrismaClient();

Sources: backend/src/db/prisma.ts:1-3

Data Seeding and Initialization

The system includes a seeding script used during Phase 0 of development to establish the initial "super_admin" user and the default set of tables (clients, companies, invoices, etc.).

async function seedDatabase() {
  const superAdmin = await prisma.user.upsert({
    where: { username: "super_admin" },
    update: {},
    create: { username: "super_admin" }
  });

  const superAdminRole = await prisma.role.upsert({
    where: { name: "superadmin" },
    update: {},
    create: { name: "superadmin", description: "Super Admin" }
  });

  await prisma.userRole.upsert({
    where: { userId_roleId: { userId: superAdmin.id, roleId: superAdminRole.id } },
    update: {},
    create: { userId: superAdmin.id, roleId: superAdminRole.id }
  });
}

Sources: backend/src/seed.ts:25-45

Data Access Patterns

User and Role Management

The DAL handles user creation and role assignments. Role assignments are persisted in the UserRole table to maintain the relational state inside the database, while simultaneously triggering tuple writes to OpenFGA.

• Role Assignment: When a role is added to a user, the userRoutes module performs an upsert on the UserRole model using a composite key of userId and roleId.
• Role Deletion: Deleting a role involves a cascaded cleanup, removing all UserRole mappings associated with that role ID before deleting the role itself.

Sources: backend/src/modules/users/users.routes.ts:38-51, backend/src/modules/roles/roles.routes.ts:31-54

Table Registry

The Table model serves as a registry for resources. When a new table is registered via POST /api/tables, it is stored in the database with a unique slug and a displayName. This database entry is used by the frontend to render navigation elements and by the backend to identify objects for permission checks.
Sources: backend/src/modules/tables/tables.routes.ts:15-24

Authorization Middleware Interaction

The DAL is frequently used by middleware to verify administrative status. The requireSuperAdmin middleware queries the database to check for the existence of a "superadmin" role and verifies the current user's membership in that role via the UserRole table.

Conclusion

The Database Access Layer provides the persistent backbone for the MuteTaxes backend. By utilizing Prisma and PostgreSQL, it ensures that user identities, role definitions, and resource metadata are stored reliably, enabling the complex relationship-based authorization logic managed by the FGA component.