379 lines
12 KiB
PL/PgSQL
379 lines
12 KiB
PL/PgSQL
-- =====================================================
|
|
-- BotKonzept - Installer JSON API Extension
|
|
-- =====================================================
|
|
-- Extends the database schema to store and expose installer JSON data
|
|
-- safely to frontend clients (without secrets)
|
|
|
|
-- =====================================================
|
|
-- Step 1: Add installer_json column to instances table
|
|
-- =====================================================
|
|
|
|
-- Add column to store the complete installer JSON
|
|
ALTER TABLE instances
|
|
ADD COLUMN IF NOT EXISTS installer_json JSONB DEFAULT '{}'::jsonb;
|
|
|
|
-- Create index for faster JSON queries
|
|
CREATE INDEX IF NOT EXISTS idx_instances_installer_json ON instances USING gin(installer_json);
|
|
|
|
-- Add comment
|
|
COMMENT ON COLUMN instances.installer_json IS 'Complete installer JSON output from install.sh (includes secrets - use api.instance_config view for safe access)';
|
|
|
|
-- =====================================================
|
|
-- Step 2: Create safe API view (NON-SECRET data only)
|
|
-- =====================================================
|
|
|
|
-- Create API schema if it doesn't exist
|
|
CREATE SCHEMA IF NOT EXISTS api;
|
|
|
|
-- Grant usage on api schema
|
|
GRANT USAGE ON SCHEMA api TO anon, authenticated, service_role;
|
|
|
|
-- Create view that exposes only safe (non-secret) installer data
|
|
CREATE OR REPLACE VIEW api.instance_config AS
|
|
SELECT
|
|
i.id,
|
|
i.customer_id,
|
|
i.lxc_id as ctid,
|
|
i.hostname,
|
|
i.fqdn,
|
|
i.ip,
|
|
i.vlan,
|
|
i.status,
|
|
i.created_at,
|
|
-- Extract safe URLs from installer_json
|
|
jsonb_build_object(
|
|
'n8n_internal', i.installer_json->'urls'->>'n8n_internal',
|
|
'n8n_external', i.installer_json->'urls'->>'n8n_external',
|
|
'postgrest', i.installer_json->'urls'->>'postgrest',
|
|
'chat_webhook', i.installer_json->'urls'->>'chat_webhook',
|
|
'chat_internal', i.installer_json->'urls'->>'chat_internal',
|
|
'upload_form', i.installer_json->'urls'->>'upload_form',
|
|
'upload_form_internal', i.installer_json->'urls'->>'upload_form_internal'
|
|
) as urls,
|
|
-- Extract safe Supabase data (NO service_role_key, NO jwt_secret)
|
|
jsonb_build_object(
|
|
'url_external', i.installer_json->'supabase'->>'url_external',
|
|
'anon_key', i.installer_json->'supabase'->>'anon_key'
|
|
) as supabase,
|
|
-- Extract Ollama URL (safe)
|
|
jsonb_build_object(
|
|
'url', i.installer_json->'ollama'->>'url',
|
|
'model', i.installer_json->'ollama'->>'model',
|
|
'embedding_model', i.installer_json->'ollama'->>'embedding_model'
|
|
) as ollama,
|
|
-- Customer info (joined)
|
|
c.email as customer_email,
|
|
c.first_name,
|
|
c.last_name,
|
|
c.company,
|
|
c.status as customer_status
|
|
FROM instances i
|
|
JOIN customers c ON i.customer_id = c.id
|
|
WHERE i.status = 'active' AND i.deleted_at IS NULL;
|
|
|
|
-- Add comment
|
|
COMMENT ON VIEW api.instance_config IS 'Safe API view for instance configuration - exposes only non-secret data from installer JSON';
|
|
|
|
-- =====================================================
|
|
-- Step 3: Row Level Security (RLS) for API view
|
|
-- =====================================================
|
|
|
|
-- Enable RLS on the view (inherited from base table)
|
|
-- Customers can only see their own instance config
|
|
|
|
-- Policy: Allow customers to see their own instance config
|
|
CREATE POLICY instance_config_select_own ON instances
|
|
FOR SELECT
|
|
USING (
|
|
-- Allow if customer_id matches authenticated user
|
|
customer_id::text = auth.uid()::text
|
|
OR
|
|
-- Allow service_role to see all (for n8n workflows)
|
|
auth.jwt()->>'role' = 'service_role'
|
|
);
|
|
|
|
-- Grant SELECT on api.instance_config view
|
|
GRANT SELECT ON api.instance_config TO anon, authenticated, service_role;
|
|
|
|
-- =====================================================
|
|
-- Step 4: Create function to get config by customer email
|
|
-- =====================================================
|
|
|
|
-- Function to get instance config by customer email (for public access)
|
|
CREATE OR REPLACE FUNCTION api.get_instance_config_by_email(customer_email_param TEXT)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
customer_id UUID,
|
|
ctid BIGINT,
|
|
hostname VARCHAR,
|
|
fqdn VARCHAR,
|
|
ip VARCHAR,
|
|
vlan INTEGER,
|
|
status VARCHAR,
|
|
created_at TIMESTAMPTZ,
|
|
urls JSONB,
|
|
supabase JSONB,
|
|
ollama JSONB,
|
|
customer_email VARCHAR,
|
|
first_name VARCHAR,
|
|
last_name VARCHAR,
|
|
company VARCHAR,
|
|
customer_status VARCHAR
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
ic.id,
|
|
ic.customer_id,
|
|
ic.ctid,
|
|
ic.hostname,
|
|
ic.fqdn,
|
|
ic.ip,
|
|
ic.vlan,
|
|
ic.status,
|
|
ic.created_at,
|
|
ic.urls,
|
|
ic.supabase,
|
|
ic.ollama,
|
|
ic.customer_email,
|
|
ic.first_name,
|
|
ic.last_name,
|
|
ic.company,
|
|
ic.customer_status
|
|
FROM api.instance_config ic
|
|
WHERE ic.customer_email = customer_email_param
|
|
LIMIT 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permission
|
|
GRANT EXECUTE ON FUNCTION api.get_instance_config_by_email(TEXT) TO anon, authenticated, service_role;
|
|
|
|
-- Add comment
|
|
COMMENT ON FUNCTION api.get_instance_config_by_email IS 'Get instance configuration by customer email - returns only non-secret data';
|
|
|
|
-- =====================================================
|
|
-- Step 5: Create function to get config by CTID
|
|
-- =====================================================
|
|
|
|
-- Function to get instance config by CTID (for internal use)
|
|
CREATE OR REPLACE FUNCTION api.get_instance_config_by_ctid(ctid_param BIGINT)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
customer_id UUID,
|
|
ctid BIGINT,
|
|
hostname VARCHAR,
|
|
fqdn VARCHAR,
|
|
ip VARCHAR,
|
|
vlan INTEGER,
|
|
status VARCHAR,
|
|
created_at TIMESTAMPTZ,
|
|
urls JSONB,
|
|
supabase JSONB,
|
|
ollama JSONB,
|
|
customer_email VARCHAR,
|
|
first_name VARCHAR,
|
|
last_name VARCHAR,
|
|
company VARCHAR,
|
|
customer_status VARCHAR
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
ic.id,
|
|
ic.customer_id,
|
|
ic.ctid,
|
|
ic.hostname,
|
|
ic.fqdn,
|
|
ic.ip,
|
|
ic.vlan,
|
|
ic.status,
|
|
ic.created_at,
|
|
ic.urls,
|
|
ic.supabase,
|
|
ic.ollama,
|
|
ic.customer_email,
|
|
ic.first_name,
|
|
ic.last_name,
|
|
ic.company,
|
|
ic.customer_status
|
|
FROM api.instance_config ic
|
|
WHERE ic.ctid = ctid_param
|
|
LIMIT 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permission
|
|
GRANT EXECUTE ON FUNCTION api.get_instance_config_by_ctid(BIGINT) TO service_role;
|
|
|
|
-- Add comment
|
|
COMMENT ON FUNCTION api.get_instance_config_by_ctid IS 'Get instance configuration by CTID - for internal use only';
|
|
|
|
-- =====================================================
|
|
-- Step 6: Create public config endpoint (no auth required)
|
|
-- =====================================================
|
|
|
|
-- Function to get public config (for website registration form)
|
|
-- Returns only the registration webhook URL
|
|
CREATE OR REPLACE FUNCTION api.get_public_config()
|
|
RETURNS TABLE (
|
|
registration_webhook_url TEXT,
|
|
api_base_url TEXT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
'https://api.botkonzept.de/webhook/botkonzept-registration'::TEXT as registration_webhook_url,
|
|
'https://api.botkonzept.de'::TEXT as api_base_url;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permission to everyone
|
|
GRANT EXECUTE ON FUNCTION api.get_public_config() TO anon, authenticated, service_role;
|
|
|
|
-- Add comment
|
|
COMMENT ON FUNCTION api.get_public_config IS 'Get public configuration for website (registration webhook URL)';
|
|
|
|
-- =====================================================
|
|
-- Step 7: Update install.sh integration
|
|
-- =====================================================
|
|
|
|
-- This SQL will be executed after instance creation
|
|
-- The install.sh script should call this function to store the installer JSON
|
|
|
|
CREATE OR REPLACE FUNCTION api.store_installer_json(
|
|
customer_email_param TEXT,
|
|
lxc_id_param BIGINT,
|
|
installer_json_param JSONB
|
|
)
|
|
RETURNS JSONB AS $$
|
|
DECLARE
|
|
instance_record RECORD;
|
|
result JSONB;
|
|
BEGIN
|
|
-- Find the instance by customer email and lxc_id
|
|
SELECT i.id, i.customer_id INTO instance_record
|
|
FROM instances i
|
|
JOIN customers c ON i.customer_id = c.id
|
|
WHERE c.email = customer_email_param
|
|
AND i.lxc_id = lxc_id_param
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN jsonb_build_object(
|
|
'success', false,
|
|
'error', 'Instance not found for customer email and LXC ID'
|
|
);
|
|
END IF;
|
|
|
|
-- Update the installer_json column
|
|
UPDATE instances
|
|
SET installer_json = installer_json_param,
|
|
updated_at = NOW()
|
|
WHERE id = instance_record.id;
|
|
|
|
-- Return success
|
|
result := jsonb_build_object(
|
|
'success', true,
|
|
'instance_id', instance_record.id,
|
|
'customer_id', instance_record.customer_id,
|
|
'message', 'Installer JSON stored successfully'
|
|
);
|
|
|
|
RETURN result;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permission to service_role only
|
|
GRANT EXECUTE ON FUNCTION api.store_installer_json(TEXT, BIGINT, JSONB) TO service_role;
|
|
|
|
-- Add comment
|
|
COMMENT ON FUNCTION api.store_installer_json IS 'Store installer JSON after instance creation - called by install.sh via n8n workflow';
|
|
|
|
-- =====================================================
|
|
-- Step 8: Create audit log entry for API access
|
|
-- =====================================================
|
|
|
|
-- Function to log API access
|
|
CREATE OR REPLACE FUNCTION api.log_config_access(
|
|
customer_id_param UUID,
|
|
access_type TEXT,
|
|
ip_address_param INET DEFAULT NULL
|
|
)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
INSERT INTO audit_log (
|
|
customer_id,
|
|
action,
|
|
entity_type,
|
|
performed_by,
|
|
ip_address,
|
|
metadata
|
|
) VALUES (
|
|
customer_id_param,
|
|
'api_config_access',
|
|
'instance_config',
|
|
'api_user',
|
|
ip_address_param,
|
|
jsonb_build_object('access_type', access_type)
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permission
|
|
GRANT EXECUTE ON FUNCTION api.log_config_access(UUID, TEXT, INET) TO anon, authenticated, service_role;
|
|
|
|
-- =====================================================
|
|
-- Step 9: Example queries for testing
|
|
-- =====================================================
|
|
|
|
-- Example 1: Get instance config by customer email
|
|
-- SELECT * FROM api.get_instance_config_by_email('max@beispiel.de');
|
|
|
|
-- Example 2: Get instance config by CTID
|
|
-- SELECT * FROM api.get_instance_config_by_ctid(769697636);
|
|
|
|
-- Example 3: Get public config
|
|
-- SELECT * FROM api.get_public_config();
|
|
|
|
-- Example 4: Store installer JSON (called by install.sh)
|
|
-- SELECT api.store_installer_json(
|
|
-- 'max@beispiel.de',
|
|
-- 769697636,
|
|
-- '{"ctid": 769697636, "urls": {...}, ...}'::jsonb
|
|
-- );
|
|
|
|
-- =====================================================
|
|
-- Step 10: PostgREST API Routes
|
|
-- =====================================================
|
|
|
|
-- After running this SQL, the following PostgREST routes will be available:
|
|
--
|
|
-- 1. GET /api/instance_config
|
|
-- - Returns all instance configs (filtered by RLS)
|
|
-- - Requires authentication
|
|
--
|
|
-- 2. POST /rpc/get_instance_config_by_email
|
|
-- - Body: {"customer_email_param": "max@beispiel.de"}
|
|
-- - Returns instance config for specific customer
|
|
-- - No authentication required (public)
|
|
--
|
|
-- 3. POST /rpc/get_instance_config_by_ctid
|
|
-- - Body: {"ctid_param": 769697636}
|
|
-- - Returns instance config for specific CTID
|
|
-- - Requires service_role authentication
|
|
--
|
|
-- 4. POST /rpc/get_public_config
|
|
-- - Body: {}
|
|
-- - Returns public configuration (registration webhook URL)
|
|
-- - No authentication required (public)
|
|
--
|
|
-- 5. POST /rpc/store_installer_json
|
|
-- - Body: {"customer_email_param": "...", "lxc_id_param": 123, "installer_json_param": {...}}
|
|
-- - Stores installer JSON after instance creation
|
|
-- - Requires service_role authentication
|
|
|
|
-- =====================================================
|
|
-- End of API Extension
|
|
-- =====================================================
|