Spaces:
Runtime error
Runtime error
File size: 16,025 Bytes
609fb6d bc19dc4 609fb6d 44c9333 609fb6d ebd398e 46d863b 2a5899a 46d863b 2a5899a 46d863b 2a5899a 46d863b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 |
# 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
```
|