tenantpilot/specs/001-global-policy-search/research.md

Research: Global Policy Search

Feature: 001-global-policy-search
Date: 2025-12-05

Research Areas

1. Drizzle ORM Case-Insensitive Search

Decision: Use ilike operator for case-insensitive search in PostgreSQL via Drizzle ORM.

Rationale:

• Drizzle ORM supports PostgreSQL's ilike operator directly
• No additional configuration or extensions required
• Pattern: ilike(field, '%searchterm%') for substring matching

Alternatives Considered:

• Full-text search with tsvector: Overkill for v1, adds complexity
• LOWER() function: Works but ilike is more idiomatic for PostgreSQL

Implementation:

import { ilike, or } from 'drizzle-orm';

const results = await db
  .select()
  .from(policySettings)
  .where(
    or(
      ilike(policySettings.settingName, `%${searchTerm}%`),
      ilike(policySettings.settingValue, `%${searchTerm}%`)
    )
  );

---

2. Server Actions for Search

Decision: Use Next.js Server Actions for search functionality instead of API routes.

Rationale:

• Constitution mandates Server-First architecture
• Server Actions provide type-safe, secure data fetching
• No client-side fetch code needed
• Built-in CSRF protection

Pattern:

// lib/actions/search.ts
'use server';

export async function searchPolicySettings(formData: FormData) {
  const searchTerm = formData.get('query') as string;
  // ... DB query with tenant filtering
}

---

3. API Secret Authentication for Ingestion API

Decision: Use X-API-SECRET header validation for the /api/policy-settings endpoint.

Rationale:

• Simple and effective for server-to-server communication (n8n → API)
• No OAuth complexity needed for internal service
• Environment variable based, easy to rotate

Pattern:

// app/api/policy-settings/route.ts
const apiSecret = request.headers.get('X-API-SECRET');
if (apiSecret !== process.env.POLICY_API_SECRET) {
  return Response.json({ error: 'Unauthorized' }, { status: 401 });
}

Environment Variable: POLICY_API_SECRET

---

4. Tenant ID from Azure AD Session

Decision: Extract tenantId from NextAuth session/JWT token (Azure AD tid claim).

Rationale:

• Azure AD includes tid (tenant ID) in the ID token
• Can be accessed via NextAuth callbacks
• Ensures tenant isolation at authentication layer

Implementation:

// lib/auth/utils.ts - extend session callback
callbacks: {
  jwt: async ({ token, account, profile }) => {
    if (account && profile) {
      token.tenantId = (profile as any).tid;
    }
    return token;
  },
  session: ({ session, token }) => {
    session.user.tenantId = token.tenantId as string;
    return session;
  },
}

---

5. Upsert Logic for Data Ingestion

Decision: Use Drizzle's onConflictDoUpdate for upsert operations.

Rationale:

• Atomic operation, prevents race conditions
• Single query instead of SELECT + INSERT/UPDATE
• Uses composite unique constraint on (tenantId, graphPolicyId, settingName)

Pattern:

await db
  .insert(policySettings)
  .values(data)
  .onConflictDoUpdate({
    target: [policySettings.tenantId, policySettings.graphPolicyId, policySettings.settingName],
    set: {
      settingValue: data.settingValue,
      lastSyncedAt: new Date(),
    },
  });

---

Dependencies Identified

Dependency	Version	Purpose
drizzle-orm	^0.44.x	Database ORM (already installed)
@shadcn/ui	latest	Input, Table components (already installed)
next-auth	^4.x	Session with tenantId (already installed)

No New Dependencies Required

All features can be implemented with existing project dependencies.