Regulated Industries: Data Compliance by Design

Every data access is an explicit SQL query. No hidden side effects. Full transparency for audits.

Compliance Challenges

Regulated industries require:

Audit trails - Every data access must be logged and explainable
Deterministic behavior - No hidden queries or magical field loading
Data lineage - Where did this data come from? How was it calculated?
Encryption in motion - Data queries over secure channels
Access control - Role-based access enforced at database level
Immutable records - Audit logs that cannot be modified

The problem with traditional GraphQL: Field resolvers execute silently. You don't know what queries are running. Batching and caching obscure the actual data access patterns.

FraiseQL for Compliance

1. SQL Views = Explicit Data Access

Every data query is a readable SQL statement. Auditors can review the exact query.

-- Healthcare: HIPAA-compliant patient view
CREATE VIEW v_patient_record AS
SELECT
  jsonb_build_object(
    'patient_id', p.id::text,
    'ssn', CASE
      WHEN current_setting('app.user_role') = 'admin' THEN p.ssn
      ELSE '***-**-' || right(p.ssn, 4)
    END,
    'medical_history', (
      SELECT jsonb_agg(jsonb_build_object(
        'date', m.visit_date,
        'diagnosis', m.diagnosis,
        'provider', m.provider_id::text
      ))
      FROM tb_medical_record m
      WHERE m.patient_id = p.id
    )
  ) AS data
FROM tb_patient p
WHERE p.practice_id = current_setting('app.practice_id')::uuid;

2. Automatic Audit Logging

Use PostgreSQL triggers to log every data access:

-- Create immutable audit log
CREATE TABLE tb_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
  user_id UUID NOT NULL,
  action TEXT NOT NULL,
  query TEXT NOT NULL,
  affected_records JSONB,
  ip_address INET NOT NULL
);

-- Trigger on view access
CREATE TRIGGER audit_patient_access
  AFTER SELECT ON v_patient_record
  FOR EACH ROW
  EXECUTE FUNCTION log_patient_access(NEW.patient_id);

-- Function to log
CREATE OR REPLACE FUNCTION log_patient_access(patient_id UUID)
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO tb_audit_log (user_id, action, query, affected_records, ip_address)
  VALUES (
    current_setting('app.user_id')::UUID,
    'view_patient',
    current_query(),
    jsonb_build_object('patient_id', patient_id),
    inet_client_addr()
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

3. Role-Based Access at Database Level

-- Finance: Account access by role
CREATE VIEW v_account_transactions AS
SELECT
  jsonb_build_object(
    'account_id', a.id::text,
    'balance', CASE
      WHEN current_setting('app.user_role') IN ('admin', 'account_owner') THEN a.balance
      ELSE NULL
    END,
    'transactions', CASE
      WHEN current_setting('app.user_role') IN ('admin', 'account_owner') THEN (
        SELECT jsonb_agg(jsonb_build_object(
          'id', t.id::text,
          'date', t.created_at,
          'amount', t.amount
        ))
        FROM tb_transaction t
        WHERE t.account_id = a.id
      )
      ELSE '[]'::jsonb
    END
  ) AS data
FROM tb_account a
WHERE a.institution_id = current_setting('app.institution_id')::uuid;

Real-World: Healthcare System

HIPAA Compliance Scenario

A nurse requests a patient's medical history. The system must:

Verify the nurse has access to that patient's records
Log the access with timestamp, user ID, and IP
Return only the data the nurse is authorized to see
Create an immutable audit record

FraiseQL Approach:

-- Single view enforces all rules
CREATE VIEW v_nurse_accessible_patients AS
SELECT
  p.id::text AS patient_id,
  jsonb_build_object(
    'patient_id', p.id::text,
    'name', p.name,
    'date_of_birth', p.dob,
    'allergies', (
      SELECT jsonb_agg(jsonb_build_object(
        'allergen', a.allergen,
        'severity', a.severity
      ))
      FROM tb_allergy a
      WHERE a.patient_id = p.id
    ),
    'current_medications', (
      SELECT jsonb_agg(jsonb_build_object(
        'name', m.name,
        'dosage', m.dosage,
        'prescribed_by', m.provider_id::text
      ))
      FROM tb_medication m
      WHERE m.patient_id = p.id
        AND m.stop_date IS NULL
    )
  ) AS data
FROM tb_patient p
WHERE p.clinic_id = current_setting('app.clinic_id')::uuid
  AND EXISTS (
    -- Nurse only sees patients assigned to them
    SELECT 1 FROM tb_patient_assignment
    WHERE patient_id = p.id
      AND provider_id = current_setting('app.user_id')::uuid
  );

Automatic audit trail: Every SELECT on this view triggers a log entry.

Best Practices for Regulated Industries

1. Immutable Audit Logs

Use PostgreSQL's built-in features:

-- Make audit table append-only
ALTER TABLE tb_audit_log DISABLE TRIGGER ALL;
-- User cannot modify audit logs
REVOKE UPDATE, DELETE ON tb_audit_log FROM app_user;
GRANT SELECT ON tb_audit_log TO app_user;

2. Encryption at Rest

PostgreSQL Transparent Data Encryption (TDE) or column encryption:

-- Encrypt sensitive columns
CREATE TABLE tb_patient (
  id UUID PRIMARY KEY,
  ssn TEXT ENCRYPTED WITH (algorithm = 'aes-256'),
  email TEXT ENCRYPTED WITH (algorithm = 'aes-256')
);

-- Only admins can decrypt
ALTER TABLE tb_patient ENABLE ROW LEVEL SECURITY;
CREATE POLICY admin_only ON tb_patient
  USING (current_setting('app.user_role') = 'admin');

3. Data Retention Policies

Enforce retention automatically:

-- Archive old records
CREATE FUNCTION archive_old_records()
RETURNS void AS $$
BEGIN
  INSERT INTO tb_medical_history_archive
  SELECT * FROM tb_medical_record
  WHERE created_at < NOW() - INTERVAL '7 years';

  DELETE FROM tb_medical_record
  WHERE created_at < NOW() - INTERVAL '7 years';
END;
$$ LANGUAGE plpgsql;

-- Run daily
SELECT cron.schedule('archive-medical-records', '0 2 * * *', 'SELECT archive_old_records()');

Compliance Mapping

HIPAA

Audit logs - Every patient access logged

Access control - View-level RBAC

Data integrity - Triggers prevent direct table access

GDPR

Right to explanation - SQL queries are readable

Data minimization - Views return only authorized fields

Audit trail - Complete access history

SOC 2

Encryption in transit - Use TLS for connections

Access controls - Database-level RBAC

Monitoring - Audit log queries for anomalies

PCI DSS

Tokenization - Never store raw card data

Encryption - Sensitive columns encrypted

Audit trails - All access logged

Related Resources

🔐 For Compliance Teams

Understanding FraiseQL from an audit perspective.

Read →

🔑 Authorization Sprawl

Consolidate authorization at the database level.

Read →

💻 SQL Ownership

Own your queries, own your compliance story.

Read →

Build Compliant Systems

FraiseQL makes regulatory compliance part of your architecture, not an afterthought.

Get Started ← Back to Use Cases