/guardrails-db-init

/guardrails-db-init

Creates the SQL Server schema and tables required for guardrails audit persistence. Safe to run multiple times -- uses IF NOT EXISTS guards throughout.

Connection method is chosen based on the host OS detected at runtime:

• Windows — uses sqlcmd (SQL Server native command-line driver).
• Linux / macOS — uses a Python script via pyodbc + ODBC Driver 18.

Prerequisites

• .claude/.guardrails-agent.json must exist in the workspace with audit.db.connectionString set. The hook also accepts .guardrails-agent.json at the workspace root as a fallback (this is the path other agents such as sprint and story check for their prerequisite guard).
• The database user in the connection string must have CREATE SCHEMA and CREATE TABLE permissions (or the schema must already exist and the user must have CREATE TABLE within it).

Windows only:

• sqlcmd must be installed and available on PATH (ships with SQL Server or installable via SQL Server command-line tools).

Linux / macOS only:

• pyodbc and ODBC Driver 18 for SQL Server must be installed on the host.

What it does

1. Reads audit.db.connectionString, audit.db.schema (default: guardrails), and audit.db.tablePrefix (default: "") from .claude/.guardrails-agent.json.
2. Detects the host OS:
   • Windows ($env:OS starts with Windows or [System.Runtime.InteropServices.RuntimeInformation]::IsOSPlatform returns Windows): writes the SQL statements to a temporary .sql file and invokes sqlcmd with the connection parameters parsed from connectionString.
   • Linux / macOS (all other platforms): executes a Python script that uses pyodbc to connect and run the same SQL statements.
3. Connects to SQL Server (3-second timeout).
4. Creates the schema if it does not exist:

   IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'{schema}')
       EXEC('CREATE SCHEMA [{schema}]');
   

5. Creates the four tables if they do not exist:

[{schema}].[{prefix}AuditLog]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}AuditLog'
)
CREATE TABLE [{schema}].[{prefix}AuditLog] (
    [Id]          BIGINT          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Timestamp]   DATETIMEOFFSET  NOT NULL,
    [AgentFamily] NVARCHAR(100)   NOT NULL,
    [Skill]       NVARCHAR(100)   NOT NULL,
    [Check]       NVARCHAR(200)   NULL,
    [Result]      NVARCHAR(50)    NOT NULL,
    [Severity]    NVARCHAR(10)    NOT NULL CONSTRAINT [DF_{prefix}AuditLog_Severity]    DEFAULT 'P2',
    [Detail]      NVARCHAR(MAX)   NULL,
    [UserStoryId] NVARCHAR(200)   NULL,
    [SessionId]   NVARCHAR(200)   NOT NULL,
    [ResolvedBy]  NVARCHAR(50)    NOT NULL CONSTRAINT [DF_{prefix}AuditLog_ResolvedBy]  DEFAULT 'unresolved',
    [Source]      NVARCHAR(50)    NOT NULL CONSTRAINT [DF_{prefix}AuditLog_Source]       DEFAULT 'skill'
);

[{schema}].[{prefix}CostLog]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}CostLog'
)
CREATE TABLE [{schema}].[{prefix}CostLog] (
    [Id]                 BIGINT          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Timestamp]          DATETIMEOFFSET  NOT NULL,
    [UserStoryId]        NVARCHAR(200)   NULL,
    [SessionId]          NVARCHAR(200)   NOT NULL,
    [SessionTokensUsed]  INT             NOT NULL,
    [ModelRateKey]       NVARCHAR(100)   NOT NULL,
    [SessionCostUSD]     DECIMAL(18,6)   NOT NULL,
    [AccumulatedCostUSD] DECIMAL(18,6)   NOT NULL,
    [MaxCostUSD]         DECIMAL(18,6)   NOT NULL
);

[{schema}].[{prefix}RateLog]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}RateLog'
)
CREATE TABLE [{schema}].[{prefix}RateLog] (
    [Id]          BIGINT          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Timestamp]   DATETIMEOFFSET  NOT NULL,
    [UserId]      NVARCHAR(200)   NOT NULL,
    [AgentFamily] NVARCHAR(100)   NOT NULL
);

[{schema}].[{prefix}CircuitBreakerState]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}CircuitBreakerState'
)
CREATE TABLE [{schema}].[{prefix}CircuitBreakerState] (
    [AgentFamily]          NVARCHAR(100)  NOT NULL CONSTRAINT [PK_{prefix}CircuitBreakerState] PRIMARY KEY,
    [ConsecutiveFailures]  INT            NOT NULL CONSTRAINT [DF_{prefix}CB_Failures]   DEFAULT 0,
    [LastFailureTimestamp] DATETIMEOFFSET NULL,
    [Tripped]              BIT            NOT NULL CONSTRAINT [DF_{prefix}CB_Tripped]    DEFAULT 0,
    [TripTimestamp]        DATETIMEOFFSET NULL
);

[{schema}].[{prefix}MemoryLog]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}MemoryLog'
)
CREATE TABLE [{schema}].[{prefix}MemoryLog] (
    [Id]              BIGINT          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Timestamp]       DATETIMEOFFSET  NOT NULL,
    [AgentFamily]     NVARCHAR(100)   NOT NULL,
    [SessionId]       NVARCHAR(200)   NOT NULL,
    [UserStoryId]     NVARCHAR(200)   NULL,
    [TicketSummary]   NVARCHAR(500)   NULL,
    [FilesModified]   NVARCHAR(MAX)   NULL,   -- JSON array
    [PatternsChosen]  NVARCHAR(MAX)   NULL,   -- JSON object
    [TeamConventions] NVARCHAR(MAX)   NULL,   -- JSON array
    [LessonsLearned]  NVARCHAR(MAX)   NULL,   -- JSON array
    [Outcome]         NVARCHAR(50)    NOT NULL CONSTRAINT [DF_{prefix}MemoryLog_Outcome] DEFAULT 'completed',
    [EmbeddedAt]      DATETIMEOFFSET  NULL     -- set when ChromaDB upsert succeeds
);

[{schema}].[{prefix}StoryMemoryLog]

IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}StoryMemoryLog'
)
CREATE TABLE [{schema}].[{prefix}StoryMemoryLog] (
    [Id]            BIGINT          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Timestamp]     DATETIMEOFFSET  NOT NULL,
    [TicketId]      NVARCHAR(200)   NOT NULL,
    [TicketSummary] NVARCHAR(500)   NULL,
    [BoardSource]   NVARCHAR(50)    NULL,
    [AgentsUsed]    NVARCHAR(MAX)   NULL,     -- JSON array
    [StoryPlanPath] NVARCHAR(500)   NULL,
    [SessionId]     NVARCHAR(200)   NOT NULL,
    [Outcome]       NVARCHAR(50)    NOT NULL,
    [DurationSecs]  INT             NULL,
    [EmbeddedAt]    DATETIMEOFFSET  NULL      -- set when ChromaDB upsert succeeds
);

EmbeddedAt migration (idempotent)

For existing databases that already have the MemoryLog and StoryMemoryLog tables, add the EmbeddedAt column if it does not already exist:

IF NOT EXISTS (
    SELECT 1 FROM sys.columns c
    JOIN   sys.tables  t ON t.object_id = c.object_id
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}MemoryLog' AND c.name = N'EmbeddedAt'
)
ALTER TABLE [{schema}].[{prefix}MemoryLog] ADD [EmbeddedAt] DATETIMEOFFSET NULL;

IF NOT EXISTS (
    SELECT 1 FROM sys.columns c
    JOIN   sys.tables  t ON t.object_id = c.object_id
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    WHERE  s.name = N'{schema}' AND t.name = N'{prefix}StoryMemoryLog' AND c.name = N'EmbeddedAt'
)
ALTER TABLE [{schema}].[{prefix}StoryMemoryLog] ADD [EmbeddedAt] DATETIMEOFFSET NULL;

6. Reports success or failure per table. Example output:

   [platform] Windows -- using sqlcmd
   [ok] Schema  [guardrails] -- already exists
   [ok] Table   [guardrails].[AuditLog] -- created
   [ok] Table   [guardrails].[CostLog] -- already exists
   [ok] Table   [guardrails].[RateLog] -- created
   [ok] Table   [guardrails].[CircuitBreakerState] -- created
   [ok] Table   [guardrails].[MemoryLog] -- created
   [ok] Table   [guardrails].[StoryMemoryLog] -- created
   DB init complete. All audit tables are ready.
   

Platform-Specific Execution

Windows — sqlcmd

Parse the ODBC-style connectionString to extract Server, Database, Uid (or Trusted_Connection=yes for Windows auth), then:

# Write SQL to a temp file
$sqlFile = [System.IO.Path]::GetTempFileName() + ".sql"
Set-Content -Path $sqlFile -Value $sqlStatements -Encoding UTF8

# SQL auth
sqlcmd -S $server -d $database -U $uid -P $pwd -i $sqlFile -b

# Windows auth (Trusted_Connection=yes)
sqlcmd -S $server -d $database -E -i $sqlFile -b

• -b causes sqlcmd to exit with a non-zero code on SQL error (fail-fast).
• Delete the temp .sql file after execution regardless of success or failure.
• If sqlcmd is not found on PATH: stop with "sqlcmd not found. Install SQL Server command-line tools and ensure sqlcmd is on PATH."

Linux / macOS — Python script

import pyodbc, json, sys

conn_str = "<value of audit.db.connectionString>"
schema   = "<audit.db.schema>"
prefix   = "<audit.db.tablePrefix>"

conn = pyodbc.connect(conn_str, timeout=3)
cursor = conn.cursor()

# execute schema + table DDL statements here
# report [ok] / [err] per statement
conn.commit()
conn.close()

• If pyodbc is not importable: stop with "pyodbc is not installed. Run pip install pyodbc and install ODBC Driver 18 for SQL Server."

Error Handling

• If audit.db.connectionString is absent from config: stop with "audit.db.connectionString is not configured. Add it to .claude/.guardrails-agent.json (or .guardrails-agent.json at the workspace root) before running /guardrails-db-init."
• If OS detection is inconclusive: default to the Linux/macOS Python path.
• If the required tool (sqlcmd on Windows, pyodbc on Linux/macOS) is unavailable: stop with a clear installation message (see above) before attempting any connection.
• If connection fails: report the error and stop. Do not attempt partial table creation.
• If a CREATE TABLE statement fails (e.g., insufficient permissions): report the failing table and stop. Tables created before the failure remain.

Steps

• Read .claude/.guardrails-agent.json and extract audit.db.connectionString, audit.db.schema, audit.db.tablePrefix
• Validate that connectionString is present; abort with a clear message if not
• Detect host OS (Windows → sqlcmd path; Linux/macOS → Python path)
• Verify the required tool is available (sqlcmd on Windows; pyodbc importable on Linux/macOS); abort with install instructions if not
• Connect to SQL Server (3-second timeout)
• Create schema if not exists
• Create AuditLog table if not exists -- report result
• Create CostLog table if not exists -- report result
• Create RateLog table if not exists -- report result
• Create CircuitBreakerState table if not exists -- report result
• Create MemoryLog table if not exists -- report result
• Create StoryMemoryLog table if not exists -- report result
• Print summary and confirm DB is ready for guardrails audit writes