[D-06]RLS FORTRESS

SECURITY

Row-Level Security, audit integrity, digital signatures, geofence enforcement — a zero-trust architecture where the database is the last line of defense.

// OPS_COMPANION FIELD MANUAL

ZERO-TRUST ARCHITECTURE

OPS Companion assumes breach at every layer. The application server, the API gateway, and the client are all considered potentially compromised. The database's Row-Level Security is the absolute last line of defense — and it cannot be bypassed.

The RLS Fortress (Jour 02)

168 Row-Level Security policies are embedded directly in the SQL engine. This is not application-layer security — it is database-engine security. Even if a malicious actor obtains a valid API token, they cannot access data outside their RLS-permitted scope.

Every query — SELECT, INSERT, UPDATE, DELETE — is evaluated against the authenticated user's digital fingerprint before any data is returned or modified. The fingerprint includes: user ID, organization ID, role claim, assignment context, and a millisecond-precision timestamp. If any element fails validation, the query returns zero rows and logs the attempt.

// RLS POLICY COUNT BY TABLE GROUP

Assets & Machines22 policies

Work Orders31 policies

Employees & Users28 policies

Inventory & Parts19 policies

Financial & Quotes24 policies

GPS & Telemetry18 policies

Communications14 policies

Audit & Integrity12 policies

How RLS Works in Practice

Each RLS policy is a predicate attached to a table and operation. For example:

-- A Technician can only see work orders assigned to them

CREATE POLICY work_orders_technician_select ON work_orders

FOR SELECT USING (

auth.role() = 'technician'

AND assigned_to = auth.uid()

AND organization_id = auth.org()

);

This predicate runs inside the database engine for every query. There is no way to bypass it from the application layer. A technician querying all work orders will only ever see their own — even if they construct the query manually.

Immutable Audit Trail

Every data modification in OPS Companion generates an immutable audit record. Audit records cannot be updated or deleted — not even by the Administrator. They are sealed with:

▸User identity (ID, role, organization)
▸Precise timestamp (millisecond resolution)
▸Action type (CREATE, UPDATE, DELETE, VIEW_SENSITIVE)
▸Before and after state (JSON diff)
▸Device telemetry (IP address, user agent, GPS if available)
▸Cryptographic seal linking to the previous audit record (chain integrity)

COMPLIANCE

The immutable audit trail meets the requirements for industrial compliance audits, including Linde Connect certification. The integrity registry (Jour 15) ensures that historical records cannot be falsified retroactively.

Digital Signatures

Three operational events require digital signatures, each sealed cryptographically:

Quote Acceptance

Client signs via secure link — signature sealed with PKCS#7, timestamp locked, linked to the quote record permanently

Inspection Completion

Technician signs completed inspection — signature includes GPS coordinates and device timestamp, immutable once submitted

Delivery Confirmation

Recipient signature on delivery — photo evidence + signature sealed together in the secure document vault

Geofence Enforcement (Jour 10)

Geofence zone compliance is enforced at the SQL engine level — not the application layer. The geospatial collision calculation runs inside the database, comparing incoming GPS coordinates against restricted zone polygons on every GPS update event.

When a breach is detected, the SQL engine immediately:

▸Locks the machine's operational status (cannot accept new work orders)
▸Revokes active task authorizations for the device
▸Triggers a real-time WebSocket alert to the Dispatcher
▸Creates an immutable breach record in the audit trail
▸Notifies the Administrator via push notification

Encryption & Data Protection

Encryption at RestAES-256

Encryption in TransitTLS 1.3

Password Hashingbcrypt (cost factor 12)

API Token TypeJWT (RS256)

Signature StandardPKCS#7

Sensitive Table EncryptionColumn-level AES-256 for PII

Multi-Tenant Isolation

Every table that contains organization-scoped data includes an organization_id column, and every RLS policy enforces organization_id = auth.org(). It is structurally impossible for one organization's data to be visible to another, even in the event of an application-layer bug.