{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1860015a",
   "metadata": {},
   "source": [
    "# Rel Agent Setup\n",
    "\n",
    "## Step 1 — Set configuration variables\n",
    "\n",
    "Replace the placeholder values below with your own, then run this block.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54b1c94e",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- ============================================================\n",
    "-- Configuration — update these values for your environment\n",
    "-- ============================================================\n",
    "\n",
    "-- Engine\n",
    "SET engine_name      = 'my_rel_agent';               -- name you choose for this engine\n",
    "SET engine_size      = 'S';                           -- compute size (S, M, L, etc.)\n",
    "\n",
    "-- Secrets location (FQNs are derived automatically)\n",
    "SET secret_db        = 'modeler_setup';               -- database for secrets and network rule\n",
    "SET secret_schema    = $secret_db || '.config';       -- schema for secrets and network rule\n",
    "SET cred_secret      = $secret_schema || '.SNOWFLAKE_CREDENTIALS';\n",
    "SET llm_secret       = $secret_schema || '.LLM_KEY';\n",
    "SET network_rule     = $secret_schema || '.modeler_network_rule';\n",
    "\n",
    "-- Credentials\n",
    "SET service_user     = '<your_service_user>';         -- Snowflake user for the Rel Agent\n",
    "\n",
    "-- Access\n",
    "SET eai_name         = 'modeler_eai';                 -- External Access Integration name\n",
    "SET service_role     = '<role_with_warehouse_access>';\n",
    "SET warehouse        = '<warehouse_name>';\n",
    "SET native_app       = 'RELATIONALAI';                -- name of the installed Native App"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "136b057f",
   "metadata": {},
   "source": [
    "## Step 2 — Enable Cortex\n",
    "\n",
    "Cortex must be enabled on your Snowflake account before the Rel Agent can use it for LLM access.\n",
    "LOOK FOR: CORTEX_ENABLED_CROSS_REGION should be set to 'ANY_REGION'.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d392bdff",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SHOW PARAMETERS LIKE 'CORTEX_ENABLED_CROSS_REGION' IN ACCOUNT;\n",
    "\n",
    "-- Enable cross-region Cortex if not already set\n",
    "ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fca9d9a4",
   "metadata": {},
   "source": [
    "## Step 3 — Create secrets\n",
    "\n",
    "The Rel Agent requires two secrets: one for Snowflake authentication and one for LLM access via Cortex.\n",
    "Replace `<your_password_or_pat>` and `<your_llm_token>` with real values before running.\n",
    "LOOK FOR: Both SNOWFLAKE_CREDENTIALS (type PASSWORD) and LLM_KEY (type GENERIC_STRING) should appear.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d27ffb5c",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Create a database and schema for your secrets (or use existing ones)\n",
    "CREATE DATABASE IF NOT EXISTS IDENTIFIER($secret_db);\n",
    "CREATE SCHEMA IF NOT EXISTS IDENTIFIER($secret_schema);\n",
    "\n",
    "-- Create the credentials secret (must be TYPE = PASSWORD)\n",
    "CREATE SECRET IF NOT EXISTS IDENTIFIER($cred_secret)\n",
    "  TYPE = PASSWORD\n",
    "  USERNAME = $service_user\n",
    "  PASSWORD = '<your_password_or_pat>';\n",
    "\n",
    "-- Create the LLM key secret for Cortex access\n",
    "CREATE SECRET IF NOT EXISTS IDENTIFIER($llm_secret)\n",
    "  TYPE = GENERIC_STRING\n",
    "  SECRET_STRING = '<your_llm_token>';\n",
    "\n",
    "SHOW SECRETS IN SCHEMA IDENTIFIER($secret_schema);\n",
    "\n",
    "-- Grant the Native App access to the secrets\n",
    "GRANT USAGE ON DATABASE IDENTIFIER($secret_db) TO APPLICATION IDENTIFIER($native_app);\n",
    "GRANT USAGE ON SCHEMA IDENTIFIER($secret_schema) TO APPLICATION IDENTIFIER($native_app);\n",
    "GRANT READ ON SECRET IDENTIFIER($cred_secret) TO APPLICATION IDENTIFIER($native_app);\n",
    "GRANT READ ON SECRET IDENTIFIER($llm_secret) TO APPLICATION IDENTIFIER($native_app);\n",
    "\n",
    "-- LOOK FOR: USAGE on the database and schema, READ on both secrets, all granted to the native app.\n",
    "SHOW GRANTS ON DATABASE IDENTIFIER($secret_db);\n",
    "SHOW GRANTS ON SCHEMA IDENTIFIER($secret_schema);\n",
    "SHOW GRANTS ON SECRET IDENTIFIER($cred_secret);\n",
    "SHOW GRANTS ON SECRET IDENTIFIER($llm_secret);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bc51227",
   "metadata": {},
   "source": [
    "## Step 4 — Configure network access\n",
    "\n",
    "The Rel Agent needs outbound network access to communicate with Snowflake's auth gateway.\n",
    "The 0.0.0.0 rule allows general egress. You can tighten this to your account's specific\n",
    "Snowflake endpoint(s) to limit access.\n",
    "\n",
    "Note: ALLOWED_NETWORK_RULES must be a literal name — session variables cannot be used here.\n",
    "If you changed `$network_rule` above, update the value in the CREATE EXTERNAL ACCESS INTEGRATION\n",
    "statement below to match.\n",
    "\n",
    "LOOK FOR: The network rule with MODE = EGRESS, the EAI with ENABLED = true,\n",
    "and USAGE privilege on both granted to the native app.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d266d861",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CREATE OR REPLACE NETWORK RULE IDENTIFIER($network_rule)\n",
    "  TYPE = HOST_PORT\n",
    "  MODE = EGRESS\n",
    "  VALUE_LIST = ('0.0.0.0')\n",
    "  COMMENT = 'Allow outbound traffic for Rel Agent service to Snowflake endpoints';\n",
    "\n",
    "-- Note: ALLOWED_NETWORK_RULES must be a literal — update if you changed $network_rule.\n",
    "CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION IDENTIFIER($eai_name)\n",
    "  ALLOWED_NETWORK_RULES = (modeler_setup.config.modeler_network_rule)\n",
    "  ENABLED = TRUE;\n",
    "\n",
    "-- Grant the Native App access\n",
    "GRANT USAGE ON NETWORK RULE IDENTIFIER($network_rule) TO APPLICATION IDENTIFIER($native_app);\n",
    "GRANT USAGE ON INTEGRATION IDENTIFIER($eai_name) TO APPLICATION IDENTIFIER($native_app);\n",
    "\n",
    "SHOW NETWORK RULES IN SCHEMA IDENTIFIER($secret_schema);\n",
    "SHOW EXTERNAL ACCESS INTEGRATIONS;\n",
    "SHOW GRANTS ON NETWORK RULE IDENTIFIER($network_rule);\n",
    "SHOW GRANTS ON INTEGRATION IDENTIFIER($eai_name);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae4caa57",
   "metadata": {},
   "source": [
    "If your service user is blocked by existing network policies, run the block below to create and assign one.\n",
    "Otherwise, continue to Step 5.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88270586",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CREATE NETWORK POLICY modeler_service_policy\n",
    "  ALLOWED_IP_LIST = ('0.0.0.0/0');\n",
    "\n",
    "ALTER USER IDENTIFIER($service_user) SET NETWORK_POLICY = modeler_service_policy;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "828859ee",
   "metadata": {},
   "source": [
    "## Step 5 — Create the persistence database\n",
    "\n",
    "The Rel Agent persists model data (entities, relations, metadata) to a Snowflake database\n",
    "named `MODELER_PERSISTENCE`. This step creates the database and grants the service role\n",
    "ownership so the app can manage schemas and tables within it.\n",
    "\n",
    "LOOK FOR: The service role should appear as the owner of the MODELER_PERSISTENCE database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a9ba464",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Create the persistence database for model storage\n",
    "CREATE DATABASE IF NOT EXISTS MODELER_PERSISTENCE;\n",
    "\n",
    "-- Grant the service role ownership so it can manage schemas and tables\n",
    "GRANT OWNERSHIP ON DATABASE MODELER_PERSISTENCE TO ROLE IDENTIFIER($service_role)\n",
    "  REVOKE CURRENT GRANTS;\n",
    "\n",
    "SHOW GRANTS ON DATABASE MODELER_PERSISTENCE;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a117df8",
   "metadata": {},
   "source": [
    "## Step 6 — Verify role access and launch the engine\n",
    "\n",
    "Before launching, confirm that `$service_role` exists, is granted to your service user,\n",
    "has warehouse access, and has a RAI application role. This is the most common source of setup failures.\n",
    "LOOK FOR: `$service_role` should appear in each result set.\n",
    "If the role is missing from any check, run the corresponding GRANT shown in the comments.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "761e722c",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Does the role exist?\n",
    "SHOW ROLES;\n",
    "SELECT \"name\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))\n",
    "WHERE \"name\" = UPPER($service_role);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edb46f28",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Is it granted to the service user?\n",
    "SHOW GRANTS TO USER IDENTIFIER($service_user);\n",
    "SELECT \"role\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))\n",
    "WHERE \"role\" = UPPER($service_role);\n",
    "-- If not: GRANT ROLE IDENTIFIER($service_role) TO USER IDENTIFIER($service_user);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72905b29",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Does the role have USAGE on the warehouse?\n",
    "SHOW GRANTS TO ROLE IDENTIFIER($service_role);\n",
    "SELECT \"privilege\", \"name\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))\n",
    "WHERE \"privilege\" = 'USAGE' AND \"name\" = UPPER($warehouse);\n",
    "-- If not: GRANT USAGE ON WAREHOUSE IDENTIFIER($warehouse) TO ROLE IDENTIFIER($service_role);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6490c5ab",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Does the role have a RAI application role (e.g. RAI_USER or RAI_DEVELOPER)?\n",
    "SHOW GRANTS TO ROLE IDENTIFIER($service_role);\n",
    "SELECT \"privilege\", \"granted_on\", \"grantee_name\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))\n",
    "WHERE \"privilege\" = 'USAGE' AND \"granted_on\" = 'APPLICATION_ROLE' and \"grantee_name\" = UPPER(($service_role));\n",
    "-- If not: GRANT APPLICATION ROLE RELATIONALAI.<rai_role> TO ROLE IDENTIFIER($service_role);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f4ab44b",
   "metadata": {},
   "source": [
    "Once the checks above pass, create the engine.\n",
    "LOOK FOR: STATUS = RUNNING. If GONE or FAILED, see the Troubleshooting section of the setup guide.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e264d553",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CALL RELATIONALAI.api.create_reasoner_async(\n",
    "    'modeler',\n",
    "    $engine_name,\n",
    "    $engine_size,\n",
    "    PARSE_JSON(\n",
    "      '{\"settings\":{\"modeler\":{' ||\n",
    "        '\"snowflake_credentials_secret\":\"' || $cred_secret    || '\",' ||\n",
    "        '\"llm_key_secret\":\"'               || $llm_secret     || '\",' ||\n",
    "        '\"snowflake_role\":\"'               || $service_role   || '\",' ||\n",
    "        '\"warehouse_name\":\"'               || $warehouse      || '\",' ||\n",
    "        '\"external_access_integrations\":[\"' || $eai_name      || '\"]' ||\n",
    "      '}}}'\n",
    "    )\n",
    ");\n",
    "\n",
    "-- LOOK FOR: STATUS = RUNNING. If GONE or FAILED, see \"Getting service logs\" in the Troubleshooting section.\n",
    "CALL RELATIONALAI.api.get_reasoner('modeler', $engine_name);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a9a9fa3",
   "metadata": {},
   "source": [
    "## Step 7 — Access the Rel Agent UI\n",
    "\n",
    "Once the engine is running, retrieve its ingress endpoint URL and open it in your browser.\n",
    "The SPCS ingress authenticates you automatically via your Snowflake account credentials.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f8e44db",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CALL RELATIONALAI.api.modeler_endpoints($engine_name);"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}