rckrdmrd
68d3c54023
Documentation: - Update SAAS-006-ai-integration.md with full implementation details - Update _MAP.md with AI schema (30 tables, 11 schemas) - Update PROJECT-STATUS.md (67% completion) Database fixes: - Add update_updated_at_column() function to 03-functions.sql - Add trigger creation for ai.configs in 03-functions.sql - Fix partial index in 02-ai-usage.sql (remove CURRENT_DATE) - Add AI schema grants to create-database.sh - Add AI to SCHEMA_ORDER array Validated: Database recreation successful with all AI objects 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
69 lines
2.7 KiB
SQL
69 lines
2.7 KiB
SQL
-- ============================================
|
|
-- AI Configurations Table
|
|
-- Per-tenant AI settings and defaults
|
|
-- ============================================
|
|
|
|
CREATE TABLE ai.configs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Provider settings
|
|
provider ai.ai_provider NOT NULL DEFAULT 'openrouter',
|
|
default_model VARCHAR(100) NOT NULL DEFAULT 'anthropic/claude-3-haiku',
|
|
fallback_model VARCHAR(100) DEFAULT 'openai/gpt-3.5-turbo',
|
|
|
|
-- Generation parameters
|
|
temperature NUMERIC(3,2) NOT NULL DEFAULT 0.7 CHECK (temperature >= 0 AND temperature <= 2),
|
|
max_tokens INTEGER NOT NULL DEFAULT 2048 CHECK (max_tokens > 0 AND max_tokens <= 32000),
|
|
top_p NUMERIC(3,2) DEFAULT 1.0 CHECK (top_p >= 0 AND top_p <= 1),
|
|
frequency_penalty NUMERIC(3,2) DEFAULT 0.0 CHECK (frequency_penalty >= -2 AND frequency_penalty <= 2),
|
|
presence_penalty NUMERIC(3,2) DEFAULT 0.0 CHECK (presence_penalty >= -2 AND presence_penalty <= 2),
|
|
|
|
-- System prompt
|
|
system_prompt TEXT,
|
|
|
|
-- Rate limits (override plan defaults)
|
|
rate_limit_requests_per_minute INTEGER,
|
|
rate_limit_tokens_per_minute INTEGER,
|
|
rate_limit_tokens_per_month INTEGER,
|
|
|
|
-- Feature flags
|
|
is_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
allow_custom_prompts BOOLEAN NOT NULL DEFAULT true,
|
|
log_conversations BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
-- Additional settings as JSON
|
|
settings JSONB DEFAULT '{}',
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Ensure one config per tenant
|
|
CONSTRAINT uq_ai_configs_tenant UNIQUE (tenant_id)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_ai_configs_tenant ON ai.configs(tenant_id);
|
|
CREATE INDEX idx_ai_configs_provider ON ai.configs(provider);
|
|
|
|
-- RLS
|
|
ALTER TABLE ai.configs ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY ai_configs_tenant_isolation ON ai.configs
|
|
FOR ALL
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
-- Trigger for updated_at (uses function from 03-functions.sql)
|
|
-- Note: This trigger is created in 03-functions.sql after the function exists
|
|
-- CREATE TRIGGER update_ai_configs_updated_at
|
|
-- BEFORE UPDATE ON ai.configs
|
|
-- FOR EACH ROW
|
|
-- EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE ai.configs IS 'AI configuration per tenant';
|
|
COMMENT ON COLUMN ai.configs.default_model IS 'Default model to use (e.g., anthropic/claude-3-haiku)';
|
|
COMMENT ON COLUMN ai.configs.system_prompt IS 'Default system prompt for all AI interactions';
|
|
COMMENT ON COLUMN ai.configs.settings IS 'Additional provider-specific settings as JSON';
|