# multi-tenant-db

> Database architecture for multi-tenant Ultimate Facturi platform. Use when working with schema design, migrations, adding tables, tenant isolation, or understanding the multi-company data structure.

- Author: Vlad Oprea
- Repository: obsid2025/ultimate-facturi-next
- Version: 20260126160544
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/obsid2025/ultimate-facturi-next
- Web: https://mule.run/skillshub/@@obsid2025/ultimate-facturi-next~multi-tenant-db:20260126160544

---

---
name: multi-tenant-db
description: Database architecture for multi-tenant Ultimate Facturi platform. Use when working with schema design, migrations, adding tables, tenant isolation, or understanding the multi-company data structure.
allowed-tools: Read, Grep, Glob, mcp__supabase__execute_sql, mcp__supabase__list_tables, mcp__supabase__apply_migration
---

# Multi-Tenant Database Architecture

## Overview

Ultimate Facturi uses **schema-per-tenant** isolation in PostgreSQL (Supabase). Each company has its own dedicated schema with identical table structures.

## Schema Structure

```
┌─────────────────────────────────────────────────────────────┐
│                    Supabase Project                          │
├─────────────────┬───────────────┬───────────────┬───────────┤
│  schema: public │ schema: obsid │ schema: firma2│ schema: N │
│   (management)  │  (tenant 1)   │  (tenant 2)   │           │
├─────────────────┼───────────────┴───────────────┴───────────┤
│ • companies     │ Per-company tables (14 total):            │
│ • api_credentials│ invoices, bank_transactions, gls_parcels,│
│ • users         │ sameday_parcels, netopia_*, products, etc │
└─────────────────┴───────────────────────────────────────────┘
```

## Public Schema (Shared Management)

### companies
Tenant registry - master list of all companies.

```sql
SELECT * FROM public.companies;
-- Columns: id, name, slug, schema_name, cif, industry, logo_url, is_active, settings, created_at
```

### api_credentials
Encrypted API keys per company/service.

```sql
SELECT * FROM public.api_credentials WHERE company_id = 'uuid';
-- service_type values: oblio, gls, sameday, netopia, imap, smtp, cloudinary, gomag
-- credentials: JSONB with service-specific keys
```

### users
User accounts with company association.

```sql
SELECT * FROM public.users WHERE company_id = 'uuid';
-- Columns: id, username, username_hash, password_hash, display_name, company_id, role, is_active
```

## Per-Company Schema Tables (14 total)

Each company schema contains these tables:

| Table | Purpose | Unique Key |
|-------|---------|------------|
| `invoices` | Oblio-synced invoices | `oblio_id` |
| `bank_transactions` | MT940 bank imports | `transaction_hash` |
| `gls_parcels` | GLS courier parcels | `parcel_number` |
| `sameday_parcels` | Sameday courier parcels | `awb_number` |
| `netopia_transactions` | Payment gateway records | `transaction_id` |
| `netopia_batches` | Payment batch metadata | `batch_id` |
| `gls_borderouri` | GLS COD settlement reports | - |
| `gls_borderou_parcels` | Parcels within settlements | - |
| `profit_transactions` | P&L categorized transactions | `transaction_hash` |
| `products` | Product catalog | `sku` |
| `product_sales` | Individual sales records | `sale_hash` |
| `bonuri_procesate` | Production receipts (decanturi) | - |
| `sync_logs` | Sync operation audit trail | - |

## Deduplication Patterns

### Hash-Based Deduplication
```python
import hashlib

# Bank transactions
transaction_hash = hashlib.md5(f"{date}|{reference}|{amount:.2f}".encode()).hexdigest()

# Product sales
sale_hash = hashlib.md5(f"{oblio_id}|{sku}|{quantity}".encode()).hexdigest()
```

### Unique Constraints
All tables use PostgreSQL UNIQUE constraints on natural keys to prevent duplicates at database level.

## Querying Data

### Current Company (from session)
```python
from app.utils.supabase_client import get_current_client

client = get_current_client()  # Uses st.session_state.current_schema
result = client.table('invoices').select('*').execute()
```

### Specific Schema
```python
from app.utils.supabase_client import get_supabase_client

# Public tables (management)
public_client = get_supabase_client('public')
companies = public_client.table('companies').select('*').execute()

# Company-specific tables
obsid_client = get_supabase_client('obsid')
invoices = obsid_client.table('invoices').select('*').execute()
```

## Adding New Tables

1. **Add to template** in `migrations/company_schema_template.sql`

2. **Apply to all existing schemas**:
```sql
-- For each company schema
CREATE TABLE {schema_name}.new_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- columns
);
```

3. **Update create_company.py** script

## Creating New Company

```bash
python scripts/create_company.py \
  --name "Firma Nouă SRL" \
  --slug "firma-noua" \
  --cif "RO12345678" \
  --industry "retail" \
  --admin-user "admin" \
  --admin-pass "password123" \
  --admin-name "Administrator"
```

This will:
1. Insert into `public.companies`
2. Create schema `firma_noua`
3. Create all 14 tables in schema
4. Create admin user in `public.users`

## Migration Commands

### Move table to schema
```sql
ALTER TABLE public.invoices SET SCHEMA obsid;
```

### Create schema
```sql
CREATE SCHEMA IF NOT EXISTS firma_noua;
```

### Grant permissions
```sql
GRANT USAGE ON SCHEMA firma_noua TO authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA firma_noua TO authenticated;
```

## Rollback Strategy

If migration fails:
```sql
-- Restore tables to public
ALTER TABLE obsid.invoices SET SCHEMA public;
-- Repeat for all tables

-- Drop company schema
DROP SCHEMA obsid CASCADE;

-- Remove from registry
DELETE FROM public.companies WHERE slug = 'obsid';
```

## Best Practices

1. **Always use `get_current_client()`** in page code - ensures correct schema
2. **Never hardcode schema names** - use session state
3. **Test with multiple companies** before deploying schema changes
4. **Backup before migrations** - use Supabase dashboard
5. **Column normalization**: Always `df.columns.str.strip().str.lower()`