Database Schema: Drive Module

This document details the database structure for the Drive module.

---

1. Overview

The Drive module uses PostgreSQL with Drizzle ORM. It implements a dual-layer storage model: metadata is stored in SQL, while binary content is stored in a MinIO (S3-compatible) Object Store.

Multi-tenancy: There is no organizations table. Tenant isolation is enforced via org_id — a UUID column present on every domain table, extracted from the authenticated user's JWT.

Core Domain Model

---

2. Domain Table Definitions

folders

Represents the hierarchical directory structure.

Field	Type	Description
id	uuid	PK, auto-generated.
org_id	uuid	Organization scope (not a FK — denormalized).
parent_id	uuid	FK to folders.id (self-reference). null for root folders.
name	text	Folder name.
user_id	uuid	Owner of the folder.
created_by	uuid	User who created the folder.
created_at	timestamptz	Creation timestamp.

files

Metadata for stored objects.

Field	Type	Description
id	uuid	PK, auto-generated.
org_id	uuid	Organization scope (not a FK — denormalized).
folder_id	uuid	FK to folders.id. null for root-level files.
name	text	Display name.
original_name	text	Original filename at upload time.
size	bigint	File size in bytes.
mime_type	text	Standard MIME type (e.g., image/jpeg).
storage_path	text	Object key in MinIO: {orgId}/{timestamp}-{randomHex}-{filename}.
storage_bucket	text	Target MinIO bucket name.
user_id	uuid	Owner of the file.
uploaded_by	uuid	User who uploaded the file.
created_at	timestamptz	Upload timestamp.

share_links

External access tokens for files.

Field	Type	Description
id	uuid	PK, auto-generated.
file_id	uuid	FK to files.id.
token	text	Unique base64url-encoded secure token.
expires_at	timestamptz	Expiration timestamp.
created_by	uuid	User who created the share link.
revoked_at	timestamptz	Soft-delete timestamp. null if still active.

---

3. Identity Table Definitions

users

Local user records provisioned from Shell JWT exchange.

Field	Type	Description
id	uuid	PK, auto-generated.
shell_user_id	varchar(255)	Linked Shell user identity (unique, nullable).
email	varchar(320)	User email (unique).
name	varchar(255)	Display name (nullable).
status	varchar(20)	active or suspended. Default: active.
created_at	timestamp	Provisioning timestamp.
updated_at	timestamp	Last modification timestamp.

roles

Permission groups assignable to users.

Field	Type	Description
id	uuid	PK, auto-generated.
name	varchar(100)	Unique role name.
is_default	boolean	If true, auto-assigned to new users on provisioning.

user_roles

Junction table for user-role assignments.

Field	Type	Description
user_id	uuid	FK to users.id (cascade delete).
role_id	uuid	FK to roles.id (cascade delete).

permissions

Permission codes assigned to roles.

Field	Type	Description
id	uuid	PK, auto-generated.
code	varchar(255)	Unique permission code (e.g., drive:read, drive:write).
role_id	uuid	FK to roles.id (cascade delete).

---

4. Implementation Details

• Atomic Deletion: When a folder is deleted, the service recursively deletes all child folders, then deletes all files (including MinIO objects and share link records) before removing the folder record itself.
• Storage Key Format: MinIO object keys follow the pattern {orgId}/{timestamp}-{randomHex}-{filename} to ensure global uniqueness.
• Share Link Revocation: Soft-delete via revokedAt timestamp. Hard deletes occur automatically when the parent file is deleted.
• User Provisioning: On first Shell JWT exchange, the Drive API provisions a local user and assigns all roles where is_default = true.