-- Notification templates: HTML email bodies + placeholder metadata
CREATE TABLE IF NOT EXISTS notification_templates (
id BIGSERIAL PRIMARY KEY,
code VARCHAR(80) NOT NULL UNIQUE,
name VARCHAR(200) NOT NULL,
channel VARCHAR(20) NOT NULL DEFAULT 'EMAIL',
subject VARCHAR(500) NOT NULL,
html_body TEXT NOT NULL,
placeholders JSONB NOT NULL DEFAULT '[]'::jsonb,
description TEXT,
is_active BOOLEAN NOT NULL DEFAULT true,
created_by BIGINT,
updated_by BIGINT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_notification_templates_channel ON notification_templates (channel);
CREATE INDEX IF NOT EXISTS idx_notification_templates_is_active ON notification_templates (is_active);
INSERT INTO notification_templates (
code,
name,
channel,
subject,
html_body,
placeholders,
description,
is_active
)
VALUES (
'PO_SUBMIT_APPROVAL',
'Purchase Order Submitted for Approval',
'EMAIL',
'PO {{po_number}} submitted for approval',
'
Hello {{approver_name}},
Purchase order {{po_number}} has been submitted and is waiting for your approval.
| PO Number |
{{po_number}} |
| PO Date |
{{po_date}} |
| Vendor |
{{vendor_name}} |
| Grand Total |
{{grand_total}} |
| Submitted By |
{{submitted_by}} |
| Remarks |
{{remarks}} |
Review Purchase Order
If the button does not work, open this link:
{{po_link}}
',
'[
{"key": "approver_name", "description": "Approver full name"},
{"key": "po_number", "description": "Purchase order number"},
{"key": "po_date", "description": "Purchase order date"},
{"key": "vendor_name", "description": "Vendor name"},
{"key": "grand_total", "description": "PO grand total"},
{"key": "submitted_by", "description": "User who submitted the PO"},
{"key": "remarks", "description": "PO remarks"},
{"key": "po_link", "description": "Frontend deep link to the PO"}
]'::jsonb,
'Sent to users with PURCHASE_ORDER approve permission when a PO is submitted',
true
)
ON CONFLICT (code) DO UPDATE SET
name = EXCLUDED.name,
channel = EXCLUDED.channel,
subject = EXCLUDED.subject,
html_body = EXCLUDED.html_body,
placeholders = EXCLUDED.placeholders,
description = EXCLUDED.description,
is_active = true,
deleted_at = NULL,
updated_at = NOW();
INSERT INTO notification_templates (
code,
name,
channel,
subject,
html_body,
placeholders,
description,
is_active
)
VALUES (
'FORGOT_PASSWORD',
'Forgot Password Reset',
'EMAIL',
'Reset your ERP password',
'
Hello {{user_name}},
We received a request to reset your password.
Click here to reset your password
This link expires in {{expiry_minutes}} minutes.
If the button does not work, open this link:
{{reset_url}}
If you did not request this, you can ignore this email.
',
'[
{"key": "user_name", "description": "User full name"},
{"key": "reset_url", "description": "Password reset frontend URL with token"},
{"key": "expiry_minutes", "description": "Reset link expiry in minutes"}
]'::jsonb,
'Sent when a user requests a password reset via forgot-password',
true
)
ON CONFLICT (code) DO UPDATE SET
name = EXCLUDED.name,
channel = EXCLUDED.channel,
subject = EXCLUDED.subject,
html_body = EXCLUDED.html_body,
placeholders = EXCLUDED.placeholders,
description = EXCLUDED.description,
is_active = true,
deleted_at = NULL,
updated_at = NOW();