malepati/custom_template_working

1# Database Connection & Migration Plan2 3## 1. Goal4Migrate Accusaga Slides Presentation to use a shared, multi-tenant PostgreSQL database. We will integrate with the **existing** Users and Organizations schema provided, rather than creating new tables for them.5 6## 2. Infrastructure & Credentials7 8### **Switch to PostgreSQL**9- **Configuration**: The application will connect using `DATABASE_URL` to the existing PostgreSQL instance.10 11### **[New] Secret Manager**12- Create `servers/fastapi/utils/secret_manager.py` to securely fetch `DATABASE_URL` and other secrets.13- This ensures we don't hardcode credentials and can retrieve them from environment variables or a secret store.14 15## 3. Database Schema Integration16 17We will map our application models to the **existing tables** in your database.18 19### **Existing Tables (ReadOnly/Foreign Keys)**20We will define SQLModel representations for these existing tables to facilitate Foreign Key relationships, but we will **not** modify their schema (creation/deletion handled externally).21 221.  **`users` Table**23    - `id` (UUID, Primary Key)24    - `organization_id` (UUID, Foreign Key to `organizations.id`)25    - `role` (Enum: `UserRole`)26    - *Mapping Strategy*: Use a read-only SQLModel or simply reference the `users` table name in Foreign Keys.27 282.  **`organizations` Table**29    - `id` (UUID, Primary Key)30    - `name` (String)31 32### **Modified Application Tables**33We will update our specific tables to link to the existing multi-tenancy structure.34 351.  **`presentations` & `slides`**36    - **Add Column**: `user_id` (UUID) -> References `users.id`37    - **Add Column**: `org_id` (UUID) -> References `organizations.id`38    - *Purpose*: Data isolation per user and organization.39 402.  **`templates`**41    - **Add Column**: `user_id` (UUID) -> References `users.id` (Uploader)42    - **Add Column**: `org_id` (UUID) -> References `organizations.id` (Owner Org)43    - *Note*: This enables the custom template logic.44 453.  **`presentation_layout_codes`**46    - Linked to `templates` via `presentation` (UUID) key. Implicitly belongs to the org of the template.47 48## 4. Role-Based Access Control (RBAC) Logic49 50We will leverage the existing `role` field on the `users` table.51 52### **Logic**53- **User Role Source**: `users.role` (e.g., `admin`, `employee`, `superadmin`).54- **Organization Source**: `users.organization_id`.55 56### **Custom Template Rules**57- **Organization Admin** (User with `role='admin'`):58    - **Action**: Can Upload & Delete templates.59    - **Scope**: Only for their `organization_id`.60- **Organization Member** (User with `role='employee'`):61    - **Action**: Can **Use** (Read) templates.62    - **Restriction**: Cannot Upload or Delete.63 64## 5. Frontend/UX Implementation (RBAC)65 66### **Upload Button**67- **Visibility**: Visible to **ALL** users in the organization.68- **Behavior**:69    - **Admin Click**: Opens upload dialog.70    - **Member Click**: Shows error toast:71      > *"You have no access to upload and delete the template. Contact your org admin to add a template."*72 73### **Delete Option**74- **Visibility**: Visible on template cards.75- **Behavior**:76    - **Admin Click**: Deletes template.77    - **Member Click**: Shows the same error toast.78 79## 6. Implementation Steps80 811.  **Create `SecretManager`**: Implement `servers/fastapi/utils/secret_manager.py`.822.  **Define SQLModels**:83    - Create `servers/fastapi/models/sql/external_schema.py` (or similar) to define `User` and `Organization` models matching your existing DB tags (for ORM correctness).843.  **Update Existing Models**:85    - Modify `PresentationModel`, `SlideModel`, `TemplateModel` in `servers/fastapi/models/sql/` to include `user_id` and `org_id` fields.864.  **Update API Logic**:87    - In `api/v1/ppt/endpoints/presentation.py` and `template_management`:88    - Extract `user_id` from request headers (or auth token).89    - Query the DB to check `user.role` and `user.organization_id`.90    - Apply RBAC rules before permitting Upload/Delete.91 92## 7. Migration Note93Since we are connecting to an **existing populated database**, we must be careful not to drop existing tables. The `create_db_and_tables` function will need to be adjusted to **only create tables that don't exist** (Safe Mode) and ideally we should use a migration tool (like Alembic) for adding columns to existing tables, but for this task, we will assume `SQLModel.metadata.create_all` is safe to run (it usually skips existing tables).94