src/database/schema-optimized.sql

-- Optimized schema with source code storage for Docker optimization
CREATE TABLE IF NOT EXISTS nodes (
  node_type TEXT PRIMARY KEY,
  package_name TEXT NOT NULL,
  display_name TEXT NOT NULL,
  description TEXT,
  category TEXT,
  development_style TEXT CHECK(development_style IN ('declarative', 'programmatic')),
  is_ai_tool INTEGER DEFAULT 0,
  is_trigger INTEGER DEFAULT 0,
  is_webhook INTEGER DEFAULT 0,
  is_versioned INTEGER DEFAULT 0,
  version TEXT,
  documentation TEXT,
  properties_schema TEXT,
  operations TEXT,
  credentials_required TEXT,
  -- New columns for source code storage
  node_source_code TEXT,
  credential_source_code TEXT,
  source_location TEXT,
  source_extracted_at DATETIME,
  -- Metadata
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_package ON nodes(package_name);
CREATE INDEX IF NOT EXISTS idx_ai_tool ON nodes(is_ai_tool);
CREATE INDEX IF NOT EXISTS idx_category ON nodes(category);

-- FTS5 table is DISABLED for sql.js compatibility (it doesn't include FTS5 extension)
-- CREATE VIRTUAL TABLE IF NOT EXISTS nodes_fts USING fts5(
--   node_type,
--   display_name,
--   description,
--   documentation,
--   operations,
--   node_source_code,
--   content=nodes,
--   content_rowid=rowid
-- );

-- Triggers DISABLED
-- CREATE TRIGGER IF NOT EXISTS nodes_fts_insert AFTER INSERT ON nodes
-- ...