trogers1884/c77_secure_db
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
c77_secure_db/EXAMPLES.md

36 KiB
Raw Permalink Blame History

c77_secure_db Examples

This document provides practical examples for using the c77_secure_db extension in real-world scenarios.

📝 About These Examples

We provide comprehensive examples for:

  • Pure SQL - Direct PostgreSQL usage
  • Laravel - Full PHP integration with tested patterns

While c77_secure_db is designed to work with any application framework that connects to PostgreSQL, we focus our testing and examples on SQL and Laravel. The extension's database-level security works with Node.js, Django, Ruby on Rails, and other frameworks, but we haven't extensively tested these integrations and prefer not to provide guidance where our expertise is limited.

Community contributions for other frameworks are welcome!

🗄️ Pure SQL Examples

Basic Setup

-- Install and verify extension
CREATE EXTENSION c77_secure_db;
SELECT c77_secure_db_run_all_tests();

-- Create application schema
CREATE SCHEMA ecommerce;
SELECT c77_secure_db_manage_secure_schemas('add', 'ecommerce');

-- Create secure tables
CREATE TABLE ecommerce.customers (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT,
    status TEXT DEFAULT 'active',
    
    -- Required security columns
    content_hash TEXT,
    hash_version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE TABLE ecommerce.orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES ecommerce.customers(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status TEXT DEFAULT 'pending',
    order_date TIMESTAMPTZ DEFAULT NOW(),
    
    -- Required security columns
    content_hash TEXT,
    hash_version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

Customer Management Examples

-- Create new customer
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'customers',
    'operation', 'insert',
    'data', jsonb_build_object(
        'email', 'john.doe@example.com',
        'first_name', 'John',
        'last_name', 'Doe',
        'phone', '+1-555-0123'
    )
));

-- Response:
{
    "success": true,
    "operation": "insert",
    "schema_name": "ecommerce",
    "table_name": "customers",
    "rows_affected": 1,
    "content_hash": "a1b2c3d4e5f6789...",
    "execution_time_ms": 8,
    "operation_id": "550e8400-e29b-41d4-a716-446655440000",
    "timestamp": "2025-01-26T15:30:00Z"
}

-- Update customer information
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'customers',
    'operation', 'update',
    'data', jsonb_build_object(
        'id', 1,
        'phone', '+1-555-9999',
        'status', 'premium'
    )
));

-- Soft delete customer (preserves data)
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'customers',
    'operation', 'soft_delete',
    'data', jsonb_build_object('id', 1)
));

-- Upsert customer (insert if new, update if exists)
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'customers',
    'operation', 'upsert',
    'data', jsonb_build_object(
        'id', 2,
        'email', 'jane.smith@example.com',
        'first_name', 'Jane',
        'last_name', 'Smith',
        'phone', '+1-555-0456'
    )
));

Order Processing Examples

-- Create new order
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'orders',
    'operation', 'insert',
    'data', jsonb_build_object(
        'customer_id', 1,
        'total_amount', 299.99,
        'status', 'confirmed'
    )
));

-- Update order status
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'orders',
    'operation', 'update',
    'data', jsonb_build_object(
        'id', 1,
        'status', 'shipped'
    )
));

-- Cancel order (soft delete)
SELECT c77_secure_db_operation(jsonb_build_object(
    'schema_name', 'ecommerce',
    'table_name', 'orders',
    'operation', 'soft_delete',
    'data', jsonb_build_object('id', 1)
));

Data Integrity Verification

-- Check if a customer record has been tampered with
SELECT c77_secure_db_check_freshness(
    'ecommerce',
    'customers',
    jsonb_build_object(
        'id', 1,
        'email', 'john.doe@example.com',
        'first_name', 'John',
        'last_name', 'Doe',
        'phone', '+1-555-0123',
        'status', 'active'
    )
);

-- Response indicates tampering status:
{
    "success": true,
    "id": "1",
    "fresh": true,
    "stored_hash": "a1b2c3d4e5f6789...",
    "calculated_hash": "a1b2c3d4e5f6789...",
    "hash_version": 1,
    "timestamp": "2025-01-26T15:30:00Z"
}

-- Verify all customer records at once
SELECT c77_secure_db_verify_content_hashes('ecommerce', 'customers');

-- Fix any hash mismatches found
SELECT c77_secure_db_verify_content_hashes('ecommerce', 'customers', true);

-- Bulk freshness check for multiple records
SELECT c77_secure_db_check_freshness_bulk(
    'ecommerce',
    'customers',
    '[
        {"id": 1, "email": "john.doe@example.com", "first_name": "John", "last_name": "Doe"},
        {"id": 2, "email": "jane.smith@example.com", "first_name": "Jane", "last_name": "Smith"}
    ]'::jsonb
);

RBAC Integration Examples

-- Setup RBAC permissions (requires c77_rbac extension)
SELECT c77_rbac_grant_feature('customer_service', 'secure_db_read');
SELECT c77_rbac_grant_feature('customer_service', 'secure_db_update');
SELECT c77_rbac_grant_feature('order_manager', 'secure_db_insert');
SELECT c77_rbac_grant_feature('order_manager', 'secure_db_update');
SELECT c77_rbac_grant_feature('supervisor', 'secure_db_delete');

-- Assign users to roles
SELECT c77_rbac_assign_subject('emp_001', 'customer_service', 'department', 'support');
SELECT c77_rbac_assign_subject('emp_002', 'order_manager', 'department', 'sales');

-- Set user context and perform RBAC-protected operation
SET "c77_rbac.external_id" TO 'emp_001';

SELECT c77_secure_db_operation(
    jsonb_build_object(
        'schema_name', 'ecommerce',
        'table_name', 'customers',
        'operation', 'update',
        'data', jsonb_build_object(
            'id', 1,
            'status', 'vip'
        )
    ),
    true,                    -- check_rbac = true
    'secure_db_update',      -- required_feature
    'department',            -- scope_type
    'support'                -- scope_id
);

-- Response includes RBAC information:
{
    "success": true,
    "operation": "update",
    "rbac_check_performed": true,
    "rbac_user_id": "emp_001",
    "required_feature": "secure_db_update",
    ...
}

Advanced Configuration

-- Exclude frequently updated columns from hash calculation
COMMENT ON COLUMN ecommerce.customers.content_hash IS 
'{"exclude_hash_columns": ["last_login", "login_count", "last_activity"]}';

-- Generate operation templates for easier development
SELECT c77_secure_db_get_operation_template('ecommerce', 'customers', 'insert');
SELECT c77_secure_db_get_operation_template('ecommerce', 'orders', 'update');

-- System monitoring
SELECT c77_secure_db_health_check();

-- Performance monitoring
SELECT 
    operation_type,
    count(*) as operation_count,
    avg(execution_time_ms) as avg_execution_time,
    max(execution_time_ms) as max_execution_time
FROM c77_secure_db_operation_audit 
WHERE created_at > now() - interval '1 hour'
GROUP BY operation_type
ORDER BY avg_execution_time DESC;

Maintenance Operations

-- Daily maintenance
SELECT c77_secure_db_cleanup_expired_tokens();

-- Weekly integrity verification
SELECT c77_secure_db_verify_content_hashes('ecommerce', 'customers');
SELECT c77_secure_db_verify_content_hashes('ecommerce', 'orders');

-- Monthly security audit
SELECT 
    user_name,
    operation_type,
    count(*) as operation_count,
    min(created_at) as first_operation,
    max(created_at) as last_operation
FROM c77_secure_db_operation_audit 
WHERE created_at > now() - interval '30 days'
GROUP BY user_name, operation_type
ORDER BY operation_count DESC;

🏗️ Laravel Integration Examples

Service Provider Setup

<?php
// app/Providers/SecureDbServiceProvider.php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use App\Services\SecureDbService;

class SecureDbServiceProvider extends ServiceProvider
{
    public function register()
    {
        $this->app->singleton(SecureDbService::class, function ($app) {
            return new SecureDbService();
        });
    }

    public function boot()
    {
        // Register the service provider in config/app.php providers array
    }
}

Core Service Class

<?php
// app/Services/SecureDbService.php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Exception;

class SecureDbService
{
    protected $schema;

    public function __construct()
    {
        $this->schema = config('database.secure_schema', 'ecommerce');
    }

    /**
     * Execute a secure database operation
     */
    public function operation(array $data, bool $checkRbac = false, string $requiredFeature = null): array
    {
        try {
            $result = DB::selectOne(
                'SELECT c77_secure_db_operation(?, ?, ?) as result',
                [
                    json_encode($data),
                    $checkRbac,
                    $requiredFeature
                ]
            );

            $response = json_decode($result->result, true);

            if (!$response['success']) {
                Log::warning('Secure DB operation failed', [
                    'operation' => $data,
                    'error' => $response['error'] ?? 'Unknown error',
                    'operation_id' => $response['operation_id'] ?? null
                ]);

                throw new Exception($response['error'] ?? 'Secure operation failed');
            }

            Log::info('Secure DB operation completed', [
                'operation_type' => $data['operation'],
                'table' => $data['table_name'],
                'operation_id' => $response['operation_id'],
                'execution_time_ms' => $response['execution_time_ms']
            ]);

            return $response;

        } catch (Exception $e) {
            Log::error('Secure DB operation exception', [
                'operation' => $data,
                'error' => $e->getMessage()
            ]);
            throw $e;
        }
    }

    /**
     * Insert record securely
     */
    public function insert(string $table, array $data, bool $checkRbac = true): array
    {
        return $this->operation([
            'schema_name' => $this->schema,
            'table_name' => $table,
            'operation' => 'insert',
            'data' => $data
        ], $checkRbac, 'secure_db_insert');
    }

    /**
     * Update record securely
     */
    public function update(string $table, array $data, bool $checkRbac = true): array
    {
        if (!isset($data['id'])) {
            throw new Exception('Primary key "id" is required for update operations');
        }

        return $this->operation([
            'schema_name' => $this->schema,
            'table_name' => $table,
            'operation' => 'update',
            'data' => $data
        ], $checkRbac, 'secure_db_update');
    }

    /**
     * Upsert record securely
     */
    public function upsert(string $table, array $data, bool $checkRbac = true): array
    {
        return $this->operation([
            'schema_name' => $this->schema,
            'table_name' => $table,
            'operation' => 'upsert',
            'data' => $data
        ], $checkRbac, 'secure_db_insert');
    }

    /**
     * Soft delete record
     */
    public function softDelete(string $table, int $id, bool $checkRbac = true): array
    {
        return $this->operation([
            'schema_name' => $this->schema,
            'table_name' => $table,
            'operation' => 'soft_delete',
            'data' => ['id' => $id]
        ], $checkRbac, 'secure_db_delete');
    }

    /**
     * Hard delete record (permanent)
     */
    public function delete(string $table, int $id, bool $checkRbac = true): array
    {
        return $this->operation([
            'schema_name' => $this->schema,
            'table_name' => $table,
            'operation' => 'delete',
            'data' => ['id' => $id]
        ], $checkRbac, 'secure_db_delete');
    }

    /**
     * Check if record data is fresh (not tampered)
     */
    public function checkFreshness(string $table, array $data): array
    {
        $result = DB::selectOne(
            'SELECT c77_secure_db_check_freshness(?, ?, ?) as result',
            [$this->schema, $table, json_encode($data)]
        );

        return json_decode($result->result, true);
    }

    /**
     * Verify content hashes for entire table
     */
    public function verifyHashes(string $table, bool $fixMismatches = false): array
    {
        $result = DB::selectOne(
            'SELECT c77_secure_db_verify_content_hashes(?, ?, ?) as result',
            [$this->schema, $table, $fixMismatches]
        );

        return json_decode($result->result, true);
    }

    /**
     * Get system health status
     */
    public function healthCheck(): array
    {
        $result = DB::selectOne('SELECT c77_secure_db_health_check() as result');
        return json_decode($result->result, true);
    }
}

Middleware for RBAC Context

<?php
// app/Http/Middleware/SecureDbMiddleware.php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Log;

class SecureDbMiddleware
{
    /**
     * Handle an incoming request and set RBAC user context
     */
    public function handle(Request $request, Closure $next)
    {
        if (Auth::check()) {
            try {
                // Set RBAC user context for secure database operations
                DB::statement('SET "c77_rbac.external_id" TO ?', [Auth::id()]);
                
                Log::debug('RBAC context set', ['user_id' => Auth::id()]);
            } catch (Exception $e) {
                Log::warning('Failed to set RBAC context', [
                    'user_id' => Auth::id(),
                    'error' => $e->getMessage()
                ]);
            }
        }

        return $next($request);
    }
}

Customer Model Example

<?php
// app/Models/Customer.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Services\SecureDbService;
use Exception;

class Customer extends Model
{
    protected $table = 'ecommerce.customers';
    
    protected $fillable = [
        'email', 'first_name', 'last_name', 'phone', 'status'
    ];

    protected $hidden = [
        'content_hash', 'hash_version'
    ];

    protected $casts = [
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    protected $secureDb;

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
        $this->secureDb = app(SecureDbService::class);
    }

    /**
     * Create new customer using secure operations
     */
    public static function createSecure(array $data): self
    {
        $secureDb = app(SecureDbService::class);
        
        $result = $secureDb->insert('customers', $data);
        
        // Assuming the operation returns the created record data
        $customer = new static();
        $customer->exists = true;
        $customer->fill($data);
        
        return $customer;
    }

    /**
     * Update customer using secure operations
     */
    public function updateSecure(array $data): bool
    {
        if (!$this->exists) {
            throw new Exception('Cannot update non-existent customer');
        }

        $data['id'] = $this->getKey();
        
        $result = $this->secureDb->update('customers', $data);
        
        // Update model attributes
        $this->fill($data);
        
        return $result['success'];
    }

    /**
     * Soft delete customer using secure operations
     */
    public function deleteSecure(): bool
    {
        if (!$this->exists) {
            return false;
        }

        $result = $this->secureDb->softDelete('customers', $this->getKey());
        
        return $result['success'];
    }

    /**
     * Check if customer data is fresh (not tampered)
     */
    public function isFresh(): bool
    {
        if (!$this->exists) {
            return false;
        }

        $result = $this->secureDb->checkFreshness('customers', $this->toArray());
        
        return $result['success'] && $result['fresh'];
    }

    /**
     * Get customer orders
     */
    public function orders()
    {
        return $this->hasMany(Order::class, 'customer_id');
    }
}

Order Model Example

<?php
// app/Models/Order.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Services\SecureDbService;

class Order extends Model
{
    protected $table = 'ecommerce.orders';
    
    protected $fillable = [
        'customer_id', 'total_amount', 'status', 'order_date'
    ];

    protected $hidden = [
        'content_hash', 'hash_version'
    ];

    protected $casts = [
        'total_amount' => 'decimal:2',
        'order_date' => 'datetime',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    /**
     * Create new order using secure operations
     */
    public static function createSecure(array $data): self
    {
        $secureDb = app(SecureDbService::class);
        
        $result = $secureDb->insert('orders', $data);
        
        $order = new static();
        $order->exists = true;
        $order->fill($data);
        
        return $order;
    }

    /**
     * Update order status securely
     */
    public function updateStatusSecure(string $status): bool
    {
        $result = app(SecureDbService::class)->update('orders', [
            'id' => $this->getKey(),
            'status' => $status
        ]);
        
        if ($result['success']) {
            $this->status = $status;
            return true;
        }
        
        return false;
    }

    /**
     * Cancel order (soft delete)
     */
    public function cancelSecure(): bool
    {
        $result = app(SecureDbService::class)->softDelete('orders', $this->getKey());
        
        return $result['success'];
    }

    /**
     * Get related customer
     */
    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
}

Controller Examples

<?php
// app/Http/Controllers/CustomerController.php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\Customer;
use App\Services\SecureDbService;
use Illuminate\Http\Request;
use Exception;

class CustomerController extends Controller
{
    protected $secureDb;

    public function __construct(SecureDbService $secureDb)
    {
        $this->secureDb = $secureDb;
    }

    /**
     * Create new customer
     */
    public function store(Request $request)
    {
        $validated = $request->validate([
            'email' => 'required|email|unique:ecommerce.customers,email',
            'first_name' => 'required|string|max:255',
            'last_name' => 'required|string|max:255',
            'phone' => 'nullable|string|max:20',
            'status' => 'sometimes|in:active,inactive,premium'
        ]);

        try {
            $customer = Customer::createSecure($validated);
            
            return response()->json([
                'success' => true,
                'message' => 'Customer created successfully',
                'customer' => $customer->toArray()
            ], 201);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Update customer
     */
    public function update(Request $request, int $id)
    {
        $validated = $request->validate([
            'email' => 'sometimes|email|unique:ecommerce.customers,email,' . $id,
            'first_name' => 'sometimes|string|max:255',
            'last_name' => 'sometimes|string|max:255',
            'phone' => 'nullable|string|max:20',
            'status' => 'sometimes|in:active,inactive,premium'
        ]);

        try {
            $customer = Customer::find($id);
            
            if (!$customer) {
                return response()->json(['error' => 'Customer not found'], 404);
            }

            $customer->updateSecure($validated);
            
            return response()->json([
                'success' => true,
                'message' => 'Customer updated successfully',
                'customer' => $customer->fresh()->toArray()
            ]);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Delete customer (soft delete)
     */
    public function destroy(int $id)
    {
        try {
            $customer = Customer::find($id);
            
            if (!$customer) {
                return response()->json(['error' => 'Customer not found'], 404);
            }

            $customer->deleteSecure();
            
            return response()->json([
                'success' => true,
                'message' => 'Customer deleted successfully'
            ]);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Verify customer data integrity
     */
    public function verifyIntegrity(int $id)
    {
        try {
            $customer = Customer::find($id);
            
            if (!$customer) {
                return response()->json(['error' => 'Customer not found'], 404);
            }

            $isFresh = $customer->isFresh();
            
            return response()->json([
                'customer_id' => $id,
                'is_fresh' => $isFresh,
                'message' => $isFresh ? 'Data integrity verified' : 'Data tampering detected!',
                'verified_at' => now()
            ]);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 500);
        }
    }
}

Order Controller

<?php
// app/Http/Controllers/OrderController.php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\Order;
use App\Models\Customer;
use Illuminate\Http\Request;
use Exception;

class OrderController extends Controller
{
    /**
     * Create new order
     */
    public function store(Request $request)
    {
        $validated = $request->validate([
            'customer_id' => 'required|exists:ecommerce.customers,id',
            'total_amount' => 'required|numeric|min:0.01',
            'status' => 'sometimes|in:pending,confirmed,shipped,delivered,cancelled'
        ]);

        try {
            $order = Order::createSecure($validated);
            
            return response()->json([
                'success' => true,
                'message' => 'Order created successfully',
                'order' => $order->toArray()
            ], 201);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Update order status
     */
    public function updateStatus(Request $request, int $id)
    {
        $validated = $request->validate([
            'status' => 'required|in:pending,confirmed,shipped,delivered,cancelled'
        ]);

        try {
            $order = Order::find($id);
            
            if (!$order) {
                return response()->json(['error' => 'Order not found'], 404);
            }

            $order->updateStatusSecure($validated['status']);
            
            return response()->json([
                'success' => true,
                'message' => 'Order status updated successfully',
                'order' => $order->fresh()->toArray()
            ]);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Cancel order
     */
    public function cancel(int $id)
    {
        try {
            $order = Order::find($id);
            
            if (!$order) {
                return response()->json(['error' => 'Order not found'], 404);
            }

            $order->cancelSecure();
            
            return response()->json([
                'success' => true,
                'message' => 'Order cancelled successfully'
            ]);
            
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 400);
        }
    }
}

Configuration

<?php
// config/database.php

return [
    // ... existing database config

    'secure_schema' => env('SECURE_DB_SCHEMA', 'ecommerce'),
    
    // ... rest of config
];

# .env additions
SECURE_DB_SCHEMA=ecommerce

Artisan Commands for Maintenance

<?php
// app/Console/Commands/SecureDbMaintenance.php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Services\SecureDbService;

class SecureDbMaintenance extends Command
{
    protected $signature = 'securedb:maintain {--verify-hashes : Verify content hashes}';
    protected $description = 'Run secure database maintenance tasks';

    protected $secureDb;

    public function __construct(SecureDbService $secureDb)
    {
        parent::__construct();
        $this->secureDb = $secureDb;
    }

    public function handle()
    {
        $this->info('Running secure database maintenance...');

        // Cleanup expired tokens
        $this->info('Cleaning up expired tokens...');
        $cleanedTokens = DB::selectOne('SELECT c77_secure_db_cleanup_expired_tokens() as count');
        $this->info("Cleaned up {$cleanedTokens->count} expired tokens");

        // Health check
        $health = $this->secureDb->healthCheck();
        $this->info("System health: {$health['extension_version']}");
        $this->info("Error rate (1h): {$health['error_rate_1h']}%");

        // Optional hash verification
        if ($this->option('verify-hashes')) {
            $this->info('Verifying content hashes...');
            
            $customerResults = $this->secureDb->verifyHashes('customers');
            $this->info("Customers: {$customerResults['total_records']} records, {$customerResults['mismatch_count']} mismatches");
            
            $orderResults = $this->secureDb->verifyHashes('orders');
            $this->info("Orders: {$orderResults['total_records']} records, {$orderResults['mismatch_count']} mismatches");
        }

        $this->info('Maintenance completed successfully!');
    }
}

Task Scheduling

<?php
// app/Console/Kernel.php

protected function schedule(Schedule $schedule)
{
    // Daily secure database maintenance
    $schedule->command('securedb:maintain')
        ->daily()
        ->at('02:00')
        ->appendOutputTo(storage_path('logs/securedb-maintenance.log'));

    // Weekly hash verification
    $schedule->command('securedb:maintain --verify-hashes')
        ->weekly()
        ->at('03:00')
        ->appendOutputTo(storage_path('logs/securedb-verification.log'));

    // Monitor system health every 15 minutes
    $schedule->call(function () {
        $secureDb = app(SecureDbService::class);
        $health = $secureDb->healthCheck();
        
        // Alert if error rate is high
        if ($health['error_rate_1h'] > 5) {
            Log::critical('High error rate detected in secure database', $health);
            // Send notification to administrators
        }
        
        // Alert if too many active tokens
        if ($health['active_tokens'] > 100) {
            Log::warning('High number of active tokens detected', $health);
        }
    })->everyFifteenMinutes();
}

Event Listeners for Audit Logging

<?php
// app/Listeners/SecureDbAuditLogger.php

namespace App\Listeners;

use Illuminate\Support\Facades\Log;
use App\Services\SecureDbService;

class SecureDbAuditLogger
{
    protected $secureDb;

    public function __construct(SecureDbService $secureDb)
    {
        $this->secureDb = $secureDb;
    }

    /**
     * Log important business events with integrity verification
     */
    public function handle($event)
    {
        // Example: Log when important customer changes occur
        if ($event instanceof CustomerUpdated) {
            $freshness = $this->secureDb->checkFreshness('customers', $event->customer->toArray());
            
            if (!$freshness['fresh']) {
                Log::critical('Customer data tampering detected!', [
                    'customer_id' => $event->customer->id,
                    'expected_hash' => $freshness['calculated_hash'],
                    'stored_hash' => $freshness['stored_hash']
                ]);
            }
        }
    }
}

Testing Examples

<?php
// tests/Feature/SecureDbTest.php

namespace Tests\Feature;

use Tests\TestCase;
use App\Services\SecureDbService;
use App\Models\Customer;
use Illuminate\Foundation\Testing\RefreshDatabase;

class SecureDbTest extends TestCase
{
    use RefreshDatabase;

    protected $secureDb;

    protected function setUp(): void
    {
        parent::setUp();
        $this->secureDb = app(SecureDbService::class);
    }

    /** @test */
    public function it_can_create_customer_securely()
    {
        $customerData = [
            'email' => 'test@example.com',
            'first_name' => 'Test',
            'last_name' => 'User',
            'phone' => '+1-555-0123'
        ];

        $result = $this->secureDb->insert('customers', $customerData, false);

        $this->assertTrue($result['success']);
        $this->assertEquals('insert', $result['operation']);
        $this->assertNotEmpty($result['content_hash']);
        $this->assertNotEmpty($result['operation_id']);
    }

    /** @test */
    public function it_can_update_customer_securely()
    {
        // Create customer first
        $customer = Customer::createSecure([
            'email' => 'test@example.com',
            'first_name' => 'Test',
            'last_name' => 'User'
        ]);

        // Update customer
        $updateData = [
            'id' => 1, // Assuming first customer
            'first_name' => 'Updated',
            'last_name' => 'Name'
        ];

        $result = $this->secureDb->update('customers', $updateData, false);

        $this->assertTrue($result['success']);
        $this->assertEquals('update', $result['operation']);
        $this->assertEquals(1, $result['rows_affected']);
    }

    /** @test */
    public function it_can_verify_data_freshness()
    {
        // Create customer
        $customerData = [
            'email' => 'fresh@example.com',
            'first_name' => 'Fresh',
            'last_name' => 'Data'
        ];

        $this->secureDb->insert('customers', $customerData, false);

        // Verify freshness
        $freshness = $this->secureDb->checkFreshness('customers', array_merge($customerData, ['id' => 1]));

        $this->assertTrue($freshness['success']);
        $this->assertTrue($freshness['fresh']);
        $this->assertNotEmpty($freshness['stored_hash']);
        $this->assertEquals($freshness['stored_hash'], $freshness['calculated_hash']);
    }

    /** @test */
    public function it_prevents_direct_database_modifications()
    {
        $this->expectException(\Exception::class);
        $this->expectExceptionMessage('Direct modifications not allowed');

        // This should fail because we're bypassing the secure operation
        DB::insert('INSERT INTO ecommerce.customers (email, first_name, last_name) VALUES (?, ?, ?)', [
            'direct@example.com',
            'Direct',
            'Insert'
        ]);
    }

    /** @test */
    public function it_can_run_system_health_check()
    {
        $health = $this->secureDb->healthCheck();

        $this->assertTrue($health['success']);
        $this->assertEquals('2.0', $health['extension_version']);
        $this->assertIsNumeric($health['secure_schemas_count']);
        $this->assertIsNumeric($health['active_tokens']);
    }

    /** @test */
    public function it_can_verify_table_hashes()
    {
        // Create some test data
        $this->secureDb->insert('customers', [
            'email' => 'hash1@example.com',
            'first_name' => 'Hash',
            'last_name' => 'Test1'
        ], false);

        $this->secureDb->insert('customers', [
            'email' => 'hash2@example.com',
            'first_name' => 'Hash',
            'last_name' => 'Test2'
        ], false);

        // Verify all hashes
        $verification = $this->secureDb->verifyHashes('customers');

        $this->assertTrue($verification['success']);
        $this->assertEquals(2, $verification['total_records']);
        $this->assertEquals(0, $verification['mismatch_count']);
    }
}

API Routes

<?php
// routes/api.php

use App\Http\Controllers\CustomerController;
use App\Http\Controllers\OrderController;

Route::middleware(['auth:sanctum', 'securedb'])->group(function () {
    // Customer routes
    Route::prefix('customers')->group(function () {
        Route::post('/', [CustomerController::class, 'store']);
        Route::put('/{id}', [CustomerController::class, 'update']);
        Route::delete('/{id}', [CustomerController::class, 'destroy']);
        Route::post('/{id}/verify', [CustomerController::class, 'verifyIntegrity']);
    });

    // Order routes
    Route::prefix('orders')->group(function () {
        Route::post('/', [OrderController::class, 'store']);
        Route::put('/{id}/status', [OrderController::class, 'updateStatus']);
        Route::delete('/{id}', [OrderController::class, 'cancel']);
    });
});

// Register middleware
// In app/Http/Kernel.php:
protected $routeMiddleware = [
    // ... existing middleware
    'securedb' => \App\Http\Middleware\SecureDbMiddleware::class,
];

🎯 Best Practices Summary

SQL Best Practices

  1. ✅ Always use c77_secure_db_operation() for data modifications
  2. ✅ Include required security columns in table definitions
  3. ✅ Run regular integrity checks with c77_secure_db_verify_content_hashes()
  4. ✅ Monitor system health with c77_secure_db_health_check()
  5. ✅ Use RBAC integration for permission-based access control

Laravel Best Practices

  1. ✅ Create dedicated service classes for secure database operations
  2. ✅ Use middleware to set RBAC user context automatically
  3. ✅ Implement proper error handling and logging
  4. ✅ Override model methods to use secure operations
  5. ✅ Schedule regular maintenance tasks
  6. ✅ Write comprehensive tests for secure operations

Security Best Practices

  1. ✅ Never bypass the secure operation functions
  2. ✅ Always validate input data before secure operations
  3. ✅ Monitor audit logs for suspicious activity
  4. ✅ Implement proper RBAC permissions
  5. ✅ Regular integrity verification of critical data
  6. ✅ Alert on high error rates or token buildup

🔗 Framework Compatibility

While these examples focus on SQL and Laravel, c77_secure_db is designed to work with any framework that connects to PostgreSQL:

  • ✅ Tested & Documented: Pure SQL, Laravel
  • 🔧 Community Contributions Welcome: Node.js, Django, Ruby on Rails, .NET, Java, Python, Go, etc.

The extension's security operates at the database level, making it framework-agnostic. However, we focus our testing and documentation efforts where we have the most expertise to ensure reliability and accuracy.

Want to contribute examples for other frameworks? We'd love to see community contributions that expand our examples to other popular frameworks!