Download the PHP package mamun724682/laravel-db-governor without Composer

Laravel DB Governor

A production-safety database governance layer for Laravel teams. Intercept, review, approve, execute, and roll back SQL queries through a secure web UI — with full audit trails, risk scoring, and row-level snapshots.

Why DB Governor?

Running raw UPDATE or DELETE queries directly on a production database is dangerous. One wrong WHERE clause (or no clause at all) can silently wipe data. DB Governor forces every write query through a structured approval workflow before it ever touches your database.

• ✅ Employees submit queries → Admins review & approve → Execution is controlled
• ✅ Every query is scored for risk and flagged for dangerous patterns
• ✅ Before execution, row-level snapshots are captured for instant rollback
• ✅ Full audit log with submitter, reviewer, executor identity and timestamps
• ✅ Works across multiple database connections from a single UI

Features

🔐 Role-Based Access

Two roles configured entirely via environment variables — no extra tables required.

Token-based sessions (stored in cache) with configurable expiry (DB_GOVERNOR_TOKEN_EXPIRY). Role is re-derived on every request so promotions/demotions take effect without re-login.

📋 Query Approval Workflow

Every query record stores:

• Raw SQL, query type (READ / WRITE / DDL), affected table
• Submitter email + IP, submission timestamp
• Reviewer email, review timestamp, review note
• Executor email, execution timestamp, rows affected, execution time (ms)
• Execution error (if any) — surfaced immediately in the UI

⚠️ Automatic Risk Scoring

Every submitted query is analysed before it enters the queue:

Blocked/flagged patterns are fully customisable regex arrays in the config. A dry-run engine estimates affected row counts before submission.

↩️ Row-Level Rollback

Before executing any UPDATE or DELETE, DB Governor captures a before-state snapshot of every affected row. If something goes wrong after execution, a one-click Rollback:

• Re-inserts deleted rows (via INSERT)
• Restores updated columns to their original values (via UPDATE … WHERE pk = ?)
• Wraps everything in a single database transaction

Snapshots are stored as JSON in the audit record. Configurable via snapshot_max_rows (default: 500 rows).

🖥️ SQL Console

A full in-browser SQL console with two modes:

Query Builder (visual, no SQL knowledge needed)

• SELECT — pick columns, add WHERE / ORDER BY / LIMIT
• INSERT — all non-id columns pre-filled; required fields (NOT NULL, no default) are marked with * and locked
• UPDATE — add/remove SET columns with WHERE condition
• DELETE — WHERE condition with danger warning

Raw SQL — free-form textarea with:

• SQL keyword + table + column autocomplete
• Arrow-key navigation through suggestions

Value inputs adapt to column type automatically:

• date / datetime / timestamp → native date/time pickers
• json → resizable monospace textarea
• Everything else → plain text input

📊 Table Browser

Browse any table directly in the UI:

• Paginated rows (25 per page), sortable column headers
• Advanced filter builder with AND / OR groups and operators (=, !=, LIKE, NOT LIKE, >, <, >=, <=, IN, IS NULL, IS NOT NULL)
• Smart value inputs in filters (same date-picker / JSON textarea logic as the query builder)
• All filtered browse actions are logged as read queries for the audit trail

🗂️ Sidebar Navigation

• Expandable table list — click the chevron to reveal columns inline
• Column list shows name (monospace), type, and a red dot for required (NOT NULL, no default) vs grey dot for optional
• Live search to filter tables by name

🔍 Query Log

• Filterable by status, query type, keyword, date range, and submitter (admins only)
• Tabbed view: Write Queries / Read Queries
• Click any row to open a detail modal with full SQL, risk flags, review details, execution details, snapshot data, and rollback info

⚡ Schema Cache

Table list and column metadata are cached (default: 5 minutes) to avoid repeated introspection queries. Configurable via DB_GOVERNOR_SCHEMA_CACHE_TTL.

🔌 Multi-Connection Support

Map multiple Laravel database connections to named URL slugs:

Switch between connections from the UI. Each connection has its own table browser, query log, and SQL console.

🛡️ Hidden Tables

Framework-internal tables (migrations, cache, sessions, jobs, telescope_*, etc.) are hidden from the UI by default and blocked from query execution.

Requirements

Installation

Publish the config and run migrations:

Optionally publish views to customise the UI:

Configuration

Add to your .env:

Full config reference at config/db-governor.php after publishing.

Usage

Navigate to http://your-app.test/db-governor and log in with an admin or employee email.

Workflow example

Customising Risk Patterns

Multiple Connections

Each connection gets its own URL: /db-governor/prod, /db-governor/staging, etc.

Security

• Login issues a random 32-character token; the token string is stored in the Laravel session, the payload (email, role, expiry) is stored in the cache — no auth table or users table required
• Token expiry is configurable; tokens are invalidated immediately on logout
• Role is re-checked on every request — revoking access in config takes immediate effect without re-login
• Login is rate-limited to 5 attempts per minute per IP; the counter clears on successful login
• All write query execution is gated behind the approval workflow
• Blocked patterns are matched after stripping SQL comments to prevent /* bypass */ evasion
• Hidden tables are enforced both in the UI and at the query execution layer

Development

Contributing

Pull requests are welcome. Please open an issue first to discuss significant changes.

1. Fork the repository
2. Create a feature branch (git checkout -b feat/my-feature)
3. Write tests for your changes
4. Ensure composer test and composer lint both pass
5. Submit a pull request

Support

If DB Governor helps your team, please star the repository.

License

The MIT License (MIT). See LICENSE for details.