Spaces:
No application file
No application file
File size: 4,056 Bytes
93dae80 | 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 | import os
import psycopg2
from dotenv import load_dotenv
load_dotenv()
def init_db():
"""
Initialize the Neon Postgres database by creating the necessary tables for multi-workspace support:
- Installations: Stores Slack workspace installation data.
- Users: Stores Slack user information with team_id, user_id, and workspace_name.
- Preferences: Stores user-specific preferences with team_id and user_id.
- Tokens: Stores authentication tokens with team_id, user_id, and service.
Prerequisites: 'DATABASE_URL' environment variable must be set with Neon Postgres connection string.
"""
conn = psycopg2.connect(os.getenv('DATABASE_URL'))
cur = conn.cursor()
# Create Installations table for OAuth installation data
cur.execute('''
CREATE TABLE IF NOT EXISTS Installations (
workspace_id TEXT PRIMARY KEY, -- Slack workspace ID (team_id)
installation_data JSONB, -- Installation data stored as JSON
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Last update timestamp
)
''')
# Create Users table with composite primary key (team_id, user_id) and workspace_name
cur.execute('''
CREATE TABLE IF NOT EXISTS Users (
team_id TEXT, -- Slack workspace ID
user_id TEXT, -- Slack user ID
workspace_name TEXT, -- Name of the workspace
real_name TEXT, -- User's real name from Slack
email TEXT, -- User's email from Slack
name TEXT, -- User's Slack handle
is_owner BOOLEAN, -- Indicates if user is workspace owner
last_updated TIMESTAMP, -- Last time user data was updated
PRIMARY KEY (team_id, user_id) -- Composite key for uniqueness across workspaces
)
''')
# Create Preferences table with composite primary key and foreign key
cur.execute('''
CREATE TABLE IF NOT EXISTS Preferences (
team_id TEXT, -- Slack workspace ID
user_id TEXT, -- Slack user ID
zoom_config JSONB, -- Zoom configuration stored as JSON
calendar_tool TEXT, -- Selected calendar tool (e.g., google, microsoft)
updated_at TIMESTAMP, -- Last update timestamp
PRIMARY KEY (team_id, user_id), -- Composite key for uniqueness
CONSTRAINT fk_user
FOREIGN KEY(team_id, user_id) -- References Users table
REFERENCES Users(team_id, user_id)
ON DELETE CASCADE -- Delete preferences if user is deleted
)
''')
# Create Tokens table with composite primary key and foreign key
cur.execute('''
CREATE TABLE IF NOT EXISTS Tokens (
team_id TEXT, -- Slack workspace ID
user_id TEXT, -- Slack user ID
service TEXT, -- Service name (google, microsoft, zoom)
token_data JSONB, -- Token data stored as JSON
updated_at TIMESTAMP, -- Last update timestamp
PRIMARY KEY (team_id, user_id, service), -- Composite key ensures one token per service per user per workspace
CONSTRAINT fk_user
FOREIGN KEY(team_id, user_id) -- References Users table
REFERENCES Users(team_id, user_id)
ON DELETE CASCADE -- Delete tokens if user is deleted
)
''')
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
init_db()
print('Neon Postgres database initialized successfully.') |