service-finder/docs/p0_3d_capability_matrix_audit_report.md

P0 Audit: 3D Capability Matrix (RBAC) — SQLAlchemy Model Audit Report

Author: Service Finder Rendszer-Architect
Date: 2026-06-24
Gitea Issue: #283
Scope: Read-only model analysis for 3-Dimensional RBAC system (Global Roles × Tenant Roles × Subscription Capabilities)

---

Executive Summary

This report audits 4 critical dimensions against the existing SQLAlchemy model layer. The goal is to determine what exists, what is missing, and what database migration steps are needed to implement a full 3D Capability Matrix.

Files examined:

• backend/app/models/identity/identity.py — User/Person models
• backend/app/models/marketplace/organization.py — Organization, OrganizationMember, OrgRole models
• backend/app/models/core_logic.py — SubscriptionTier, OrganizationSubscription, UserSubscription models
• backend/app/models/fleet_finance/models.py — CostCategory model
• backend/scripts/seed_finance_dictionaries.py — CostCategory seed data

---

DIMENSION 1: Global / System Roles

Model: User — identity.users
Enum: UserRole

What EXISTS

Field	Type	Details
role	PG_ENUM(UserRole)	System-level role: SUPERADMIN, ADMIN, MODERATOR, SALES_REP, SERVICE_MGR, USER
is_superuser	NOT FOUND	There is no is_superuser boolean. Role-based only.
custom_permissions	JSON	Per-user permission overrides — {}::jsonb default
scope_level	String(30)	"individual" default — hierarchical scope context
scope_id	String(50)	The ID of the scope context
is_vip	Boolean	VIP flag
subscription_plan	String(30)	"FREE" default
subscription_expires_at	DateTime	Subscription expiry

Verdict

Dimension 1 is sufficiently covered. The UserRole enum provides 6 system-level roles. The custom_permissions JSON column allows per-user capability overrides. However, there is no standardized capability/feature matrix at the global level — everything is ad-hoc JSON.

---

DIMENSION 2: Tenant / Local Roles

Model: OrganizationMember — fleet.organization_members
Enum: OrgUserRole

What EXISTS

Field	Type	Details
role	PG_ENUM(OrgUserRole)	OWNER, ADMIN, ACCOUNTANT, DRIVER, VIEWER
permissions	JSON	Per-member capability flags — {}::jsonb
is_permanent	Boolean	Permanent member flag
is_verified	Boolean	Verified member flag
status	String(20)	"active" default

Bonus: Dynamic OrgRole RBAC System

Model: OrgRole — fleet.org_roles

Field	Type	Details
name_key	String(50)	Unique role identifier (e.g., "OWNER")
display_name	String(100)	Human-readable name
permissions	JSONB	Capability flags per role — e.g. {"can_add_expense": true, "can_approve_expense": false}
is_system	Boolean	System role (cannot be deleted)
priority	Integer	Role hierarchy priority

Verdict

Dimension 2 is well-covered. The OrgRole model with its permissions JSONB column is exactly what Phase 2 RBAC needs. Each OrganizationMember also has an individual permissions JSON column for user-level overrides above the role.

---

DIMENSION 3: Subscription Tier & Feature Capabilities

3A. SubscriptionTier Model

Model: SubscriptionTier — system.subscription_tiers

Field	Type	Details
name	String	e.g., "free", "premium", "vip"
rules	JSONB	Generic limits: {"max_vehicles": 5} — NO standardized feature capability format
is_custom	Boolean	Custom tier flag

3B. Organization Model (fleet schema)

Field	Type	Details
subscription_plan	String(30)	"FREE" default — denormalized quick-ref
subscription_expires_at	DateTime	Subscription expiry
subscription_tier_id	Integer (FK)	References system.subscription_tiers.id
base_asset_limit	Integer	Base vehicle limit (1 default)
purchased_extra_slots	Integer	Extra purchased vehicle slots
settings	JSONB	Operational settings only — NOT feature capabilities

3C. OrganizationSubscription Model

Model: OrganizationSubscription — finance.org_subscriptions

Field	Type	Details
extra_allowances	JSONB	Extra quotas: {"extra_vehicles": 1, "extra_garages": 1}
tier_id	Integer (FK)	References system.subscription_tiers.id

CRITICAL GAP: Missing custom_features / capabilities_override on Organization

There is NO dedicated JSONB column on the Organization model to store feature-level capability overrides like:

{
  "feature_fuel_logging": true,
  "feature_gps_tracking": false,
  "feature_analytics": true,
  "feature_multi_driver": true,
  "feature_service_booking": false,
  "feature_tco_reports": false
}

The existing settings JSONB column is for operational settings, not feature capability gates.

Similarly, the SubscriptionTier.rules JSONB has no standardized format for feature capability flags — it only stores numeric limits.

---

DIMENSION 4: Cost Category Feature Linkage

Model: CostCategory — fleet_finance.cost_categories

Field	Type	Details
min_tier	String(20)	"free" default — minimum subscription tier to access this category
visibility	String(20)	"both" default — visibility scope
is_system	Boolean	System category flag
required_feature	NOT FOUND	No column to tie category to a specific subscription feature
min_capability	NOT FOUND	No column for minimum capability requirement

Verdict

Partially covered. The min_tier field gates categories by subscription tier (e.g., "free", "premium"), but there is no way to gate by individual feature capability. For example, we cannot say "this category requires the feature_fuel_logging capability."

---

3D Capability Matrix — Current vs. Required

Dimension	What We Have	What We Need
D1: Global Roles	UserRole enum with 6 values + custom_permissions JSON	Sufficient. No DB changes needed.
D2: Tenant Roles	OrgUserRole enum + OrgRole.permissions JSONB + per-member permissions JSON	Sufficient. No DB changes needed.
D3a: Subscription Tiers	SubscriptionTier.rules JSONB (generic limits)	Need standardized feature capability format
D3b: Org Feature Overrides	Organization.settings JSONB (operational only)	NEED custom_features JSONB column
D4: Cost Category Gating	CostCategory.min_tier (tier-level only)	Need required_feature column + feature-based gating

---

Proposed Database Migration Steps

The following migrations must be executed in order to build the 3D Capability Matrix architecture:

Migration 1: Add feature_capabilities JSONB to SubscriptionTier

Target: SubscriptionTier — system.subscription_tiers

ALTER TABLE system.subscription_tiers 
ADD COLUMN feature_capabilities JSONB NOT NULL DEFAULT '{}'::jsonb;

Purpose: Define which features each tier enables. Example:

Tier	Feature Capabilities
free	{"feature_fuel_logging": true, "feature_basic_reports": true, "feature_service_reminders": false, "feature_gps_tracking": false}
premium	{"feature_fuel_logging": true, "feature_basic_reports": true, "feature_analytics": true, "feature_gps_tracking": true, "feature_multi_driver": true}
vip	All features enabled

Migration 2: Add custom_features JSONB to Organization

Target: Organization — fleet.organizations

ALTER TABLE fleet.organizations 
ADD COLUMN custom_features JSONB NOT NULL DEFAULT '{}'::jsonb;

Purpose: Allow per-organization feature overrides. These overrides merge on top of the tier defaults. A false value can disable a tier feature for a specific org.

Migration 3: Add required_feature to CostCategory

Target: CostCategory — fleet_finance.cost_categories

ALTER TABLE fleet_finance.cost_categories 
ADD COLUMN required_feature VARCHAR(50) DEFAULT NULL;

Purpose: Tie a cost category to a specific feature capability.

Category	required_feature
OPERATION_FUEL	NULL (free for all)
SERVICE_SCHEDULED	NULL (free for all)
FINANCING_LEASE	"feature_financing"
INSURANCE_CASCO	"feature_insurance"
ANALYTICS_TCO	"feature_analytics"

Migration 4 (Optional): Create FeatureCapability Enum

CREATE TYPE system.feature_capability AS ENUM (
    'feature_fuel_logging',
    'feature_basic_reports',
    'feature_analytics',
    'feature_gps_tracking',
    'feature_multi_driver',
    'feature_service_booking',
    'feature_tco_reports',
    'feature_financing',
    'feature_insurance',
    'feature_ad_free'
);

Purpose: Standardize feature names across the system.

---

Capability Resolution Algorithm (Proposed)

The final capability check for any feature at runtime follows this resolution chain:

1. Organization.custom_features["feature_xxx"]  <- explicit override (highest priority)
2. SubscriptionTier.feature_capabilities["feature_xxx"]  <- tier default
3. false  <- implicit default (feature not available)

For CostCategory visibility:

If category.min_tier > org.subscription_plan -> HIDE
If category.required_feature IS NOT NULL AND NOT resolved_feature_capability -> HIDE
Otherwise -> SHOW

---

Summary

Component	Status	Action Required
D1: Global Roles	Complete	None
D2: Tenant Roles	Complete	None
D3: Subscription Tier Features	Partial	Add feature_capabilities JSONB to SubscriptionTier
D3: Org Feature Overrides	Missing	Add custom_features JSONB to Organization
D4: Cost Category Feature Gate	Partial	Add required_feature column to CostCategory
Standardization	Missing	(Optional) Create FeatureCapability Enum

Total proposed DB changes: 3 ALTER TABLE statements + 1 optional ENUM type.