Database

ERD Schema

---

Indexes

Table	Index	Type	Usage
archived_mails	search_vector	GIN	Full-text search
archived_mails	gmail_account_id	BTree	Filter by account
archived_mails	sender	BTree	Filter by sender
archived_mails	date DESC	BTree	Sort by date
archived_mails	size_bytes DESC	BTree	Sort by size
archived_attachments	archived_mail_id	BTree	Join emails ↔ attachments
jobs	gmail_account_id	BTree	Filter jobs by account
jobs	status	BTree	Filter by status
jobs	created_at DESC	BTree	Sort by date
jobs	user_id	BTree	Filter jobs by user
users	google_id (partial)	Unique	Google SSO lookup
audit_logs	user_id + created_at	BTree	Logs by user
audit_logs	action + created_at	BTree	Filter by action
webhooks	user_id	BTree	Filter by user
notification_preferences	user_id	Unique	One row per user
tracking_pixels	gmail_account_id	BTree	Filter by account
tracking_pixels	(gmail_account_id, gmail_message_id)	Unique	Scan deduplication
pii_findings	gmail_account_id	BTree	Filter by account
pii_findings	archived_mail_id	BTree	Join emails ↔ PII
retention_policies	user_id	BTree	Filter by user
gmail_api_usage	(gmail_account_id, recorded_at)	BTree	Quota stats by account
gmail_api_usage	recorded_at	BTree	Old data cleanup
storage_configs	user_id	Unique	One config per user
archived_mails	(gmail_account_id, deleted_at)	BTree (partial)	Trash listing (WHERE deleted_at IS NOT NULL)

---

Full-Text Search

The search_vector field is automatically updated via a PostgreSQL trigger on each insert/update on archived_mails:

-- Poids de recherche :
-- A (le plus fort) : sujet
-- B : expéditeur
-- C : snippet (extrait du corps)
NEW.search_vector :=
  setweight(to_tsvector('french', COALESCE(NEW.subject, '')), 'A') ||
  setweight(to_tsvector('french', COALESCE(NEW.sender, '')), 'B') ||
  setweight(to_tsvector('french', COALESCE(NEW.snippet, '')), 'C');

Search query example:

SELECT *, ts_rank(search_vector, query) AS rank
FROM archived_mails, to_tsquery('french', 'facture & 2024') query
WHERE gmail_account_id = $1
  AND search_vector @@ query
ORDER BY rank DESC, date DESC;

---

JSONB — Rules Format

conditions (array)

[
  { "field": "from", "operator": "contains", "value": "newsletter@" },
  { "field": "subject", "operator": "contains", "value": "promotion" }
]

Supported fields: from, to, subject, has_attachment, size_gt (bytes)

Operators: contains, equals, not_contains, gt, lt

action (object)

{ "type": "trash" }
{ "type": "label", "labelId": "Label_123" }
{ "type": "archive" }
{ "type": "archive_nas" }