SPAR

SQL Governance Portal

Secure, Controlled Database Access for Enterprise Teams

Proof of Concept

James Hall & Co Ltd • Presented by Adam Entwistle • 2026

01

The Problem

Current challenges with database access and script execution

Challenges We Face

Managing database access across teams presents several critical challenges that impact security, efficiency, and compliance.

Ad-hoc SQL execution without audit trails
Inconsistent access control across teams
Risk of accidental data modification
Difficulty tracking who executed what and when
No standardised approval process for critical operations
Knowledge silos and undocumented scripts

Business Impact
                  No Visibility
                  Who runs what scripts
                
                  No Control
                  Over script access permissions
                
                  No Audit
                  Trail of script execution

💡

Without proper governance, database access becomes a security risk and operational bottleneck.

02

System Architecture

Laravel 12 + Livewire + Blade with PostgreSQL and AI integration

Backend

Laravel 12
PHP 8.3
PostgreSQL

Frontend

Livewire
Blade
Tailwind CSS

Integrations

Google Gemini AI
Microsoft Teams
pgvector

🏗️

Modern tech stack built for scalability, security, and developer productivity.

03

Role-Based Access Control

5-tier permission hierarchy for granular access management

How It Works

The portal implements a comprehensive 5-tier RBAC system that ensures users only access scripts appropriate to their role and expertise level.

First Line Support - Read-only access to safe queries
Second Line Support - Execute low-risk scripts
Retail Developers - Create and modify scripts
Infrastructure Team - Execute high-risk operations
Administrators - Full system management

Implementation

enum UserRole: string
{
    case FIRST_LINE = 'first_line';
    case SECOND_LINE = 'second_line';
    case RETAIL_DEV = 'retail_dev';
    case INFRASTRUCTURE = 'infrastructure';
    case ADMIN = 'admin';

    public function hierarchy(): int
    {
        return match ($this) {
            self::FIRST_LINE => 1,
            self::SECOND_LINE => 2,
            self::RETAIL_DEV => 3,
            self::INFRASTRUCTURE => 4,
            self::ADMIN => 5,
        };
    }

    public function hasPermission(UserRole $required): bool
    {
        return $this->hierarchy() >= $required->hierarchy();
    }
}

Each role has a hierarchy level. Users can only access scripts requiring their level or below.

🔐

Granular permissions ensure the right people have the right access at the right time.

04

SQL Sanitisation

Protected variable injection prevents SQL injection attacks

Safe Variable Injection

Users define variables in scripts using placeholders. The system validates, sanitises, and safely injects values at runtime.

Type validation (integer, string, date, etc.)
Automatic parameterised query generation
Input sanitisation and escaping
Preview mode shows final SQL before execution
Prevents SQL injection attacks

Type-Aware Sanitisation

public function sanitiseValue(string $value, string $type): string
{
    return match ($type) {
        'int', 'integer' => $this->sanitiseInteger($value),
        'decimal', 'float' => $this->sanitiseDecimal($value),
        'date' => $this->sanitiseDate($value),
        default => $this->sanitiseString($value),
    };
}

private function sanitiseString(string $value): string
{
    // Escape single quotes by doubling them
    $cleaned = str_replace("'", "''", $value);
    return "'{$cleaned}'";
}

🛡️

Zero SQL injection risk - all variables are validated and parameterised.

05

Transaction Safety

Automatic rollback protection for high-risk operations

Automatic Transaction Wrapping

High-risk scripts (UPDATE, DELETE, INSERT) are automatically wrapped in transactions with rollback capability.

Scripts marked as high-risk automatically get transaction wrapping
Preview changes before committing
Explicit COMMIT required for changes to persist
Automatic ROLLBACK on errors
Transaction log for debugging

private const TRANSACTION_HEADER = <<<'SQL'
-- HIGH RISK SCRIPT - Review carefully
BEGIN TRANSACTION;
SQL;

private const TRANSACTION_FOOTER = <<<'SQL'
-- IMPORTANT: Review results before committing!
-- Uncomment COMMIT to apply, or ROLLBACK to abort

-- COMMIT;
ROLLBACK;
SQL;

public function applyTransactionWrapper(
    string $sql, RiskLevel $riskLevel
): array {
    if (!$riskLevel->requiresTransactionWrapper()) {
        return [$sql, false];
    }
    return [self::TRANSACTION_HEADER . $sql . self::TRANSACTION_FOOTER, true];
}

⚡

Test first, commit only when certain - prevents accidental data modification.

06

Approval Workflows

Manager approval required for critical operations

Multi-Stage Approval Process

Critical scripts require manager approval before execution, creating a safety checkpoint for high-impact operations.

Scripts flagged as "requires approval"
Approval request sent to designated managers
Teams notifications for pending approvals
Approval with comments and conditions
Time-limited approval windows
Full audit trail of approval chain

✅

Human oversight for critical operations - automated safety with manual verification.

07

Comprehensive Audit Trail

Every action logged for compliance and security

Complete Activity Logging

All user actions, script executions, and system events are logged with full context for compliance and forensic analysis.

Who executed what script and when
Input parameters and variables used
Execution results and row counts
Approval chain and timestamps
Failed attempts and errors
Export logs for compliance reporting

Audit Logs

📋

Full traceability meets compliance requirements and enables forensic investigation.

08

AI-Powered Search

Semantic search using Google Gemini embeddings and pgvector

Intelligent Script Discovery

Find scripts by describing what you need in natural language - no need to remember exact names or keywords.

Semantic search understands intent, not just keywords
Google Gemini generates embeddings for all scripts
pgvector extension for similarity search
Search by description, use case, or problem
Related scripts suggested automatically
Continuous learning from usage patterns

Script Vault - AI Search

Vector Search Implementation

// Generate embedding for search query
$queryEmbedding = $this->embeddingService->generateQueryEmbedding($query);

// pgvector cosine similarity search
$sql = "
    SELECT id, name, description,
        1 - (embedding <=> :embedding::vector) as similarity
    FROM scripts
    WHERE state = 'checked_in'
    AND embedding IS NOT NULL
    ORDER BY embedding <=> :embedding2::vector
    LIMIT :limit
";

The <=> operator calculates cosine distance. Lower distance = higher similarity.

🤖

Find what you need by describing the problem, not memorising script names.

09

Microsoft Teams Integration

Notifications and approvals directly in Teams

Seamless Workflow Integration

Receive notifications and approve requests without leaving Teams - reducing context switching and accelerating workflows.

Execution notifications with result summaries
Approval requests as adaptive cards
One-click approve/reject from Teams
Error alerts sent to relevant channels
Daily/weekly digest reports
@mentions for urgent requests

Approvals Queue

💬

Work where you already are - approvals and notifications in your existing workflow.

10

Version History

Full version control with diffs and rollback

Track Every Change

Complete version history for all scripts with visual diffs and one-click rollback to any previous version.

Automatic versioning on every script change
Visual diff view showing what changed
Restore previous versions instantly
Track who made changes and when
Change comments and rationale
Compare any two versions

Script Detail with Version History

🔄

Never lose work - complete version control for all scripts with easy rollback.

11

User Workflows

Experience the portal in action

Teams Bot Workflow

Demo Flow

1

Browse Script Vault

Filter by category, risk level, permissions

2

Select & Configure

Choose script, fill in variables

3

Preview & Execute

Review generated SQL, execute safely

4

Review Results

View output, download reports

5

Check Audit Log

See execution history and details

6

AI Search

Find scripts using natural language

Dashboard

Checkout Process

🎯

See the complete workflow from script discovery to execution and audit.

Security in Depth

Security First

RBAC, sanitisation, transaction safety, and approval workflows ensure database operations are secure by default.

Compliance Ready

Comprehensive audit trails and approval workflows meet regulatory requirements and enable forensic analysis.

Developer Friendly

AI-powered search, version control, and Teams integration make the portal intuitive and efficient.

Modern Stack

Built with Laravel 12, Livewire, and AI integration - ready for future enhancements.