clienthub/.planning/phases/05-offer-system/05-RESEARCH.md

Phase 5: Offer System — Research

Researched: 2026-05-30 Domain: Drizzle ORM schema design, Next.js 16 App Router server actions, shadcn/ui multi-select pattern, revenue forecast algorithm Confidence: HIGH (codebase fully inspected; all patterns verified against existing code)

---

<phase_requirements>

Phase Requirements

ID	Description	Research Support
OFFER-01	Admin can create macro-offers with internal name, public name, macro transformation promise	New table offer_macros; CRUD follows catalog/actions.ts pattern
OFFER-02	Each macro-offer has child micro-offers with internal name, public name, micro transformation promise, duration in months	New table offer_micros with FK to offer_macros; duration_months integer column
OFFER-03	Admin can create services with name, price, transformation description; each service assignable to multiple micro-offers via multi-select (many-to-many)	New table offer_services (separate from service_catalog) + junction table offer_micro_services; multi-select via checkbox list pattern
OFFER-04	Admin can assign one or more micro-offers to a project; admin sees active offers per project/client	New table project_offers with project_id, micro_offer_id, start_date, accepted_total columns
OFFER-05	Client dashboard shows active offers with public name, cumulative service price, accepted final price; multiple offers shown	Extend getProjectView() to include offer data; render in ClientDashboard component; NO internal names or individual prices
OFFER-06	Admin revenue forecast for next 12 months based on active offers, duration, accepted_total; monthly breakdown	Pure JS computation in a new query function; no new DB tables required
</phase_requirements>

---

Summary

Phase 5 adds an Offer System on top of the existing multi-project structure. The work divides cleanly into three layers: (1) a new schema with four tables, (2) admin CRUD UI for the offers catalog and project assignment, and (3) two read surfaces — the client dashboard and an admin revenue forecast page.

The most important architectural decision is that Offer Services (offer_services) are a NEW entity, distinct from the existing service_catalog. The existing service_catalog is used for quote line items (internal pricing). Offer services carry a "transformation description" for marketing language and are bundled into micro-offers. The two entities serve different purposes and must not be merged.

The revenue forecast algorithm (OFFER-06) requires no extra DB tables: given project_offers.start_date and offer_micros.duration_months, each project_offer generates revenue in months [start_month, start_month + duration_months). The accepted_total from the project_offers row (not from the project) drives the monthly amount — this is the "offer-level accepted total", distinct from projects.accepted_total.

Primary recommendation: Follow the established pattern — "use server" actions + revalidatePath + router.refresh() in client components. Add no new libraries for this phase; the existing shadcn/ui select.tsx is sufficient for the single-select assignment form, and a controlled checkbox list covers the multi-select service assignment. Radix @radix-ui/react-checkbox (v1.3.3 available) is the only optional new install if a styled checkbox component is desired.

---

Architectural Responsibility Map

Capability	Primary Tier	Secondary Tier	Rationale
Offer catalog CRUD (macro/micro)	API / Backend (Server Actions)	Admin Frontend (RSC + Client form)	All writes go through "use server" actions with session guard
Offer services CRUD	API / Backend (Server Actions)	Admin Frontend	Same as service catalog pattern
Service-to-micro assignment (M2M)	API / Backend (Server Actions)	Admin Frontend (checkbox list)	Junction table writes in single server action
Project offer assignment	API / Backend (Server Actions)	Admin Frontend	Extends project workspace tab
Client dashboard offer display	Frontend Server (RSC)	—	getProjectView() extension; never exposes internal names or prices
Revenue forecast computation	API / Backend (query function)	Admin Frontend (RSC)	Pure JS over DB query result; no client-side computation

---

Standard Stack

Core (already installed — no new installs required)

Library	Version	Purpose	Why Standard
drizzle-orm	0.45.2	Schema definition, queries, relations	Project ORM — all schema work follows this
drizzle-kit	0.31.10	drizzle-kit push migration	Existing migration workflow
next	16.2.6	App Router, Server Actions	Project framework
zod	4.4.3	Server action input validation	Used in every existing action
nanoid	5.1.11	ID generation	Used for all PKs
@radix-ui/react-select	2.2.6	Single-select dropdown	Already installed
lucide-react	1.14.0	Icons	Already installed

[VERIFIED: /Users/simonecavalli/IAMCAVALLI/package.json]

Optional (multi-select checkbox styling only)

Library	Version	Purpose	When to Use
@radix-ui/react-checkbox	1.3.3	Styled checkbox primitive	Only if the bare HTML <input type="checkbox"> looks too unstyled; the project uses Radix primitives for all interactive elements

[VERIFIED: npm registry]

Alternatives Considered

Instead of	Could Use	Tradeoff
Checkbox list for multi-select	cmdk Command palette	Command palette adds a dependency and is overkill for a 5-20 item list; checkbox list is simpler and consistent with project style
Separate offer_services table	Re-use service_catalog	Wrong: service_catalog is for quoted line-item prices; offer_services carry marketing transformation descriptions — different semantics

Installation (if checkbox component needed):

npm install @radix-ui/react-checkbox@1.3.3

---

Architecture Patterns

System Architecture Diagram

Admin Browser
    |
    | Server Action (POST)
    v
offer-actions.ts  ─────────────────────────────────────┐
    |                                                    |
    | db.insert / db.update / db.delete                 |
    v                                                    |
Postgres (Neon/Coolify)                                 |
  offer_macros                                           |
  offer_micros ──FK──> offer_macros                     |
  offer_services                                         |
  offer_micro_services ──FK──> offer_micros             |
                           └──FK──> offer_services      |
  project_offers ──FK──> projects                       |
                     └──FK──> offer_micros              |
                                                        |
Admin RSC Pages                                         |
  /admin/offers            ← catalog management         |
  /admin/projects/[id]     ← OffersTab (new tab) ──────┘
  /admin/clients/[id]      ← active offers badge
  /admin/forecast          ← revenue forecast
    |
Client RSC Page
  /client/[token]          ← OffersSection (read-only, public names only)
      |
      | getProjectView() extended — no internal names, no unit prices
      v
  ClientDashboard component

Recommended Project Structure

src/
├── app/
│   └── admin/
│       ├── offers/
│       │   └── page.tsx              # Offer catalog (macro + micro + services)
│       ├── forecast/
│       │   └── page.tsx              # Revenue forecast 12 months
│       └── projects/[id]/page.tsx    # Add OffersTab here
├── components/
│   └── admin/
│       ├── tabs/
│       │   └── OffersTab.tsx         # Assign micro-offers to project + list
│       └── offers/
│           ├── MacroOfferForm.tsx    # Create/edit macro-offer
│           ├── MicroOfferForm.tsx    # Create/edit micro-offer (child of macro)
│           ├── OfferServiceForm.tsx  # Create/edit offer service
│           ├── ServiceAssignForm.tsx # Multi-select checkbox list for micro→services
│           └── ForecastTable.tsx    # 12-month breakdown table
└── lib/
    ├── offer-queries.ts              # All offer-related read queries
    └── forecast-queries.ts           # Revenue forecast computation

[VERIFIED: mirrors structure of existing catalog/, tabs/, components/admin/]

Pattern 1: Schema — Four New Tables

// Source: /Users/simonecavalli/IAMCAVALLI/src/db/schema.ts (existing pattern)

export const offer_macros = pgTable("offer_macros", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  sort_order: integer("sort_order").notNull().default(0),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export const offer_micros = pgTable("offer_micros", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  macro_id: text("macro_id").notNull().references(() => offer_macros.id, { onDelete: "cascade" }),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  duration_months: integer("duration_months").notNull().default(1),
  sort_order: integer("sort_order").notNull().default(0),
});

export const offer_services = pgTable("offer_services", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  price: numeric("price", { precision: 10, scale: 2 }).notNull(),
  transformation_description: text("transformation_description"),
  active: boolean("active").notNull().default(true),
});

// Junction table — many micro-offers <-> many services
export const offer_micro_services = pgTable("offer_micro_services", {
  micro_id: text("micro_id").notNull().references(() => offer_micros.id, { onDelete: "cascade" }),
  service_id: text("service_id").notNull().references(() => offer_services.id, { onDelete: "cascade" }),
}, (t) => ({
  pk: primaryKey({ columns: [t.micro_id, t.service_id] }),
}));

// Project assignment
export const project_offers = pgTable("project_offers", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
  micro_id: text("micro_id").notNull().references(() => offer_micros.id, { onDelete: "restrict" }),
  start_date: timestamp("start_date", { withTimezone: true }).notNull().defaultNow(),
  // Offer-level accepted total — separate from projects.accepted_total
  // This is what the client pays for THIS specific offer bundle
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

[VERIFIED: schema.ts naming conventions, nanoid PK pattern, pgTable from drizzle-orm/pg-core]

Important: offer_micro_services uses a composite primary key — Drizzle primaryKey() import comes from drizzle-orm/pg-core. [VERIFIED: Drizzle ORM docs — composite PK syntax]

Pattern 2: Server Action Guard (existing pattern)

// Source: /Users/simonecavalli/IAMCAVALLI/src/app/admin/catalog/actions.ts
"use server";

async function requireAdmin() {
  const session = await getServerSession(authOptions);
  if (!session) throw new Error("Non autorizzato");
}

All offer server actions must call requireAdmin() as first line. [VERIFIED: catalog/actions.ts, project-actions.ts]

Pattern 3: Multi-Select Service Assignment (checkbox list)

No cmdk or Combobox needed. The project has at most ~20 offer services. Use a controlled checkbox list:

// Source: pattern derived from ServiceTable.tsx + QuoteTab.tsx interaction model
// "use client"
function ServiceCheckboxList({
  allServices,
  assignedIds,
  microId,
}: {
  allServices: OfferService[];
  assignedIds: string[];
  microId: string;
}) {
  const [selected, setSelected] = useState(new Set(assignedIds));
  const [, startTransition] = useTransition();
  const router = useRouter();

  function toggle(serviceId: string) {
    const next = new Set(selected);
    if (next.has(serviceId)) next.delete(serviceId);
    else next.add(serviceId);
    setSelected(next);
    startTransition(async () => {
      await updateMicroOfferServices(microId, [...next]);
      router.refresh();
    });
  }

  return (
    <div className="space-y-2">
      {allServices.map((svc) => (
        <label key={svc.id} className="flex items-center gap-2 cursor-pointer">
          <input
            type="checkbox"
            checked={selected.has(svc.id)}
            onChange={() => toggle(svc.id)}
            className="rounded"
          />
          <span className="text-sm">{svc.name}</span>
          <span className="text-xs text-[#71717a] ml-auto">
            €{parseFloat(svc.price).toFixed(2)}
          </span>
        </label>
      ))}
    </div>
  );
}

[VERIFIED: pattern matches existing QuoteTab useTransition + router.refresh() approach]

Pattern 4: Revenue Forecast Algorithm

// Source: analytics-queries.ts existing SQL+JS pattern
// No new DB tables needed — pure computation from project_offers

export type ForecastMonth = {
  year: number;
  month: number;        // 1-12
  label: string;        // "Giu 2026"
  total: number;        // sum of offer revenue expected in that month
};

export async function getRevenueForecast12Months(): Promise<ForecastMonth[]> {
  // Load all active project_offers with micro duration
  const offers = await db
    .select({
      project_id: project_offers.project_id,
      start_date: project_offers.start_date,
      duration_months: offer_micros.duration_months,
      accepted_total: project_offers.accepted_total,
    })
    .from(project_offers)
    .innerJoin(offer_micros, eq(project_offers.micro_id, offer_micros.id));

  // Build 12-month bucket array starting from current month
  const now = new Date();
  const buckets: ForecastMonth[] = Array.from({ length: 12 }, (_, i) => {
    const d = new Date(now.getFullYear(), now.getMonth() + i, 1);
    return {
      year: d.getFullYear(),
      month: d.getMonth() + 1,
      label: d.toLocaleDateString("it-IT", { month: "short", year: "numeric" }),
      total: 0,
    };
  });

  for (const offer of offers) {
    if (!offer.accepted_total) continue;
    const total = parseFloat(offer.accepted_total);
    const perMonth = total / offer.duration_months;
    const start = new Date(offer.start_date);

    for (let m = 0; m < offer.duration_months; m++) {
      const offerMonth = new Date(start.getFullYear(), start.getMonth() + m, 1);
      const bucket = buckets.find(
        (b) => b.year === offerMonth.getFullYear() && b.month === offerMonth.getMonth() + 1
      );
      if (bucket) bucket.total += perMonth;
    }
  }

  return buckets;
}

[VERIFIED: pattern mirrors getMonthlyCollected() from analytics-queries.ts; no SQL GROUP BY needed since computation is JS-side]

Pattern 5: Client Dashboard Offer Section (safe exposure)

The getProjectView() function in client-view.ts must be extended with a new field activeOffers. This field exposes ONLY:

• public_name (from offer_micros.public_name) — NOT internal_name
• cumulative_price (sum of offer_services.price for services assigned to that micro) — NOT individual service prices
• accepted_total (from project_offers.accepted_total) — already how payments work

// Extension to ProjectView interface in client-view.ts
activeOffers?: Array<{
  id: string;
  public_name: string;           // micro offer public name only
  cumulative_price: string;      // sum of all service prices in the micro-offer
  accepted_total: string | null; // offer-level accepted total, shown prominently
}>;

[VERIFIED: client-view.ts security pattern — amount intentionally excluded from payments, same discipline applied here]

Pattern 6: Admin Project Page — Adding an Offers Tab

/admin/projects/[id]/page.tsx uses <Tabs>. Adding an "Offerte" tab follows the exact same structure as the existing tabs:

// In /admin/projects/[id]/page.tsx
<TabsTrigger value="offers">Offerte</TabsTrigger>
// ...
<TabsContent value="offers">
  <OffersTab projectId={id} projectOffers={projectOffers} availableMicros={availableMicros} />
</TabsContent>

The getProjectFullDetail() query needs one additional parallel query for project_offers. [VERIFIED: existing tab pattern in /admin/projects/[id]/page.tsx]

Anti-Patterns to Avoid

• Re-using service_catalog for offer services: service_catalog has unit_price semantics for quote line items. Offer services have a transformation_description for marketing copy and are bundled into packages. Different entities, different tables.
• Storing cumulative price in DB: Compute cumulative_price at query time (SUM of offer_services.price joined via offer_micro_services). Never denormalize into offer_micros — it would go stale when services are edited.
• Exposing internal_name to client API: Every client-side query must select public_name explicitly, never SELECT * on offer tables.
• Computing forecast in the browser: The getRevenueForecast12Months() function runs server-side as a Server Component prop. No client-side fetch or useEffect.
• Using onDelete: "cascade" on project_offers.micro_id: Use onDelete: "restrict" — if an admin tries to delete a micro-offer that is actively assigned to projects, the DB should reject it with an error rather than silently destroying assignment history.

---

Don't Hand-Roll

Problem	Don't Build	Use Instead	Why
Multi-select UI	Custom dropdown with search	Checkbox list (plain HTML or Radix Checkbox)	5-20 items max; no search needed at this scale
Composite PK in junction table	Separate id column + unique constraint	Drizzle primaryKey({ columns: [t.micro_id, t.service_id] })	Drizzle natively supports composite PKs
Revenue forecast	Complex SQL window functions	JS loop over query results	The dataset is tiny (< 100 active offers); analytics-queries.ts already uses this pattern
Relation definitions	Raw SQL joins	Drizzle relations()	Existing pattern; enables type-safe .with() queries

Key insight: This phase is data-model heavy but UI-light. The hardest part is the schema design (junction table, composite PK, the start_date + duration_months forecast model), not the UI.

---

Common Pitfalls

Pitfall 1: Drizzle Composite PK import

What goes wrong: primaryKey is not imported from drizzle-orm — it must be imported from drizzle-orm/pg-core. Why it happens: drizzle-orm re-exports many things but primaryKey for table definitions is from the pg-core package. How to avoid: import { pgTable, primaryKey, text, ... } from "drizzle-orm/pg-core" — add primaryKey to the existing import in schema.ts. Warning signs: TypeScript error "primaryKey is not exported from drizzle-orm".

Pitfall 2: Offer accepted_total vs. project accepted_total

What goes wrong: Revenue forecast uses projects.accepted_total instead of project_offers.accepted_total. Why it happens: projects.accepted_total is the quote-builder total (from Phase 3 CRUD). Offer assignments have their own accepted totals. How to avoid: project_offers table has its own accepted_total column. Forecast queries MUST join to project_offers.accepted_total, not projects.accepted_total. Warning signs: Forecast totals match the quote totals instead of offer totals.

Pitfall 3: start_date = NULL breaks forecast

What goes wrong: project_offers.start_date nullable + forecast query silently drops those rows. Why it happens: If start_date is nullable, active offers with no start date contribute nothing to forecast. How to avoid: Make start_date NOT NULL with .defaultNow() — admin sets it at assignment time. If needed, allow admin to edit it after assignment. Warning signs: Forecast shows 0 even with active offers.

Pitfall 4: Client API security — internal names leaking

What goes wrong: A query accidentally includes internal_name in the client-side response. Why it happens: Using ...spread or SELECT * on offer tables in getProjectView(). How to avoid: In getProjectView(), always use explicit column selection: offer_micros.public_name, project_offers.accepted_total — never SELECT * on tables with both internal and public name columns. Warning signs: Client dashboard shows internal names like "Entry A" instead of "Entry Offer — Starter".

Pitfall 5: Drizzle push order — tables with circular deps

What goes wrong: drizzle-kit push fails if tables reference each other out of order. Why it happens: offer_micro_services references both offer_micros and offer_services — both must exist first. How to avoid: Define in schema.ts in this order: offer_macros → offer_micros → offer_services → offer_micro_services → project_offers. drizzle-kit push respects definition order. Warning signs: relation "offer_micros" does not exist error during push.

Pitfall 6: Forecast page route collision with existing /admin/analytics

What goes wrong: Naming the forecast page /admin/analytics when that route already exists. Why it happens: Existing /admin/analytics/page.tsx is the financial statistics page. How to avoid: Use /admin/forecast or add a tab to the existing analytics page. Recommended: new route /admin/forecast to keep concerns separated. Warning signs: The analytics page gets replaced.

---

Code Examples

Drizzle composite PK (junction table)

// Source: Drizzle ORM docs — https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key
import { pgTable, primaryKey, text } from "drizzle-orm/pg-core";

export const offer_micro_services = pgTable(
  "offer_micro_services",
  {
    micro_id: text("micro_id").notNull().references(() => offer_micros.id, { onDelete: "cascade" }),
    service_id: text("service_id").notNull().references(() => offer_services.id, { onDelete: "cascade" }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.micro_id, t.service_id] }),
  })
);

[CITED: https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key]

Cumulative price query (server-side)

// Sum of all services assigned to a micro-offer
const rows = await db
  .select({
    micro_id: offer_micro_services.micro_id,
    cumulative_price: sql<string>`coalesce(sum(${offer_services.price}::numeric), 0)`,
  })
  .from(offer_micro_services)
  .innerJoin(offer_services, eq(offer_micro_services.service_id, offer_services.id))
  .where(inArray(offer_micro_services.micro_id, microIds))
  .groupBy(offer_micro_services.micro_id);

[VERIFIED: mirrors analytics-queries.ts SQL aggregate patterns]

revalidatePath strategy for offer mutations

// After any offer catalog mutation:
revalidatePath("/admin/offers");

// After project offer assignment:
revalidatePath(`/admin/projects/${projectId}`);
revalidatePath("/admin/forecast");
// Do NOT revalidate /client/[token] — client pages use revalidate = 0

[VERIFIED: existing revalidatePath usage in catalog/actions.ts, project-actions.ts]

---

State of the Art

Old Approach	Current Approach	When Changed	Impact
quote_items exposed to client	accepted_total only	Phase 1 (locked)	Offer display must follow same discipline
Single project per client	Multi-project (projects table)	Phase 4	project_offers links to projects.id, not clients.id
Timer/payments on clients	Timer/payments on projects	Phase 4	Offers also scope to projects, not clients

---

Schema Migration Strategy

The four new tables are purely additive. The migration:

1. Adds offer_macros, offer_micros, offer_services, offer_micro_services, project_offers
2. Does NOT modify any existing table
3. Does NOT drop or truncate any existing data

[VERIFIED: CLAUDE.md Data Safety constraint — migrations must only add columns/tables, never drop]

Command after schema.ts is updated:

npx drizzle-kit push

---

Navigation Integration

Current NavBar links: Clienti | Progetti | Statistiche | Catalogo | Impostazioni

Recommended addition: Add "Offerte" between "Catalogo" and "Impostazioni", and "Forecast" after "Statistiche". This keeps catalog-type items together.

Final NavBar: Clienti | Progetti | Statistiche | Forecast | Catalogo | Offerte | Impostazioni

Alternatively, "Forecast" can live under "Statistiche" as a tab, avoiding NavBar clutter. Since the analytics page already uses year-based filtering, adding a "Forecast" tab to /admin/analytics is a viable option.

[ASSUMED] — Which navigation placement is preferable is a product decision. Both options work technically.

---

Assumptions Log

#	Claim	Section	Risk if Wrong
A1	Offer-level accepted_total on project_offers is separate from projects.accepted_total	Schema Design	If the intent is that projects.accepted_total also covers offers, the schema design changes; the revenue forecast would use a different source
A2	"Forecast" gets its own page at /admin/forecast rather than a tab in /admin/analytics	Navigation Integration	If admin prefers a tab, the page structure changes but not the algorithm
A3	Offer services have a flat price (not quantity × unit_price like quote_items)	Schema Design	If offer services need quantity support, offer_micro_services needs a quantity column

---

Open Questions (RESOLVED)

1. Should project_offers.accepted_total be mandatory or optional?

   • What we know: projects.accepted_total defaults to "0"; offer assignment may happen before price negotiation
   • What's unclear: Can admin assign a micro-offer with no accepted total yet? Does it appear in forecast as 0?
   • RESOLVED: nullable — exclude from forecast if null
   • Recommendation: Make it nullable (accepted_total: numeric(...)), exclude null rows from forecast computation

2. Can a project have the same micro-offer assigned twice (e.g., a retainer renewed)?

   • What we know: The project_offers table as designed allows duplicate (project_id, micro_id) combinations
   • What's unclear: Is renewal a new row (different start_date) or an update?
   • RESOLVED: allowed — differentiated by start_date, no unique constraint
   • Recommendation: Allow duplicate rows (multiple assignments of same micro to same project), differentiated by start_date; no unique constraint on (project_id, micro_id)

3. Where does the "Offerte" catalog page live — merged with existing /admin/catalog, or separate?

   • What we know: /admin/catalog handles service_catalog; offer entities are distinct
   • What's unclear: Admin preference for navigation
   • RESOLVED: separate /admin/offers page
   • Recommendation: Separate page /admin/offers keeps concerns clean; existing /admin/catalog stays for quote service catalog

---

Environment Availability

Step 2.6: SKIPPED — no new external dependencies identified. All required tools (Node.js, npm, Postgres, drizzle-kit) are already verified operational from Phase 4.

---

Validation Architecture

nyquist_validation: false in .planning/config.json — section omitted per config.

[VERIFIED: /Users/simonecavalli/IAMCAVALLI/.planning/config.json]

---

Security Domain

Applicable ASVS Categories

ASVS Category	Applies	Standard Control
V2 Authentication	yes	requireAdmin() guard in every server action — already established pattern
V3 Session Management	yes	Auth.js v4 session; no changes needed
V4 Access Control	yes	Client API (getProjectView) must never return internal_name or individual offer_services.price
V5 Input Validation	yes	zod schemas in all server actions (established pattern)
V6 Cryptography	no	No new cryptographic operations

Known Threat Patterns

Pattern	STRIDE	Standard Mitigation
Client reads internal offer names via /api/client/*	Information Disclosure	Explicit column selection in getProjectView() — never SELECT * on offer tables
Admin accesses offer CRUD without session	Elevation of Privilege	requireAdmin() as first call in every server action
Cascade delete of micro-offer deletes project_offer history	Tampering	onDelete: "restrict" on project_offers.micro_id — prevents deletion of in-use micros

---

Sources

Primary (HIGH confidence)

• /Users/simonecavalli/IAMCAVALLI/src/db/schema.ts — full schema inspected
• /Users/simonecavalli/IAMCAVALLI/src/lib/admin-queries.ts — all query patterns verified
• /Users/simonecavalli/IAMCAVALLI/src/lib/client-view.ts — client API security model verified
• /Users/simonecavalli/IAMCAVALLI/src/app/admin/catalog/actions.ts — server action pattern verified
• /Users/simonecavalli/IAMCAVALLI/src/lib/analytics-queries.ts — SQL aggregate + JS computation pattern verified
• /Users/simonecavalli/IAMCAVALLI/package.json — dependencies and versions verified
• /Users/simonecavalli/IAMCAVALLI/.planning/config.json — workflow config verified

Secondary (MEDIUM confidence)

• Drizzle ORM composite primary key syntax — https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key [CITED]
• npm registry — @radix-ui/react-checkbox@1.3.3, @radix-ui/react-dialog@1.1.15, @radix-ui/react-popover@1.1.15 versions [VERIFIED]

Tertiary (LOW confidence)

• None

---

Metadata

Confidence breakdown:

• Standard stack: HIGH — all packages verified from package.json
• Schema design: HIGH — derived directly from existing schema.ts patterns and Drizzle docs
• Architecture patterns: HIGH — derived from direct codebase inspection
• Revenue forecast algorithm: HIGH — mirrors existing analytics-queries.ts pattern
• Client security model: HIGH — directly reading client-view.ts with explicit column exclusions

Research date: 2026-05-30 Valid until: 2026-06-30 (stable stack — Next.js 16, Drizzle, Radix; no fast-moving dependencies)