Database Class¶

Database schema setup and configuration utilities for HTM.

Overview¶

HTM::Database provides class methods for setting up the HTM database schema, managing PostgreSQL connections, and configuring TimescaleDB hypertables.

Key Features:

Schema creation and migration
TimescaleDB hypertable setup
Extension verification (TimescaleDB, pgvector, pg_trgm)
Connection configuration parsing
Automatic compression policies

Class Definition¶

class HTM::Database
  # All methods are class methods
end

Class Methods¶

`setup(db_url = nil)`¶

Set up the HTM database schema and TimescaleDB hypertables.

HTM::Database.setup(db_url = nil)

Parameters¶

Parameter	Type	Default	Description
`db_url`	String, nil	`ENV['HTM_DBURL']`	Database connection URL

Returns¶

void

Raises¶

RuntimeError - If database configuration not found
PG::Error - If database connection or schema creation fails

Side Effects¶

Connects to PostgreSQL database
Verifies required extensions (TimescaleDB, pgvector, pg_trgm)
Creates schema (tables, indexes, views)
Converts tables to hypertables
Sets up compression policies
Prints status messages to stdout

Examples¶

# Use default configuration from environment
HTM::Database.setup

# Use specific database URL
HTM::Database.setup('postgresql://user:pass@host:5432/dbname')

# Use TimescaleDB Cloud
url = 'postgresql://tsdbadmin:pass@xxx.tsdb.cloud.timescale.com:37807/tsdb?sslmode=require'
HTM::Database.setup(url)

Output¶

✓ TimescaleDB version: 2.13.0
✓ pgvector version: 0.5.1
✓ pg_trgm version: 1.6
Creating HTM schema...
✓ Schema created
✓ Created hypertable for operations_log
✓ Created hypertable for nodes
✓ Enabled compression for nodes older than 30 days
✓ HTM database schema created successfully

`parse_connection_url(url)`¶

Parse a PostgreSQL connection URL into a configuration hash.

HTM::Database.parse_connection_url(url)

Parameters¶

Parameter	Type	Description
`url`	String	PostgreSQL connection URL

Returns¶

Hash - Connection configuration
nil - If url is nil

Hash Structure¶

{
  host: "hostname",
  port: 5432,
  dbname: "database_name",
  user: "username",
  password: "password",
  sslmode: "require"  # or from URL params, default "prefer"
}

Examples¶

# Standard PostgreSQL URL
url = 'postgresql://user:pass@localhost:5432/mydb'
config = HTM::Database.parse_connection_url(url)
# => {
#   host: "localhost",
#   port: 5432,
#   dbname: "mydb",
#   user: "user",
#   password: "pass",
#   sslmode: "prefer"
# }

# With SSL mode
url = 'postgresql://user:pass@host:5432/db?sslmode=require'
config = HTM::Database.parse_connection_url(url)
# => { ..., sslmode: "require" }

# TimescaleDB Cloud URL
url = 'postgresql://tsdbadmin:secret@xxx.tsdb.cloud.timescale.com:37807/tsdb?sslmode=require'
config = HTM::Database.parse_connection_url(url)
# => {
#   host: "xxx.tsdb.cloud.timescale.com",
#   port: 37807,
#   dbname: "tsdb",
#   user: "tsdbadmin",
#   password: "secret",
#   sslmode: "require"
# }

# Nil handling
config = HTM::Database.parse_connection_url(nil)
# => nil

`parse_connection_params()`¶

Build configuration from individual environment variables.

HTM::Database.parse_connection_params()

Returns¶

Hash - Connection configuration
nil - If ENV['HTM_DBNAME'] not set

Environment Variables¶

Variable	Description	Default
`HTM_DBHOST`	Database hostname	`'cw7rxj91bm.srbbwwxn56.tsdb.cloud.timescale.com'`
`HTM_DBPORT`	Database port	`37807`
`HTM_DBNAME`	Database name	required
`HTM_DBUSER`	Database user	required
`HTM_DBPASS`	Database password	required

Examples¶

# Set environment variables
ENV['HTM_DBNAME'] = 'tsdb'
ENV['HTM_DBUSER'] = 'tsdbadmin'
ENV['HTM_DBPASS'] = 'secret'

config = HTM::Database.parse_connection_params()
# => {
#   host: "cw7rxj91bm.srbbwwxn56.tsdb.cloud.timescale.com",
#   port: 37807,
#   dbname: "tsdb",
#   user: "tsdbadmin",
#   password: "secret",
#   sslmode: "require"
# }

# Custom host and port
ENV['HTM_DBHOST'] = 'localhost'
ENV['HTM_DBPORT'] = '5432'

config = HTM::Database.parse_connection_params()
# => { host: "localhost", port: 5432, ... }

# Without HTM_DBNAME
ENV.delete('HTM_DBNAME')
config = HTM::Database.parse_connection_params()
# => nil

`default_config()`¶

Get default database configuration from environment.

HTM::Database.default_config()

Returns¶

Hash - Connection configuration
nil - If no configuration found

Priority Order¶

ENV['HTM_DBURL'] - Parse connection URL
ENV['HTM_DBNAME'] - Parse individual params
nil - No configuration available

Examples¶

# Using HTM_DBURL
ENV['HTM_DBURL'] = 'postgresql://user:pass@host/db'
config = HTM::Database.default_config
# => Parsed from URL

# Using HTM_DBNAME
ENV.delete('HTM_DBURL')
ENV['HTM_DBNAME'] = 'mydb'
ENV['HTM_DBUSER'] = 'user'
ENV['HTM_DBPASS'] = 'pass'
config = HTM::Database.default_config
# => Parsed from params

# No configuration
ENV.delete('HTM_DBURL')
ENV.delete('HTM_DBNAME')
config = HTM::Database.default_config
# => nil

# Use in HTM initialization
htm = HTM.new(db_config: HTM::Database.default_config)

Database Schema¶

For detailed database schema documentation, see:

Database Schema Documentation - Query patterns, optimization tips, and best practices
Database Tables Overview - Auto-generated table reference with ER diagram

Quick Reference¶

Table	Purpose
robots	Robot registry for multi-robot tracking
nodes	Primary memory storage with vector embeddings
tags	Hierarchical tag names for categorization
robot_nodes	Robot-to-node associations (hive mind, working memory)
node_tags	Node-to-tag associations

Required Extensions¶

Extension	Purpose
`pgvector`	Vector similarity search with HNSW indexes
`pg_trgm`	Trigram-based fuzzy text matching

Setup Process¶

1. Verify Extensions¶

# Check TimescaleDB
timescale = conn.exec("SELECT extversion FROM pg_extension WHERE extname='timescaledb'").first
# => {"extversion"=>"2.13.0"}

# Check pgvector
pgvector = conn.exec("SELECT extversion FROM pg_extension WHERE extname='vector'").first
# => {"extversion"=>"0.5.1"}

# Check pg_trgm
pg_trgm = conn.exec("SELECT extversion FROM pg_extension WHERE extname='pg_trgm'").first
# => {"extversion"=>"1.6"}

2. Run Schema¶

Reads and executes sql/schema.sql from the repository:

Creates tables
Creates indexes
Creates views
Sets up constraints

Note: CREATE EXTENSION lines are filtered out (extensions must be pre-installed).

3. Setup Hypertables¶

Converts tables to hypertables:

# operations_log
conn.exec("SELECT create_hypertable('operations_log', 'timestamp', if_not_exists => TRUE, migrate_data => TRUE)")

# nodes (with compression)
conn.exec("SELECT create_hypertable('nodes', 'created_at', if_not_exists => TRUE, migrate_data => TRUE)")
conn.exec("ALTER TABLE nodes SET (timescaledb.compress, timescaledb.compress_segmentby = 'robot_id,type')")
conn.exec("SELECT add_compression_policy('nodes', INTERVAL '30 days', if_not_exists => TRUE)")

Environment Configuration¶

TimescaleDB Cloud¶

Using URL (recommended):

# In ~/.bashrc__tiger
export HTM_DBURL='postgresql://tsdbadmin:PASSWORD@SERVICE.tsdb.cloud.timescale.com:37807/tsdb?sslmode=require'

Using individual variables:

# In ~/.bashrc__tiger
export HTM_DBHOST='xxx.tsdb.cloud.timescale.com'
export HTM_DBPORT=37807
export HTM_DBNAME='tsdb'
export HTM_DBUSER='tsdbadmin'
export HTM_DBPASS='your_password'

Local PostgreSQL¶

export HTM_DBURL='postgresql://localhost/htm_dev'

# Or with auth
export HTM_DBURL='postgresql://user:pass@localhost:5432/htm_dev'

Docker PostgreSQL¶

export HTM_DBURL='postgresql://postgres:postgres@localhost:5432/htm'

Usage Examples¶

Initial Setup¶

# First time setup
require 'htm'

HTM::Database.setup
# Creates all tables, indexes, hypertables

# Verify
config = HTM::Database.default_config
conn = PG.connect(config)
result = conn.exec("SELECT COUNT(*) FROM nodes")
conn.close

Configuration Management¶

# Get current config
config = HTM::Database.default_config

if config
  puts "Database: #{config[:dbname]}"
  puts "Host: #{config[:host]}"
  puts "Port: #{config[:port]}"
else
  puts "No database configuration found"
  puts "Please set HTM_DBURL or HTM_DBNAME environment variables"
end

# Test connection
begin
  conn = PG.connect(config)
  version = conn.exec("SELECT version()").first['version']
  puts "Connected: #{version}"
  conn.close
rescue PG::Error => e
  puts "Connection failed: #{e.message}"
end

Schema Migration¶

# Check if schema exists
config = HTM::Database.default_config
conn = PG.connect(config)

tables = conn.exec(<<~SQL).to_a
  SELECT table_name
  FROM information_schema.tables
  WHERE table_schema = 'public'
  AND table_name IN ('nodes', 'robots', 'relationships', 'tags', 'operations_log')
SQL

if tables.empty?
  puts "Schema not found, running setup..."
  HTM::Database.setup
else
  puts "Schema already exists:"
  tables.each { |t| puts "  - #{t['table_name']}" }
end

conn.close

Custom Database¶

# Use non-standard database
custom_url = 'postgresql://app:secret@db.example.com:5432/production'

HTM::Database.setup(custom_url)

# Use with HTM
config = HTM::Database.parse_connection_url(custom_url)
htm = HTM.new(db_config: config)

Troubleshooting¶

Extensions Not Available¶

⚠ Warning: TimescaleDB extension not found
⚠ Warning: pgvector extension not found

Solution: Install required extensions:

# Ubuntu/Debian
sudo apt install postgresql-15-timescaledb postgresql-15-pgvector

# macOS with Homebrew
brew install timescaledb pgvector

# Or use TimescaleDB Cloud (extensions pre-installed)

Connection Refused¶

PG::ConnectionBad: could not connect to server: Connection refused

Solution: Verify PostgreSQL is running and connection details:

# Check PostgreSQL status
pg_isready -h localhost -p 5432

# Test connection
psql -h localhost -U user -d dbname

# Verify environment
echo $HTM_DBURL

Permission Denied¶

PG::InsufficientPrivilege: ERROR:  permission denied for schema public

Solution: Grant necessary permissions:

GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;

Hypertable Already Exists¶

Note: nodes hypertable: table "nodes" is already a hypertable

This is not an error - the schema setup is idempotent. Safe to ignore.

Best Practices¶

1. Use Environment Variables¶

# Good: Use environment variables
HTM::Database.setup

# Avoid: Hardcoded credentials
HTM::Database.setup('postgresql://user:password@host/db')

2. Verify Extensions First¶

# Check extensions before setup
config = HTM::Database.default_config
conn = PG.connect(config)

required = ['timescaledb', 'vector', 'pg_trgm']
missing = required.reject do |ext|
  !conn.exec("SELECT 1 FROM pg_extension WHERE extname='#{ext}'").first
end

if missing.any?
  puts "Missing extensions: #{missing.join(', ')}"
  puts "Please install before running setup"
  exit 1
end

conn.close
HTM::Database.setup

3. Run Setup Once¶

# Run setup in a migration or initial deployment
# Not on every application start

# Bad:
def initialize
  HTM::Database.setup  # Don't do this
  @htm = HTM.new
end

# Good:
# Run once during deployment:
# rake db:setup -> HTM::Database.setup

4. Handle Missing Configuration¶

config = HTM::Database.default_config

unless config
  raise "Database not configured. Please set HTM_DBURL environment variable. " \
        "See README.md for configuration instructions."
end

Database Class¶

Overview¶

Class Definition¶

Class Methods¶

setup(db_url = nil)¶

Parameters¶

Returns¶

Raises¶

Side Effects¶

Examples¶

Output¶

parse_connection_url(url)¶

Parameters¶

Returns¶

Hash Structure¶

Examples¶

parse_connection_params()¶

Returns¶

Environment Variables¶

Examples¶

default_config()¶

Returns¶

Priority Order¶

Examples¶

Database Schema¶

Quick Reference¶

Required Extensions¶

Setup Process¶

1. Verify Extensions¶

2. Run Schema¶

3. Setup Hypertables¶

Environment Configuration¶

TimescaleDB Cloud¶

Local PostgreSQL¶

Docker PostgreSQL¶

Usage Examples¶

Initial Setup¶

Configuration Management¶

Schema Migration¶

Custom Database¶

Troubleshooting¶

Extensions Not Available¶

Connection Refused¶

Permission Denied¶

Hypertable Already Exists¶

Best Practices¶

1. Use Environment Variables¶

2. Verify Extensions First¶

3. Run Setup Once¶

4. Handle Missing Configuration¶

See Also¶

`setup(db_url = nil)`¶

`parse_connection_url(url)`¶

`parse_connection_params()`¶

`default_config()`¶