The Schema Advisor

The Schema Advisor: give it a schema and a query log, get back the advisories a careful DBA would write - each rule its own capability, each table analyzed as its own task.

source on github

a real captured run

SCHEMA REVIEW: 3 tables, 4 logged queries, 13 advisories (completed)

HIGH
  - queries filter on users.email but no index covers it - add_index :users, :email
  - queries filter on orders.user_id but no index covers it - add_index :orders, :user_id
  - queries filter on orders.status but no index covers it - add_index :orders, :status
  - orders.total_cents stores money as float - use integer cents or decimal before rounding errors become refunds
  - queries filter on audit_logs.user_id but no index covers it - add_index :audit_logs, :user_id

MEDIUM
  - users.email allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - users.created_at allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - orders.user_id allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - orders.status allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - orders.total_cents allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - audit_logs.payload allows NULL - if it's required, say so: NOT NULL with a default beats a validation
  - audit_logs.user_id allows NULL - if it's required, say so: NOT NULL with a default beats a validation

LOW
  - audit_logs.uuid is a text primary key - fine if it's truly a UUID column type, expensive if it's a string

source

# frozen_string_literal: true

# The Schema Advisor: give it a schema and a query log, get back the
# advisories a careful DBA would write - each rule its own capability,
# each table analyzed as its own task.
#
#   bundle exec ruby examples/schema_advisor.rb
#
# Runs offline: the rules are deterministic. Rules that fire are facts
# about your schema, not opinions from a model.

require class="s">"bundler/setup"
require class="s">"agentic"

SCHEMA = {
  class="s">"users" => {
    columns: {
      class="s">"id" => {type: class="s">"integer", primary_key: true},
      class="s">"email" => {type: class="s">"text", null: true},
      class="s">"created_at" => {type: class="s">"timestamp", null: true}
    },
    indexes: [class="s">"id"]
  },
  class="s">"orders" => {
    columns: {
      class="s">"id" => {type: class="s">"integer", primary_key: true},
      class="s">"user_id" => {type: class="s">"integer", null: true},
      class="s">"status" => {type: class="s">"text", null: true},
      class="s">"total_cents" => {type: class="s">"float", null: true}
    },
    indexes: [class="s">"id"]
  },
  class="s">"audit_logs" => {
    columns: {
      class="s">"uuid" => {type: class="s">"text", primary_key: true},
      class="s">"payload" => {type: class="s">"text", null: true},
      class="s">"user_id" => {type: class="s">"integer", null: true}
    },
    indexes: []
  }
}.freeze

QUERY_LOG = [
  class="s">"SELECT * FROM orders WHERE user_id = ?",
  class="s">"SELECT * FROM orders WHERE status = ? ORDER BY id DESC",
  class="s">"SELECT * FROM users WHERE email = ?",
  class="s">"SELECT * FROM audit_logs WHERE user_id = ?"
].freeze

def register_rule(name, &impl)
  spec = Agentic:class="y">:CapabilitySpecification.new(
    name: name, description: name.tr(class="s">"_", class="s">" "), version: class="s">"1.0.0",
    inputs: {
      table: {type: class="s">"string", required: true},
      definition: {type: class="s">"object", required: true},
      queries: {type: class="s">"array", required: true}
    },
    outputs: {advisories: {type: class="s">"array", required: true}}
  )
  Agentic.register_capability(
    spec, Agentic:class="y">:CapabilityProvider.new(capability: spec, implementation: impl)
  )
end

register_rule(class="s">"check_missing_indexes") do |input|
  table, definition, queries = input.values_at(class="y">:table, class="y">:definition, class="y">:queries)
  filtered = queries.filter_map { |q| q[/FROM #{table} WHERE (\w+)/, 1] }.uniq
  advisories = (filtered - definition[class="y">:indexes]).map do |column|
    {severity: class="s">"high", table: table,
     advice: class="s">"queries filter on #{table}.#{column} but no index covers it - add_index :#{table}, :#{column}"}
  end
  {advisories: advisories}
end

register_rule(class="s">"check_null_discipline") do |input|
  table, definition = input.values_at(class="y">:table, class="y">:definition)
  advisories = definition[class="y">:columns].filter_map do |column, meta|
    next if meta[class="y">:primary_key] || meta[class="y">:null] == false

    {severity: class="s">"medium", table: table,
     advice: class="s">"#{table}.#{column} allows NULL - if it's required, say so: NOT NULL with a default beats a validation"}
  end
  {advisories: advisories}
end

register_rule(class="s">"check_money_types") do |input|
  table, definition = input.values_at(class="y">:table, class="y">:definition)
  advisories = definition[class="y">:columns].filter_map do |column, meta|
    next unless column.match?(/cents|price|amount|total/) && meta[class="y">:type] == class="s">"float"

    {severity: class="s">"high", table: table,
     advice: class="s">"#{table}.#{column} stores money as float - use integer cents or decimal before rounding errors become refunds"}
  end
  {advisories: advisories}
end

register_rule(class="s">"check_text_primary_keys") do |input|
  table, definition = input.values_at(class="y">:table, class="y">:definition)
  advisories = definition[class="y">:columns].filter_map do |column, meta|
    next unless meta[class="y">:primary_key] && meta[class="y">:type] == class="s">"text"

    {severity: class="s">"low", table: table,
     advice: class="s">"#{table}.#{column} is a text primary key - fine if it's truly a UUID column type, expensive if it's a string"}
  end
  {advisories: advisories}
end

RULES = %w[check_missing_indexes check_null_discipline check_money_types check_text_primary_keys].freeze

dba = Agentic:class="y">:Agent.build { |a| a.name = class="s">"DBA" }
RULES.each { |rule| dba.add_capability(rule) }

orchestrator = Agentic:class="y">:PlanOrchestrator.new(concurrency_limit: 4)
SCHEMA.each do |table, definition|
  orchestrator.add_task(Agentic:class="y">:Task.new(
    description: table,
    agent_spec: {class="s">"name" => class="s">"DBA", class="s">"instructions" => class="s">"Review the table"},
    payload: definition
  ), agent: ->(task) {
    RULES.flat_map do |rule|
      dba.execute_capability(rule, {
        table: task.description, definition: task.payload, queries: QUERY_LOG
      })[class="y">:advisories]
    end
  })
end
result = orchestrator.execute_plan
findings = result.results.values.select(&class="y">:successful?).flat_map(&class="y">:output)

puts class="s">"SCHEMA REVIEW: #{SCHEMA.size} tables, #{QUERY_LOG.size} logged queries, " \
  class="s">"#{findings.size} advisories (#{result.status})"
puts
%w[high medium low].each do |severity|
  matching = findings.select { |f| f[class="y">:severity] == severity }
  next if matching.empty?

  puts severity.upcase
  matching.each { |f| puts class="s">"  - #{f[class="y">:advice]}" }
  puts
end