Tyler
d0a12d33da
Deploy to route.crispygoat.com / deploy (push) Failing after 3m19s
Two related bugs caused the 'New Template' modal on
/admin/communications to fail with a database error:
1. The `email_templates` table was defined in `db/schema/marketing.ts`
(Drizzle) but never migrated, so the modal's `upsertTemplate`
server action errored with 'relation "email_templates" does not
exist'. Adds migration 0092 to create the table (and `campaigns`)
to match the Drizzle schema. Uses CREATE TABLE IF NOT EXISTS,
gen_random_uuid() PKs, and the standard set_updated_at trigger
pattern from 0001_init.sql.
2. `/admin/communications/page.tsx` resolved `effectiveBrandId`
with a hard-coded UUID ('64294306-...') for sessions without a
brand (e.g., dev platform_admin). That UUID isn't in the local
dev DB (brands are '11111111-...' and '22222222-...'), so the
modal would FK-violate on insert with 'violates foreign key
constraint email_templates_brand_id_fkey'. Replaced with a
`listBrandsForAdmin()` lookup that picks the first accessible
brand.
3. `listBrandsForAdmin()` (`src/actions/brands.ts`) was SELECTing
`logo_url` from `brands`, but that column doesn't exist (the
brands table only has id/name/slug/plan_tier/limits/stripe_*/...).
The query threw 'column "logo_url" does not exist', got caught
by the silent try/catch, returned [], which made #2 above render
the page with brandId="" — and any submit would then error with
'invalid input syntax for type uuid: ""'. Removed logo_url
from the SELECT; BrandListItem.logo_url is always null until a
future migration adds the column.
E2E verified: opening /admin/communications → Templates tab → New
Template → fill name → Create Template inserts a row in
email_templates with the correct brand_id.
87 lines
3.2 KiB
PL/PgSQL
87 lines
3.2 KiB
PL/PgSQL
-- 0092_email_templates_and_campaigns.sql
|
|
--
|
|
-- Creates the `email_templates` and `campaigns` tables referenced by
|
|
-- `db/schema/marketing.ts` (Drizzle). These tables were defined in the
|
|
-- schema but never migrated, so the "Create Template" UI on the
|
|
-- communications page errored with `relation "email_templates" does
|
|
-- not exist`.
|
|
--
|
|
-- A parallel set of tables (`communication_templates`,
|
|
-- `communication_campaigns`) already exists from 0001_init.sql — those
|
|
-- are a different schema used by the Harvest Reach module and are
|
|
-- untouched here. This migration only adds the marketing-schema tables
|
|
-- the `upsertTemplate` / `createCampaign` actions need.
|
|
--
|
|
-- Conventions (mirror 0001_init.sql):
|
|
-- - gen_random_uuid() for PKs
|
|
-- - TIMESTAMPTZ for timestamps
|
|
-- - TEXT + CHECK for the campaigns.status enum
|
|
-- - CREATE TABLE IF NOT EXISTS for re-runnability
|
|
-- - set_updated_at() trigger for updated_at maintenance
|
|
|
|
BEGIN;
|
|
|
|
-- ============================================================================
|
|
-- email_templates
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS email_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
brand_id UUID NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
subject TEXT NOT NULL,
|
|
body_html TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_trigger t
|
|
JOIN pg_class c ON t.tgrelid = c.oid
|
|
JOIN pg_namespace n ON c.relnamespace = n.oid
|
|
WHERE t.tgname = 'email_templates_set_updated_at' AND n.nspname = current_schema()
|
|
) THEN
|
|
CREATE TRIGGER email_templates_set_updated_at
|
|
BEFORE UPDATE ON email_templates
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS email_templates_brand_idx ON email_templates (brand_id);
|
|
|
|
-- ============================================================================
|
|
-- campaigns
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS campaigns (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
brand_id UUID NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
|
|
template_id UUID REFERENCES email_templates(id) ON DELETE SET NULL,
|
|
name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft'
|
|
CHECK (status IN ('draft', 'scheduled', 'sending', 'sent', 'canceled')),
|
|
scheduled_for TIMESTAMPTZ,
|
|
sent_at TIMESTAMPTZ,
|
|
recipient_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_trigger t
|
|
JOIN pg_class c ON t.tgrelid = c.oid
|
|
JOIN pg_namespace n ON c.relnamespace = n.oid
|
|
WHERE t.tgname = 'campaigns_set_updated_at' AND n.nspname = current_schema()
|
|
) THEN
|
|
CREATE TRIGGER campaigns_set_updated_at
|
|
BEFORE UPDATE ON campaigns
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS campaigns_brand_idx ON campaigns (brand_id);
|
|
CREATE INDEX IF NOT EXISTS campaigns_status_idx ON campaigns (brand_id, status);
|
|
|
|
COMMIT; |