# Data Model: Global Policy Search

**Feature**: 001-global-policy-search  
**Version**: 1.0.0

## Entity: PolicySetting

### Drizzle Schema Definition

```typescript
// lib/db/schema/policySettings.ts

import { pgTable, text, timestamp, index } from 'drizzle-orm/pg-core';
import { createId } from '@paralleldrive/cuid2';

export const policySettings = pgTable(
  'policy_settings',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => createId()),
    
    tenantId: text('tenant_id').notNull(),
    
    policyName: text('policy_name').notNull(),
    
    policyType: text('policy_type').notNull(),
    // e.g., 'deviceConfiguration', 'compliancePolicy', 'windowsUpdateForBusiness'
    
    settingName: text('setting_name').notNull(),
    
    settingValue: text('setting_value').notNull(),
    
    graphPolicyId: text('graph_policy_id').notNull(),
    // Microsoft Graph ID of the policy
    
    lastSyncedAt: timestamp('last_synced_at').defaultNow().notNull(),
    
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (table) => ({
    // Index for tenant-scoped searches
    tenantIdIdx: index('policy_settings_tenant_id_idx').on(table.tenantId),
    
    // Index for search queries
    settingNameIdx: index('policy_settings_setting_name_idx').on(table.settingName),
    
    // Composite index for upsert operations
    upsertIdx: index('policy_settings_upsert_idx').on(
      table.tenantId,
      table.graphPolicyId,
      table.settingName
    ),
  })
);

// Type exports for type-safe queries
export type PolicySetting = typeof policySettings.$inferSelect;
export type NewPolicySetting = typeof policySettings.$inferInsert;
```

---

## Field Specifications

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `id` | CUID2 | Yes | Auto-generated unique identifier |
| `tenantId` | text | Yes | Azure AD tenant ID (from `tid` claim) |
| `policyName` | text | Yes | Display name of the Intune policy |
| `policyType` | text | Yes | Type/category of policy (see Policy Types below) |
| `settingName` | text | Yes | Name of the individual setting |
| `settingValue` | text | Yes | Value of the setting (JSON-stringified if complex) |
| `graphPolicyId` | text | Yes | Microsoft Graph API policy ID |
| `lastSyncedAt` | timestamp | Yes | When this setting was last synced from Graph |
| `createdAt` | timestamp | Yes | Record creation timestamp |

---

## Policy Types (enum values)

```typescript
export const POLICY_TYPES = [
  'deviceConfiguration',
  'compliancePolicy', 
  'windowsUpdateForBusiness',
  'endpointSecurity',
  'appConfiguration',
  'enrollmentRestriction',
  'conditionalAccess',
] as const;

export type PolicyType = typeof POLICY_TYPES[number];
```

---

## Relationships

```
User (Azure AD)
  └── tenantId ─────────┐
                        │
PolicySetting           │
  └── tenantId ─────────┘ (implicit FK via Azure AD tid)
```

**Note**: No explicit foreign key to users table. Tenant isolation is enforced by filtering on `tenantId` which comes from the authenticated Azure AD session.

---

## Validation Rules

### Input Validation (Zod Schema)

```typescript
// lib/validators/policySettings.ts
import { z } from 'zod';
import { POLICY_TYPES } from '@/lib/db/schema/policySettings';

export const policySettingSchema = z.object({
  tenantId: z.string().min(1, 'Tenant ID is required'),
  policyName: z.string().min(1, 'Policy name is required'),
  policyType: z.enum(POLICY_TYPES),
  settingName: z.string().min(1, 'Setting name is required'),
  settingValue: z.string(), // Can be empty string
  graphPolicyId: z.string().min(1, 'Graph Policy ID is required'),
});

export const bulkPolicySettingsSchema = z.object({
  settings: z.array(policySettingSchema).min(1).max(1000),
});

export type PolicySettingInput = z.infer<typeof policySettingSchema>;
export type BulkPolicySettingsInput = z.infer<typeof bulkPolicySettingsSchema>;
```

---

## Indexes

| Index Name | Columns | Purpose |
|------------|---------|---------|
| `policy_settings_tenant_id_idx` | `tenant_id` | Fast tenant filtering |
| `policy_settings_setting_name_idx` | `setting_name` | Fast search on setting names |
| `policy_settings_upsert_idx` | `tenant_id, graph_policy_id, setting_name` | Efficient upsert operations |

---

## Migration

After adding the schema, run:

```bash
npm run db:push
```

Or for production with migrations:

```bash
npm run db:generate
npm run db:migrate
```