File size: 5,199 Bytes
fe1e225 | 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 | # 07 — Database Schema Diagram
## Overview
BAYAN uses Supabase (PostgreSQL) with Row-Level Security (RLS). All tables enforce per-user data isolation via `auth.uid()`.
## Entity Relationship Diagram
```mermaid
erDiagram
AUTH_USERS {
uuid id PK
text email
boolean is_anonymous
jsonb raw_user_meta_data
jsonb raw_app_meta_data
timestamptz created_at
timestamptz updated_at
}
PROFILES {
uuid id PK "FK → auth.users(id)"
text display_name
text avatar_url
text auth_provider "anonymous | google"
timestamptz created_at
timestamptz updated_at
}
DOCUMENTS {
uuid id PK "gen_random_uuid()"
uuid user_id FK "→ auth.users(id)"
text title "Default: مستند جديد"
text content "HTML content"
timestamptz created_at
timestamptz updated_at
}
SUMMARIES {
uuid id PK "gen_random_uuid()"
uuid document_id FK "→ documents(id)"
uuid user_id FK "→ auth.users(id)"
text original_text
text summary_text
integer word_count
real compression_ratio
timestamptz created_at
}
SETTINGS {
uuid id PK "gen_random_uuid()"
uuid user_id FK "→ auth.users(id)"
jsonb preferences "theme, font, size, etc."
timestamptz created_at
timestamptz updated_at
}
AUTH_USERS ||--|| PROFILES : "auto-created on signup"
AUTH_USERS ||--o{ DOCUMENTS : "owns"
AUTH_USERS ||--o| SETTINGS : "has"
DOCUMENTS ||--o{ SUMMARIES : "has"
AUTH_USERS ||--o{ SUMMARIES : "owns"
```
## Table Details
### profiles
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `uuid` | PK, FK → auth.users | User ID from Supabase Auth |
| `display_name` | `text` | nullable | Display name (from Google or "ضيف") |
| `avatar_url` | `text` | nullable | Google profile picture URL |
| `auth_provider` | `text` | NOT NULL, default 'anonymous' | `anonymous` or `google` |
| `created_at` | `timestamptz` | NOT NULL, default now() | Account creation time |
| `updated_at` | `timestamptz` | NOT NULL, default now() | Last profile update |
### documents
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `uuid` | PK, default gen_random_uuid() | Document unique ID |
| `user_id` | `uuid` | FK → auth.users, NOT NULL | Owner |
| `title` | `text` | default 'مستند جديد' | Document title |
| `content` | `text` | nullable | HTML content from editor |
| `created_at` | `timestamptz` | NOT NULL, default now() | Creation time |
| `updated_at` | `timestamptz` | NOT NULL, default now() | Last save time |
### summaries
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `uuid` | PK, default gen_random_uuid() | Summary unique ID |
| `document_id` | `uuid` | FK → documents(id) | Parent document |
| `user_id` | `uuid` | FK → auth.users, NOT NULL | Owner |
| `original_text` | `text` | NOT NULL | Source text that was summarized |
| `summary_text` | `text` | NOT NULL | Generated summary |
| `word_count` | `integer` | nullable | Summary word count |
| `compression_ratio` | `real` | nullable | Compression percentage |
| `created_at` | `timestamptz` | NOT NULL, default now() | Generation time |
### settings
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `uuid` | PK, default gen_random_uuid() | Setting record ID |
| `user_id` | `uuid` | FK → auth.users, UNIQUE | Owner (one per user) |
| `preferences` | `jsonb` | NOT NULL, default '{}' | All user preferences |
| `created_at` | `timestamptz` | NOT NULL, default now() | Creation time |
| `updated_at` | `timestamptz` | NOT NULL, default now() | Last update time |
## Row Level Security (RLS) Policies
All tables have RLS enabled. Policies follow the pattern:
```sql
-- SELECT: Users can only read their own data
CREATE POLICY "select_own" ON table
FOR SELECT USING (auth.uid() = user_id);
-- INSERT: Users can only insert their own data
CREATE POLICY "insert_own" ON table
FOR INSERT WITH CHECK (auth.uid() = user_id);
-- UPDATE: Users can only update their own data
CREATE POLICY "update_own" ON table
FOR UPDATE USING (auth.uid() = user_id);
-- DELETE: Users can only delete their own data
CREATE POLICY "delete_own" ON table
FOR DELETE USING (auth.uid() = user_id);
```
## Database Triggers
| Trigger | Event | Function | Purpose |
|---------|-------|----------|---------|
| `on_auth_user_created` | `AFTER INSERT ON auth.users` | `handle_new_user()` | Auto-create profile on signup |
| `on_auth_user_updated` | `AFTER UPDATE ON auth.users` | `handle_user_updated()` | Update profile when linking Google |
## Client-Side Storage (localStorage)
| Key | Type | Purpose |
|-----|------|---------|
| `bayan_editor_draft` | `string (HTML)` | Unsaved editor content |
| `bayan_dismissed_words` | `JSON array` | Words marked "keep as-is" |
| `bayan_word_goal` | `number` | Word count target |
| `bayan_theme` | `string` | "dark" or "light" |
|