Implementing Row-Level Access Control

Part 3 of the Omnismith "Core Platform Capabilities" series Securing data inside shared multi-tenant environments requires robust authorization boundaries. Omnismith compiles dynamic row-level Access Scopes directly to database-level queries. For platforms managing flexible database schemas, access control must extend beyond simple endpoint or structural restrictions. Authorization must operate at the individual record level, evaluating data values dynamically at query execution time. Omnismith has expanded its role-based access control (RBAC) engine to support row-level "Access Scopes." This addition complements the existing module-based and resource-based security permissions by applying granular, value-driven constraints directly to database operations. Architecture of Omnismith Access Controls The security model of Omnismith operates across three distinct tiers. Every role defined in a project is evaluated against these tiers during API requests. Module-Based Access: The outer security layer. It controls access to entire functional zones within the platform, such as Attribute Management, Template Management, Entity Management, Billing, or Automation. Each module supports four basic operational levels: View, Create, Edit, and Delete. Resource-Based Access: The structural security layer. This level regulates permissions for specific templates or attributes. An administrator can assign Full Access, Edit, View Only, or Deny permissions to specific structures. This layer enables the hiding of sensitive fields containing personally identifiable information (PII) or entire entity groups from unauthorized roles. Access Scopes (Row-Level): The data-level security layer. Access Scopes target individual entities inside a template. These scopes apply dynamic, conditional rules to determine if a specific role can view or manipulate a specific entity. Roles in Omnismith remain strictly project-scoped. Consequently, the newly introduced Access Scopes are assigned, stored, and executed within the boundary of a single project role. Design of Row-Level Access Scopes An Access Scope is bound to a single template and contains one or more conditions. Each condition compares an attribute value on the target entity with a static or dynamic value defined in the role configuration. The evaluation logic scales from basic scalar matching to complex relational checks: String Conditions: The simplest form of row filtering. For example, a role designed for a billing team can be restricted on the Team Members template. The Access Scope evaluates the Team string attribute, permitting access only to records matching the value "Billing". List Conditions: These evaluate values against set options or statuses, ensuring a user only accesses records assigned to appropriate phases of a workflow. Reference Conditions: These resolve references to other entities in the system. In a Cold-Chain Fleet Management project utilizing a Shipments template, an Access Scope can restrict a delivery driver to specific rows. The configuration validates whether the Driver reference attribute on the shipment entity matches the unique identifier of the driver's own entity (e.g., "Bob Smith") in the Drivers template. // Schematic example. Please check OpenAPI Spec for actual schema definitions. { "template_uuid": "018f3a3c-5d7a-7c65-8f5a-3c5d7a658f5a", "conditions": [ { "attribute": "driver_reference", "operator": "equals", "value": "018f3a3c-5d7a-7c65-8f5a-3c5d7a658f5b" } ] } Enter fullscreen mode Exit fullscreen mode To support complex business rules, users can attach multiple Access Scopes to the same template within a single role. When multiple scopes are defined for the same structure, the query engine treats them as logical OR conditions. This allows a user to access rows matching predicate A or predicate B, avoiding restrictive narrow filters when broader visibility is required. Database-Level Compilation and CQRS Integration Evaluating row-level permissions in application memory introduces severe scaling bottlenecks. Fetching entire datasets to filter them inside the runtime consumes excessive memory and CPU cycles. Omnismith bypasses application-level filtering by compiling Access Scopes directly into the database layer. The platform relies on a CQRS (Command Query Responsibility Segregation) model for read operations. When a query is initiated through the API, the query handler inspects the active project session, retrieves the active role's Access Scopes, and compiles the conditions into native SQL. These compiled conditions are dynamically appended to the query. For simple attribute filters, the compilation produces basic comparison clauses: WHERE attribute_value = parameter Enter fullscreen mode Exit fullscreen mode For complex reference conditions, the compiler resolves the relations to ensure the reference identifiers match. By modifying the execution AST (Abstract Syntax Tree) before query execution, the database optimizer can use existing indexes on attributes and entity relationships. The security boundary is enforced natively by PostgreSQL at the storage layer, ensuring that unauthorized records are never loaded into the application context. Performance Tradeoffs and Database Profiling While compile-time query filtering preserves system memory, it shifts the computational load to the PostgreSQL query planner. Appending multiple dynamic clauses and complex relational subqueries can alter index selection paths. The platform structures tables on PostgreSQL and TimescaleDB with highly optimized indexing strategies designed to handle dynamic EAV (Entity-Attribute-Value) attributes. However, highly nested logical OR blocks combined with deep entity references may introduce planning overhead on projects with vast datasets. Omnismith monitors query performance and has designed these access paths to prevent degradation under standard workloads. To encourage comprehensive testing of this architecture, Omnismith is running an optimization challenge: the first tenant to encounter, document, and submit a verifiable database-side query execution degradation caused by Access Scope complexity will receive a discount on their monthly subscription. Security Posture The addition of Access Scopes provides a unified mechanism for coarse and fine-grained data isolation. Because the filtering mechanism is integrated into the core CQRS database execution pipeline, security boundaries remain uniform across the interactive web application, API endpoints, automated workflows, and AI Assistant actions.