Database Schema & Data Model

Relevant source files

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

• backend/prisma/schema.prisma
• backend/prisma/migrations/20260122120201_init/migration.sql
• PLAN.md
• backend/src/seed.ts
• backend/src/fga/fgaClient.ts

Introduction

The MuteTaxes_Backend database schema is designed to support a relationship-based authorization system. It utilizes PostgreSQL as the primary relational store for core entities—Users, Roles, and Tables—while integrating with OpenFGA for fine-grained access control logic. The relational model serves as the registry for users and resources, while the many-to-many relationship between users and roles facilitates the assignment of permissions.

Sources: PLAN.md:10-18, backend/prisma/schema.prisma:1-10

Entity Relationship Model

The core data model consists of four primary entities: User, Role, UserRole (a join table), and Table. The Table entity acts as a registry for resources (e.g., clients, invoices) that are subjected to authorization checks.

Schema Visualization

The following ER diagram illustrates the relationships between the database entities. Note that while permissions are enforced via OpenFGA, the database maintains the structural mappings between users and their assigned roles.

Data Dictionary

Model	Field	Type	Constraints	Description
User	id	TEXT	Primary Key, UUID	Unique identifier for the user.
	username	TEXT	Unique	User's unique login name.
	createdAt	TIMESTAMP	Default: Now	Record creation timestamp.
Role	id	TEXT	Primary Key, UUID	Unique identifier for the role.
	name	TEXT	Unique	Name of the role (e.g., 'superadmin').
	description	TEXT	Optional	Detailed description of the role's purpose.
UserRole	userId	TEXT	FK to User	Part of composite Primary Key.
	roleId	TEXT	FK to Role	Part of composite Primary Key.
Table	id	TEXT	Primary Key, UUID	Unique identifier for the resource table.
	slug	TEXT	Unique	URL-friendly identifier (e.g., 'invoices').
	displayName	TEXT	-	Human-readable name for the UI.

Sources: backend/prisma/schema.prisma:12-45, backend/prisma/migrations/20260122120201_init/migration.sql:1-55

Authorization Synchronization

While PostgreSQL stores the "who" (Users) and the "what" (Roles/Tables), the "how" (Permissions) is handled by OpenFGA. There is a strict synchronization logic between the database state and OpenFGA tuples.

Data Flow for Role Assignment

When a role is assigned to a user in the database, a corresponding tuple is written to OpenFGA to maintain consistency.

Seed Logic and Resource Registration

During system initialization, the seed.ts script populates both the relational database and the OpenFGA store. This ensures the super_admin user exists and is mapped to the superadmin role across both systems.

• Postgres Seed: Creates default tables (clients, companies, invoices, documents, tasks) and the initial super admin user.
• OpenFGA Seed: Writes tuples granting the superadmin role "creator" badges across all registered table resources.

Sources: backend/src/seed.ts:25-50, backend/src/seed.ts:80-95

SQL Implementation Details

The physical implementation of the schema is managed via Prisma migrations. All primary keys use the TEXT type to accommodate UUIDs generated at the application level.

CREATE TABLE "UserRole" (
    "userId" TEXT NOT NULL,
    "roleId" TEXT NOT NULL,
    CONSTRAINT "UserRole_pkey" PRIMARY KEY ("userId","roleId")
);

ALTER TABLE "UserRole" ADD CONSTRAINT "UserRole_userId_fkey" 
FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Sources: backend/prisma/migrations/20260122120201_init/migration.sql:24-29, backend/prisma/migrations/20260122120201_init/migration.sql:47-50

Summary

The Database Schema & Data Model provides a robust foundation for resource-based authorization. By decoupling resource registration (Postgres) from permission logic (OpenFGA), the system allows for dynamic role management and granular CRUD access control without complex relational joins or heavy database locks.