Sign in Subscribe

This first post outlines the exact requirements I provided to Claude.

The Challenge

Our Laravel 12 application needed a robust system for managing temporary database access. Instead of permanent credentials floating around, we wanted Just-in-Time (JIT) provisioning where users request access, get approval, and receive auto-generated credentials that expire automatically.

The Requirements Document

Here's the complete requirements specification I gave to Claude:

Project Context

You are building a comprehensive Database Privileged Access Management (PAM) system for Laravel 12 that provides encrypted admin configuration storage and Just-in-Time (JIT) user provisioning. This system allows users to request temporary database access, get approval, and receive auto-generated database credentials during their approved session time.

Database Schema

I provided Claude with our existing schema:

-- Assets table: Database servers/instances
CREATE TABLE assets (
    id BIGINT PRIMARY KEY,
    org_id BIGINT REFERENCES orgs(id),
    name VARCHAR(100),
    description TEXT,
    status ENUM('active', 'inactive'),
    host VARCHAR(255),
    port SMALLINT UNSIGNED,
    dbms ENUM('mysql', 'postgresql', 'sqlserver', 'oracle', 'mongodb', 'redis', 'mariadb'),
    created_by INT,
    created_at TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP,
    deleted_by INT,
    deleted_at TIMESTAMP
);

-- Asset Accounts: Both admin and JIT accounts
CREATE TABLE asset_accounts (
    id BIGINT PRIMARY KEY,
    asset_id BIGINT REFERENCES assets(id) ON DELETE CASCADE,
    name VARCHAR(100),
    username VARCHAR(100),
    password TEXT, -- encrypted
    type ENUM('admin', 'jit') DEFAULT 'admin',
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP,
    UNIQUE KEY unique_admin_per_asset (asset_id, type) WHERE type = 'admin'
);

-- Asset Access Grants: Permission management
CREATE TABLE asset_access_grants (
    id BIGINT PRIMARY KEY,
    asset_id BIGINT REFERENCES assets(id) ON DELETE CASCADE,
    user_id BIGINT NULL REFERENCES users(id) ON DELETE CASCADE,
    user_group_id BIGINT NULL REFERENCES user_groups(id) ON DELETE CASCADE,
    role ENUM('requester', 'approver', 'auditor'),
    created_by INT,
    created_at TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP,
    UNIQUE KEY unique_access_grant (asset_id, user_id, user_group_id, role),
    CONSTRAINT check_user_or_user_group CHECK (user_id IS NOT NULL OR user_group_id IS NOT NULL)
);

-- Requests: Access requests from users
CREATE TABLE requests (
    id BIGINT PRIMARY KEY,
    org_id BIGINT REFERENCES orgs(id),
    asset_id BIGINT REFERENCES assets(id),
    asset_account_id BIGINT NULL REFERENCES asset_accounts(id) ON DELETE SET NULL,
    requester_id BIGINT REFERENCES users(id),
    start_datetime TIMESTAMP,
    end_datetime TIMESTAMP,
    duration SMALLINT UNSIGNED,
    reason TEXT,
    intended_query TEXT NULL,
    scope ENUM('read_only', 'read_write', 'admin') DEFAULT 'read_only',
    is_access_sensitive_data BOOLEAN DEFAULT FALSE,
    sensitive_data_note TEXT NULL,
    approver_note TEXT NULL,
    approver_risk_rating ENUM('low', 'medium', 'high') NULL,
    ai_note TEXT NULL,
    ai_risk_rating ENUM('low', 'medium', 'high') NULL,
    status ENUM('pending', 'approved', 'rejected', 'expired') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    rejected_by INT NULL,
    rejected_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP
);

-- Sessions: Active database sessions
CREATE TABLE sessions (
    id BIGINT PRIMARY KEY,
    org_id BIGINT REFERENCES orgs(id),
    request_id BIGINT REFERENCES requests(id),
    asset_id BIGINT REFERENCES assets(id),
    asset_account_id BIGINT NULL REFERENCES asset_accounts(id) ON DELETE SET NULL,
    requester_id BIGINT REFERENCES users(id),
    approver_id BIGINT REFERENCES users(id),
    start_datetime TIMESTAMP,
    end_datetime TIMESTAMP NULL,
    scheduled_end_datetime TIMESTAMP,
    requested_duration SMALLINT UNSIGNED,
    actual_duration SMALLINT UNSIGNED NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    account_name VARCHAR(100) NULL,
    session_note TEXT NULL,
    is_expired BOOLEAN DEFAULT FALSE,
    is_terminated BOOLEAN DEFAULT FALSE,
    is_checkin BOOLEAN DEFAULT FALSE,
    status ENUM('scheduled', 'active', 'ended', 'expired') DEFAULT 'scheduled',
    checkin_by BIGINT NULL REFERENCES users(id),
    checkin_at TIMESTAMP NULL,
    terminated_by BIGINT NULL REFERENCES users(id),
    terminated_at TIMESTAMP NULL,
    ended_by BIGINT NULL REFERENCES users(id),
    ended_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP
);

-- Session Audits: Query audit logs
CREATE TABLE session_audits (
    id BIGINT PRIMARY KEY,
    org_id BIGINT REFERENCES orgs(id) ON DELETE CASCADE,
    session_id BIGINT REFERENCES sessions(id) ON DELETE CASCADE,
    request_id BIGINT REFERENCES requests(id) ON DELETE CASCADE,
    asset_id BIGINT REFERENCES assets(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    query_text TEXT,
    query_timestamp TIMESTAMP,
    created_at TIMESTAMP
);

Complete Workflow

I outlined the five-phase workflow:

1. Setup Phase

  • Admin creates Asset (database server details)
  • Admin creates admin AssetAccount for each Asset (stored encrypted in database)
  • Admin configures asset_access_grants (who can request/approve access)

2. Request Phase

  • User submits Request for database access to specific Asset
  • Request includes: timeframe, duration, scope (read_only/read_write/admin), reason
  • System creates Request record with status 'pending'

3. Approval Phase

  • Approver reviews Request and either approves or rejects
  • If approved: Request status = 'approved', Session record created with status 'scheduled'

4. Session Start Phase

  • During approved timeframe, user can start their Session
  • System triggers JIT account creation:
    • SecretsManager retrieves admin credentials from asset_accounts
    • Generates secure JIT username/password
    • DatabaseDriver creates JIT user in target database with appropriate permissions
    • Stores JIT account in asset_accounts table (type='jit', expires_at=session_end)
    • Updates Session with asset_account_id = jit_account.id
  • User receives JIT database credentials for direct connection

5. Session End Phase

  • When session expires OR user manually ends session:
    • SecretsManager retrieves JIT user's query logs from target database
    • Stores audit logs in session_audits table
    • DatabaseDriver terminates JIT user in target database
    • Deletes JIT account from asset_accounts table
    • Session.asset_account_id becomes NULL (due to ON DELETE SET NULL)
    • Session status = 'ended'

Technical Requirements

I specified the file structure and interfaces:

app/Services/
├── Secrets/
│   └── SecretsManager.php
├── Database/
│   ├── DatabaseDriverFactory.php
│   ├── Contracts/
│   │   └── DatabaseDriverInterface.php
│   └── Drivers/
│       ├── AbstractDatabaseDriver.php
│       ├── PostgreSQLDriver.php
│       ├── MySQLDriver.php
│       ├── SqliteDriver.php
│       └── SqlServerDriver.php

DatabaseDriverInterface:

interface DatabaseDriverInterface
{
    public function createUser(string $username, string $password, string $database, string $scope, Carbon $expiresAt): bool;
    public function terminateUser(string $username, string $database): bool;
    public function testAdminConnection(array $adminCredentials): bool;
    public function retrieveUserQueryLogs(string $username, Carbon $fromTime, Carbon $toTime): array;
    public function validateScope(string $scope): bool;
    public function generateSecureCredentials(): array;
}

SecretsManager Core Methods:

class SecretsManager
{
    public function createAccount(Session $session): AssetAccount;
    public function terminateAccount(Session $session): array;
    public function getAdminCredentials(Asset $asset): array;
    public function generateCredentials(Asset $asset): array;
    public function getDatabaseDriver(Asset $asset): DatabaseDriverInterface;
    public function retrieveQueryLogs(AssetAccount $account, Session $session): array;
}

Security Requirements

  • All passwords encrypted using Laravel's Crypt facade
  • Admin credentials never exposed to end users
  • JIT credentials shown only after session start
  • Comprehensive audit logging for all operations
  • Secure credential generation (random usernames/passwords)

Error Handling

  • JIT creation failure: Mark session as failed
  • Database connection issues: Graceful degradation with logging
  • JIT termination failure: Mark for manual review
  • Audit retrieval failure: Continue termination, log warning

Key Implementation Notes

I emphasized these points to Claude:

  1. Use existing Laravel models: Asset, AssetAccount, Request, Session, SessionAudit
  2. Leverage existing traits: HasBlamable, BelongsToOrganization, etc.
  3. Follow Laravel conventions: Service classes, Factory pattern, Interface contracts
  4. Database-agnostic design: Support multiple database types through driver pattern
  5. Security-first approach: Encryption, audit trails, secure credential generation
  6. Cleanup automation: Expired JIT accounts automatically terminated

The Ask

Finally, I gave Claude this directive:

Build this complete database secrets management system following Laravel best practices. Implement all the services, drivers, and integrate with the existing models. Focus on security, auditability, and maintainability. The system should handle the complete lifecycle from request submission to JIT account cleanup while maintaining comprehensive audit trails.

Make sure to:
- Implement proper error handling and logging
- Add comprehensive validation
- Follow SOLID principles
- Write clean, documented code
- Consider edge cases and failure scenarios
- Implement proper transaction handling for critical operations

What's Next

In Part 2, I'll share the core implementation that Claude produced - the SecretsManager, database drivers, and the factory pattern that makes it all work together.

In Part 3, we'll look at the integration components - the API endpoints, job queues, real-time updates, and monitoring dashboard.

Stay tuned to see how Claude transformed these requirements into a production-ready PAM system.

This is part 1 of a 3-part series on building a Database PAM system with Claude AI. Read Part 2: Core Implementation →

Database PAM System Requirements: What I Asked Claude to Build (Part 1)