> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Platform Foundation Database Tables

> Platform Foundation provides the core infrastructure for multi-tenancy, authentication, authorization, notifications, documents, and shared services across all…

**Prefix:** `pf_`\
**Table Count:** 72\
**Last Updated:** 2026-01-10

***

## Overview

Platform Foundation provides the core infrastructure for multi-tenancy, authentication, authorization, notifications, documents, and shared services across all modules.

***

## Table Categories

### 1. Core Multi-Tenancy (4 tables)

| Table              | Columns | Description                                        |
| ------------------ | ------- | -------------------------------------------------- |
| `pf_organizations` | 15      | Root tenant entity - all business data scoped here |
| `pf_sites`         | 18      | Physical locations within an organization          |
| `pf_profiles`      | 22      | User profiles linked to auth.users                 |
| `pf_departments`   | 12      | Organizational departments                         |

**Key Relationships:**

```
pf_organizations (1) ──< (many) pf_sites
pf_organizations (1) ──< (many) pf_departments
pf_profiles (many) >── (1) pf_organizations
```

### 2. Authorization & Roles (7 tables)

| Table                      | Columns | Description                                               |
| -------------------------- | ------- | --------------------------------------------------------- |
| `pf_user_roles`            | 10      | **DEPRECATED** - V1 table, read-only (retained for audit) |
| `pf_user_role_assignments` | 8       | User-to-role mappings (V2 - active)                       |
| `pf_role_permissions`      | 7       | Permissions assigned to roles                             |
| `pf_custom_roles`          | 12      | Organization-defined custom roles                         |
| `pf_module_permissions`    | 8       | Available permissions per module                          |
| `pf_permission_sets`       | 10      | Grouped permission collections                            |
| `pf_permission_set_items`  | 5       | Items within permission sets                              |

**Usage Pattern:**

```typescript theme={null}
// Check permission
const hasPermission = await supabase.rpc('pf_user_has_permission', {
  p_user_id: userId,
  p_permission_key: 'hr.employees.view'
});
```

### 3. Notifications (6 tables)

| Table                          | Columns | Description                     |
| ------------------------------ | ------- | ------------------------------- |
| `pf_notifications`             | 16      | Individual notification records |
| `pf_notification_templates`    | 14      | Reusable notification templates |
| `pf_notification_preferences`  | 12      | User notification settings      |
| `pf_notification_batches`      | 10      | Batch notification sends        |
| `pf_notification_delivery_log` | 11      | Delivery tracking               |
| `pf_push_subscriptions`        | 9       | Web push subscriptions          |

**Key Fields:**

* `notification_type`: 'email', 'in\_app', 'push', 'sms'
* `priority`: 'low', 'normal', 'high', 'urgent'
* `status`: 'pending', 'sent', 'delivered', 'failed'

### 4. Documents & Signatures (6 tables)

| Table                     | Columns | Description                        |
| ------------------------- | ------- | ---------------------------------- |
| `pf_documents`            | 22      | Document metadata and storage refs |
| `pf_document_versions`    | 14      | Version history                    |
| `pf_document_approvals`   | 12      | Approval workflows                 |
| `pf_document_permissions` | 8       | Access control                     |
| `pf_signatures`           | 15      | Digital signature records          |
| `pf_signature_requests`   | 14      | Signature request tracking         |

**Storage Integration:**

```typescript theme={null}
// Documents stored in Supabase Storage
const bucket = 'pf-documents';
const path = `${organizationId}/${documentId}/${version}`;
```

### 5. AI Integration (3 tables)

| Table                   | Columns | Description                   |
| ----------------------- | ------- | ----------------------------- |
| `pf_ai_conversations`   | 14      | AI chat sessions              |
| `pf_ai_usage_logs`      | 12      | Token/cost tracking           |
| `pf_ai_prompt_feedback` | 10      | User feedback on AI responses |

**Usage Tracking:**

* Tracks tokens used per conversation
* Cost allocation by organization
* Model performance metrics

### 6. Wizards & Guided Workflows (5 tables)

| Table                            | Columns | Description               |
| -------------------------------- | ------- | ------------------------- |
| `pf_wizard_templates`            | 18      | Wizard definitions        |
| `pf_wizard_template_versions`    | 12      | Version history           |
| `pf_wizard_executions`           | 16      | Active wizard instances   |
| `pf_wizard_analytics_events`     | 11      | Usage analytics           |
| `pf_wizard_marketplace_listings` | 14      | Shared wizard marketplace |

### 7. Dashboards (4 tables)

| Table                           | Columns | Description            |
| ------------------------------- | ------- | ---------------------- |
| `pf_dashboard_templates`        | 16      | Dashboard definitions  |
| `pf_dashboard_shares`           | 8       | Sharing configurations |
| `pf_org_dashboard_defaults`     | 7       | Org-level defaults     |
| `pf_user_dashboard_preferences` | 10      | User customizations    |

### 8. Reports (6 tables)

| Table                   | Columns | Description             |
| ----------------------- | ------- | ----------------------- |
| `pf_reports`            | 18      | Report definitions      |
| `pf_report_schedules`   | 14      | Scheduled report runs   |
| `pf_report_runs`        | 12      | Execution history       |
| `pf_report_executions`  | 11      | Detailed execution logs |
| `pf_report_permissions` | 7       | Access control          |
| `pf_report_recipients`  | 8       | Distribution lists      |

### 9. Custom Objects (8 tables)

| Table                      | Columns | Description               |
| -------------------------- | ------- | ------------------------- |
| `pf_custom_objects`        | 16      | Custom entity definitions |
| `pf_custom_object_fields`  | 18      | Field definitions         |
| `pf_custom_object_records` | 12      | Record data storage       |
| `pf_object_relationships`  | 10      | Relationship definitions  |
| `pf_object_layouts`        | 12      | UI layout configurations  |
| `pf_object_validations`    | 10      | Validation rules          |
| `pf_object_triggers`       | 12      | Automation triggers       |
| `pf_object_permissions`    | 8       | Object-level security     |

### 10. Field Configuration (6 tables)

| Table                        | Columns | Description               |
| ---------------------------- | ------- | ------------------------- |
| `pf_entity_field_configs`    | 14      | Field metadata per entity |
| `pf_field_permissions`       | 8       | Field-level security      |
| `pf_field_interaction_stats` | 10      | Usage analytics           |
| `pf_page_layouts`            | 12      | Page layout definitions   |
| `pf_page_layout_sections`    | 10      | Layout sections           |
| `pf_page_layout_fields`      | 9       | Fields within sections    |

### 11. Picklists (2 tables)

| Table               | Columns | Description          |
| ------------------- | ------- | -------------------- |
| `pf_picklists`      | 12      | Picklist definitions |
| `pf_picklist_items` | 10      | Picklist values      |

**Usage:**

```typescript theme={null}
// Fetch picklist items
const { data } = await supabase
  .from('pf_picklist_items')
  .select('*')
  .eq('picklist_id', picklistId)
  .eq('is_active', true)
  .order('display_order');
```

### 12. Integrations (6 tables)

| Table                        | Columns | Description                |
| ---------------------------- | ------- | -------------------------- |
| `pf_integrations`            | 16      | Integration configurations |
| `pf_integration_credentials` | 12      | Encrypted credentials      |
| `pf_outbound_webhooks`       | 14      | Webhook configurations     |
| `pf_webhook_deliveries`      | 12      | Delivery logs              |
| `pf_oauth_providers`         | 14      | OAuth provider configs     |
| `pf_oauth_tokens`            | 12      | Token storage              |

### 13. Settings & Audit (5 tables)

| Table                 | Columns | Description              |
| --------------------- | ------- | ------------------------ |
| `pf_module_settings`  | 25      | Platform module settings |
| `pf_settings_audit`   | 10      | Settings change history  |
| `pf_audit_logs`       | 18      | System-wide audit trail  |
| `pf_health_checks`    | 12      | System health monitoring |
| `pf_health_incidents` | 14      | Incident tracking        |

### 14. Tasks & Invitations (3 tables)

| Table                 | Columns | Description              |
| --------------------- | ------- | ------------------------ |
| `pf_tasks`            | 18      | Task assignments         |
| `pf_task_comments`    | 10      | Task discussions         |
| `pf_user_invitations` | 14      | User invitation tracking |

***

## Common Query Patterns

### Get User's Organizations

```typescript theme={null}
const { data } = await supabase
  .from('pf_profiles')
  .select(`
    *,
    organization:pf_organizations(*)
  `)
  .eq('user_id', userId);
```

### Check User Permissions

```typescript theme={null}
const { data } = await supabase.rpc('pf_get_user_permissions', {
  p_user_id: userId,
  p_organization_id: orgId
});
```

### Get Active Notifications

```typescript theme={null}
const { data } = await supabase
  .from('pf_notifications')
  .select('*')
  .eq('user_id', userId)
  .eq('is_read', false)
  .order('created_at', { ascending: false });
```

***

## RLS Policies

All PF tables have RLS enabled with policies based on:

* `organization_id` for tenant isolation
* `user_id` for personal data
* Role-based access via `pf_user_has_org_access()` helper function

***

## Module Settings

`pf_module_settings` contains 25 columns for platform configuration:

| Setting                   | Type    | Default             | Description             |
| ------------------------- | ------- | ------------------- | ----------------------- |
| `default_locale`          | text    | 'en-US'             | Default language        |
| `default_timezone`        | text    | 'America/New\_York' | Default timezone        |
| `session_timeout_minutes` | integer | 60                  | Session expiration      |
| `mfa_required`            | boolean | false               | Require 2FA             |
| `password_min_length`     | integer | 8                   | Minimum password length |
| `audit_retention_days`    | integer | 365                 | Audit log retention     |

***

## See Also

* [Database Schema Overview](/architecture/DATABASE_SCHEMA)
* [Database Audit Report](https://github.com/Encore-OS/encoreos/blob/development/docs/archive/architecture/DATABASE_AUDIT_REPORT.md)
* [RLS Patterns](/architecture/DATABASE_SCHEMA#rls-patterns)