Permissions System Architecture

Answer to the Question: Permissions and Subscriptions in the Same Database

Analysis

For a medium-sized SaaS application, keeping permissions and subscriptions in the same database is generally a good solution for the following reasons:

✅ Advantages

1. Simplicity: Single database to manage, backup, and monitor
2. ACID Transactions: Operations can be atomic (e.g., assign a plan and check permissions in a single transaction)
3. Join Queries: Easy to join user data with their permissions and subscriptions
4. Consistency: No synchronization issues between databases
5. Performance: No network latency between databases
6. Maintenance: Simpler to maintain and debug

⚠️ Potential Disadvantages

1. Coupling: Billing data is coupled with application data
2. Scalability: If billing becomes very large, might require separation
3. Security: All data in a single database (but can be mitigated with PostgreSQL schemas)

Recommendation

Keep in the same database for now because:

• The application is still growing
• The complexity added by separation is not justified
• We can always migrate to separation later if necessary

When to Separate?

Consider separation if:

• Billing data volume becomes very large (> millions of transactions)
• Need to isolate billing security (PCI-DSS compliance, etc.)
• Different teams manage billing vs application
• Need independent scalability

Future Alternatives

If separation becomes necessary later:

1. Microservices: Separate billing service with its own database
2. Dedicated Database: Separate PostgreSQL database for billing only
3. Event-driven: Synchronization via events (more complex)

Users Table Cleanup

Changes Made

1. Removed role field: Replaced by ADMIN_ACCESS permission
2. Automatic migration: Existing ADMIN users are automatically migrated to ENTERPRISE plan with ADMIN_ACCESS permission
3. Updated checks: All role !== "ADMIN" checks are replaced by permission checks

Migration

The migration 0032_remove_user_role:

• Assigns ADMIN_ACCESS permission to all plans
• Creates ENTERPRISE subscriptions for existing ADMIN users
• Removes the role field from the users table

Usage

Before:

if (req.user.role !== "ADMIN") {
  throw new ForbiddenException("Admin access required");
}

After:

@UseGuards(AuthGuard('jwt'), ActiveUserGuard, PermissionGuard)
@RequirePermission('ADMIN_ACCESS')
async adminEndpoint() {
  // ...
}

Or in a service:

await verifyAdmin(this.permissionsService, userId, this.logger, 'actionName');

Current Structure

Main Tables

• users: Basic information (without role)
• user_subscriptions: Active user subscriptions
• subscription_plans: Available plans (FREE, BASIC, PRO, ENTERPRISE)
• permissions: Available granular permissions
• plan_permissions: Plan-permission associations
• plan_limits: Numeric limits per plan

Verification Flow

1. User makes a request
2. AuthGuard('jwt') validates the token
3. ActiveUserGuard checks ACTIVE status
4. PermissionGuard checks required permission (if decorator present)
5. PermissionsService loads permissions from database
6. Verification performed and response returned

Performance

• Permission checks: 1 SQL query per check (can be optimized with cache)
• Limit checks: 1 SQL query + 1 count query
• Scalability: Indexes on user_subscriptions and plan_permissions ensure good performance

Scalability

The system is designed to be scalable:

• Easy addition of new permissions
• Easy addition of new plans
• Limit modifications without complex migration
• Possibility to add Redis cache later if necessary