sql/readme.md

# Agentic Employment Data Structure

## Overview
Agentic Employment Data Structure utilizes Supabase to manage and interact with SQL databases. The repository includes several SQL scripts for setting up and maintaining various components of the system, such as user management, agent interactions, analytics reporting, command control, documentation, governance, and system settings.

## Database Structure

### Tables

#### users
The `users` table is designed to store user information with the following columns:
- `id`: A UUID that serves as the primary key and is generated by default.
- `email`: A unique email address for each user.
- `password_hash`: A hashed version of the user's password.
- `role`: The role of the user (e.g., admin, user).
- `tenant_id`: A UUID that identifies the tenant to which the user belongs.
- `created_at`: A timestamp indicating when the user was created, with a default value of the current timestamp.

#### agents
The `agents` table is designed to store agent information with the following columns:
- `id`: A serial primary key.
- `user_id`: A UUID that references the `id` in the `users` table.
- `tenant_id`: A UUID that identifies the tenant to which the agent belongs.
- `name`: The name of the agent.
- `type`: The type of the agent (e.g., Conversational, Analytical).
- `status`: The current status of the agent (e.g., Active, Idle, Failed).
- `description`: A text description of the agent.
- `avatar_url`: A URL to the agent's avatar image.
- `llm_base`: The base language model used by the agent.
- `prompt`: The prompt used by the agent.
- `knowledge_base_url`: A URL to the agent's knowledge base.
- `learning_rate`: The learning rate for the agent's training.
- `exploration_rate`: The exploration rate for the agent's training.
- `training_iterations`: The number of training iterations.
- `batch_size`: The batch size for training.
- `max_tokens`: The maximum number of tokens the agent can generate.
- `temperature`: The temperature setting for the agent's language model.
- `top_p`: The top-p sampling parameter.
- `frequency_penalty`: The frequency penalty parameter.
- `presence_penalty`: The presence penalty parameter.
- `stop_sequences`: The stop sequences for the agent.
- `created_at`: A timestamp indicating when the agent was created, with a default value of the current timestamp.

#### agent_data
The `agent_data` table contains information related to agents with the following columns:
- `id`: A serial primary key.
- `name`: The name of the agent.
- `email`: The email address of the agent.
- `role`: The role of the agent.
- `created_at`: A timestamp indicating when the agent was created.

#### agent_interaction
The `agent_interaction` table records interactions between users and agents with the following columns:
- `id`: A serial primary key.
- `user_id`: An integer referencing the `id` in the `users` table.
- `agent_id`: An integer referencing the `id` in the `agents` table.
- `interaction_time`: A timestamp indicating when the interaction occurred.
- `details`: A text field containing details of the interaction.

#### analytics_reporting
The `analytics_reporting` table stores data used for generating analytics reports with the following columns:
- `id`: A serial primary key.
- `report_name`: A varchar for the name of the report.
- `generated_at`: A timestamp indicating when the report was generated.
- `data`: A JSONB field containing the report data.

#### chat_history_agent_details
The `chat_history_agent_details` table is designed to log the details of chat histories involving agents with the following columns:
- `id`: A serial primary key.
- `chat_id`: An integer referencing the `id` in the `chat` table.
- `agent_id`: An integer referencing the `id` in the `agents` table.
- `message`: A text field containing the chat message.
- `timestamp`: A timestamp indicating when the message was sent.

#### command_control
The `command_control` table manages command and control data for the system with the following columns:
- `id`: A serial primary key.
- `command`: A varchar for the command.
- `description`: A text field describing the command.
- `executed_at`: A timestamp indicating when the command was executed.

#### documentation
The `documentation` table holds documentation related to various system components with the following columns:
- `id`: A serial primary key.
- `doc_title`: A varchar for the title of the document.
- `content`: A text field containing the document content.
- `created_at`: A timestamp indicating when the document was created.

#### governance
The `governance` table contains governance-related information and policies with the following columns:
- `id`: A serial primary key.
- `policy_name`: A varchar for the name of the policy.
- `description`: A text field describing the policy.
- `enacted_at`: A timestamp indicating when the policy was enacted.

#### system_settings
The `system_settings` table stores system configuration settings with the following columns:
- `id`: A serial primary key.
- `setting_name`: A varchar for the name of the setting.
- `value`: A varchar for the setting value.
- `updated_at`: A timestamp indicating when the setting was last updated.

#### users_agents
The `users_agents` table manages user and agent information with the following columns:
- `id`: A serial primary key.
- `user_id`: An integer referencing the `id` in the `users` table.
- `agent_id`: An integer referencing the `id` in the `agents` table.
- `assigned_at`: A timestamp indicating when the user was assigned to the agent.

## Security and Policies

### Authentication
- Ensure all database connections are authenticated using secure tokens or credentials provided by Supabase.
- Use environment variables to manage sensitive information.

### Authorization
- Implement role-based access control (RBAC) to restrict access to specific tables and actions based on user roles.
- Define policies in Supabase to manage read, write, and delete permissions.

### Data Privacy
- Encrypt sensitive data both at rest and in transit.
- Regularly audit data access logs to detect and respond to unauthorized access.

### Backup and Recovery
- Schedule regular backups of the database to prevent data loss.
- Test recovery procedures periodically to ensure data integrity and availability.

## Row-Level Security (RLS)

Row-Level Security is enabled for all tables to ensure data isolation between tenants:
```sql
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE agents ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_interaction ENABLE ROW LEVEL SECURITY;
ALTER TABLE analytics_reporting ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_history_agent_details ENABLE ROW LEVEL SECURITY;
ALTER TABLE command_control ENABLE ROW LEVEL SECURITY;
ALTER TABLE documentation ENABLE ROW LEVEL SECURITY;
ALTER TABLE governance ENABLE ROW LEVEL SECURITY;
ALTER TABLE system_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE users_agents ENABLE ROW LEVEL SECURITY;
```

### RLS Policies
Several RLS policies are created to enforce tenant isolation and real-time access:
```sql
CREATE POLICY "tenant_isolation" ON users FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON agents FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON agent_data FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON agent_interaction FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON analytics_reporting FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON chat_history_agent_details FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON command_control FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON documentation FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON governance FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON system_settings FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY "tenant_isolation" ON users_agents FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY "Enable real-time" ON users FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON agents FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON agent_data FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON agent_interaction FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON analytics_reporting FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON chat_history_agent_details FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON command_control FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON documentation FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON governance FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON system_settings FOR SELECT USING (true);
CREATE POLICY "Enable real-time" ON users_agents FOR SELECT USING (true);

CREATE POLICY "user_access" ON users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "agent_access" ON agents FOR SELECT USING (auth.uid() = user_id);
```

### Indexes
Indexes are created on the `tenant_id` columns to optimize queries:
```sql
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_agents_tenant_id ON agents(tenant_id);
CREATE INDEX idx_agent_data_tenant_id ON agent_data(tenant_id);
CREATE INDEX idx_agent_interaction_tenant_id ON agent_interaction(tenant_id);
CREATE INDEX idx_analytics_reporting_tenant_id ON analytics_reporting(tenant_id);
CREATE INDEX idx_chat_history_agent_details_tenant_id ON chat_history_agent_details(tenant_id);
CREATE INDEX idx_command_control_tenant_id ON command_control(tenant_id);
CREATE INDEX idx_documentation_tenant_id ON documentation(tenant_id);
CREATE INDEX idx_governance_tenant_id ON governance(tenant_id);
CREATE INDEX idx_system_settings_tenant_id ON system_settings(tenant_id);
CREATE INDEX idx_users_agents_tenant_id ON users_agents(tenant_id);
```

## How to Use

### Setting Up the Database
1. Clone the repository:
    ```sh
    git clone https://github.com/ruvnet/agentic-employment.git
    cd agentic-employment
    ```

2. Configure your Supabase environment variables in a `.env` file:
    ```env
    SUPABASE_URL=your-supabase-url
    SUPABASE_ANON_KEY=your-anon-key
    SUPABASE_SERVICE_KEY=your-service-key
    ```

3. Run the SQL scripts to set up the database structure:

```sh
psql -h your-supabase-url -U your-username -d your-database -f agent_data.sql
psql -h your-supabase-url -U your-username -d your-database -f agent_interaction.sql
psql -h your-supabase-url -U your-username -d your-database -f analytics_reporting.sql
psql -h your-supabase-url -U your-username -d your-database -f chat_history_agent_details.sql
psql -h your-supabase-url -U your-username -d your-database -f command_control.sql
psql -h your-supabase-url -U your-username -d your-database -f documentation.sql
psql -h your-supabase-url -U your-username -d your-database -f governance.sql
psql -h your-supabase-url -U your-username -d your-database -f system_settings.sql
psql -h your-supabase-url -U your-username -d your-database -f users_agents.sql
```

# Setting Up Supabase and Installing SQL Files

## Overview
This guide will walk you through setting up Supabase, configuring your environment, and running a bash script to install various SQL files into your Supabase database.

## Prerequisites
- A Supabase account and project
- `psql` command-line tool installed
- A terminal or command-line interface

## Step 1: Set Up Supabase

### 1.1 Create a Supabase Account
1. Go to [Supabase](https://supabase.io/).
2. Sign up for a free account and log in.

### 1.2 Create a New Project
1. Once logged in, click on "New Project".
2. Fill in the project details:
   - **Name**: Choose a name for your project.
   - **Database Password**: Set a strong password.
   - **Region**: Select a region close to you.
3. Click "Create new project".

### 1.3 Get Database Connection Details
1. After the project is created, navigate to the "Settings" > "Database" tab.
2. Note down the following details:
   - **Database URL**
   - **Database name**
   - **Database user**
   - **Database password**

## Step 2: Install `psql`

### 2.1 Install on macOS
```sh
brew install postgresql
```

### 2.2 Install on Linux (Debian-based)
```sh
sudo apt update
sudo apt install postgresql-client
```

### 2.3 Install on Windows
Download and install PostgreSQL from the [official website](https://www.postgresql.org/download/), ensuring `psql` is included in the installation.

## Step 3: Prepare the SQL Files

Ensure that you have the following SQL files in your project directory:
- `agent_data.sql`
- `agent_interaction.sql`
- `analytics_reporting.sql`
- `chat_history_agent_details.sql`
- `command_control.sql`
- `documentation.sql`
- `governance.sql`
- `system_settings.sql`
- `users_agents.sql`

## Step 4: Create the `.env` File

Create a `.env` file in your project directory and add the following content, replacing the placeholders with your actual Supabase details:

```env
SUPABASE_URL=your-supabase-url
SUPABASE_DB=your-database-name
SUPABASE_USER=your-username
SUPABASE_PASSWORD=your-password
```

## Step 5: Create the Installation Script

Create a file named `install_sql.sh` in your project directory and add the following content:

```bash
#!/bin/bash

# Check if psql is installed
if ! command -v psql &> /dev/null; then
  echo "psql could not be found. Installing it now..."

  # Check OS and install psql accordingly
  if [[ "$OSTYPE" == "linux-gnu"* ]]; then
    sudo apt update
    sudo apt install postgresql-client -y
  elif [[ "$OSTYPE" == "darwin"* ]]; then
    brew install postgresql
  elif [[ "$OSTYPE" == "msys" ]]; then
    echo "Please install psql manually from: https://www.postgresql.org/download/windows/"
    exit 1
  else
    echo "Unsupported OS. Please install psql manually."
    exit 1
  fi
fi

# Load environment variables
if [ -f ".env" ]; then
  source .env
else
  echo ".env file not found. Please create one with your Supabase configuration."
  exit 1
fi

# Prompt for environment variables if not set
if [ -z "$SUPABASE_URL" ]; then
  read -p "Enter your Supabase URL: " SUPABASE_URL
fi

if [ -z "$SUPABASE_DB" ]; then
  read -p "Enter your Supabase database name: " SUPABASE_DB
fi

if [ -z "$SUPABASE_USER" ]; then
  read -p "Enter your Supabase username: " SUPABASE_USER
fi

if [ -z "$SUPABASE_PASSWORD" ]; then
  read -s -p "Enter your Supabase password: " SUPABASE_PASSWORD
  echo
fi

# Function to execute a SQL file
execute_sql() {
  local file=$1
  echo "Executing $file..."
  PGPASSWORD=$SUPABASE_PASSWORD psql -h $SUPABASE_URL -d $SUPABASE_DB -U $SUPABASE_USER -f $file
  if [ $? -eq 0 ]; then
    echo "$file executed successfully."
  else
    echo "Error executing $file."
    exit 1
  fi
}

# List of SQL files to be executed
sql_files=(
  "agent_data.sql"
  "agent_interaction.sql"
  "analytics_reporting.sql"
  "chat_history_agent_details.sql"
  "command_control.sql"
  "documentation.sql"
  "governance.sql"
  "system_settings.sql"
  "users_agents.sql"
)

# Execute each SQL file
for sql_file in "${sql_files[@]}"; do
  if [ -f "$sql_file" ]; then
    execute_sql "$sql_file"
  else
    echo "File $sql_file does not exist."
    exit 1
  fi
done

echo "All SQL files executed successfully."
```

## Step 6: Run the Installation Script

### 6.1 Make the Script Executable
Run the following command to make the script executable:
```sh
chmod +x install_sql.sh
```

### 6.2 Execute the Script
Run the script to install the SQL files into your Supabase database:
```sh
./install_sql.sh
```