saqib mirandClaude Sonnet 5
8d5731340d
Implements tasks T001-T020 from specs/002-saas-integration/tasks.md (Setup, Foundational, and User Story 1 - the P1 MVP: every inbound request is authenticated and trusted before anything happens). User Story 2 (admin onboarding/rotation/revocation) and User Story 3 (rate limiting) are not yet implemented (T021-T032 remain). Schema (prisma/schema.prisma + initial migration): - Replace the placeholder Product model (leftover starter-template scaffolding: code/description/ProductStatus enum) with the real docs/06-database-schema.md shape (externalProductId, supportEnabled, status). - Add ProductIntegration (credential ref, rotation/revocation state, allowed scope, per-integration/per-user rate limits) and CustomerReference models. - Align AuditLog to docs/06's shape (actor/actorType/entityType/ entityId/reason/metadata) -- the placeholder shape had no fields to satisfy this feature's audit requirements. Auth: - HMAC-signed short-lived tokens (issue/verify) with jti-based replay defense via Redis and a bounded clock-skew tolerance. - Credential secrets are AES-256-GCM encrypted at rest (new required INTEGRATION_CREDENTIAL_ENCRYPTION_KEY env var) since no secret manager exists in this stack yet -- see research.md "Credential storage". - New product-integration-auth.plugin.ts Fastify plugin runs the validation order in contracts/inbound-request-contract.md and populates request.reqContext only on full success; every attempt (success or failure) is audit-logged without ever persisting the raw token/credential. Unregistered product and invalid credential return an identical response (FR-010). - New POST /v1/support/requests endpoint exercises the boundary end-to-end (ticket creation itself is a future feature). Also: - Fix docker-compose.test.yml's container_name collisions -- discovered while testing this change concurrently is now covered by an app-level regression test (separate commit). - Fix test:unit to scope to tests/unit only (it was running the entire tests/** glob including integration tests) -- this feature's new integration test makes real Prisma/Redis calls, unlike the prior instantiation-only checks, so the existing glob-scoping gap became actually harmful. - Update Jenkinsfile with the new required credential. Verified: full quality gate (typecheck/lint/format/architecture/ unit tests) passes; all of User Story 1's quickstart scenarios manually verified end-to-end against a live server + Postgres + Redis; the new integration test suite verified against a live database (not run as part of `npm test`, matches existing test:integration convention). Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
104 lines
3.2 KiB
SQL
104 lines
3.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('ADMIN', 'AGENT', 'CUSTOMER');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "users" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"role" "UserRole" NOT NULL DEFAULT 'CUSTOMER',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "products" (
|
|
"id" TEXT NOT NULL,
|
|
"externalProductId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"supportEnabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "products_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_integrations" (
|
|
"id" TEXT NOT NULL,
|
|
"productId" TEXT NOT NULL,
|
|
"credentialRef" TEXT NOT NULL,
|
|
"previousCredentialRef" TEXT,
|
|
"previousCredentialExpiresAt" TIMESTAMP(3),
|
|
"authMechanism" TEXT NOT NULL DEFAULT 'signed_token',
|
|
"allowedScope" JSONB NOT NULL,
|
|
"rateLimitPerMinute" INTEGER NOT NULL DEFAULT 60,
|
|
"rateLimitPerUserPerMinute" INTEGER NOT NULL DEFAULT 20,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"rotatedAt" TIMESTAMP(3),
|
|
"revokedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "product_integrations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "customer_references" (
|
|
"id" TEXT NOT NULL,
|
|
"externalUserId" TEXT NOT NULL,
|
|
"externalTenantId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "customer_references_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "categories" (
|
|
"id" TEXT NOT NULL,
|
|
"productId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "audit_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"actor" TEXT NOT NULL,
|
|
"actorType" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"entityType" TEXT NOT NULL,
|
|
"entityId" TEXT NOT NULL,
|
|
"oldValue" JSONB,
|
|
"newValue" JSONB,
|
|
"reason" TEXT,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "products_externalProductId_key" ON "products"("externalProductId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_integrations_productId_key" ON "product_integrations"("productId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "customer_references_externalUserId_externalTenantId_key" ON "customer_references"("externalUserId", "externalTenantId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_integrations" ADD CONSTRAINT "product_integrations_productId_fkey" FOREIGN KEY ("productId") REFERENCES "products"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "categories" ADD CONSTRAINT "categories_productId_fkey" FOREIGN KEY ("productId") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|