Strat Planner Pro — Developer Documentation

1

Overview

Platform scope, planning lifecycle, and Philippine public sector context

🌏

Philippine Public Sector Context

Strat Planner Pro is purpose-built for the Philippine public sector, using terminology and frameworks aligned with national planning standards — Programs, Activities & Projects (PAPs), Monitoring, Evaluation & Learning (MEL), and the Balanced Scorecard (BSC) with four perspectives.

The platform guides organizations through a structured planning lifecycle:

SWOT Analysis → Strategic Options → BSC Objectives → KPIs → PAPs → MEL Monitoring

📊

Strategic Planning

Full strategic planning workflow combining SWOT, TOWS Matrix, Balanced Scorecard, KPI tracking, PAP management, and MEL logging — all integrated in a single data model.

📧

Integrated CRM

Contacts, email campaigns, automation flows, and appointment scheduling — sharing the same Supabase project and authentication layer as the planning module.

2

Key Features

Strategic planning, collaboration, templates, AI, and CRM capabilities

📋

Strategic Planning Modules

SWOT · Strategy Matrix · BSC · KPIs · PAPs · MEL

▾

Module	Description
SWOT Analysis	Score items by impact and likelihood; AI-generated entries flagged with badge
Strategic Options Matrix	Auto-generate SO, ST, WO, WT strategies from SWOT data
Balanced Scorecard	Objectives across Financial, Customer, Internal Process, and Learning & Growth
KPI Tracking	Baseline → Target → Current value tracking with on-track / at-risk / delayed / completed statuses
PAP Management	Programs, Activities & Projects — budget, spend, progress %, and date tracking
MEL Logs	Monitoring, Evaluation & Learning entries linked to specific KPIs and PAPs

👥

Collaboration & Multi-Tenancy

Organizations, teams, permissions, real-time presence

▾

Organizations & Teams

Multi-user orgs with viewer / editor / admin / owner roles. Plan sharing to individuals or entire organizations with permission controls.

Real-time & Audit

WebSocket presence indicators showing active viewers/editors. Threaded comments on any plan item. Full immutable audit log for every plan action.

Templates System

Built-in public templates by industry. User-saved templates. Org-shared templates. Community ratings (1–5 stars) with aggregated scores on template records.

AI Features

SWOT generation, strategy derivation, KPI suggestions, PAP generation, MEL insights, trend analysis, causal loop diagrams, and systems archetype detection.

📧

CRM Feature Set

Contacts, campaigns, automation flows, scheduling

▾

Feature	Details
Contact Management	Email marketing opt-ins, GIN-indexed tags, e-commerce linkage via `ecom_customer_id`
Mailing Lists	Static and dynamic (filter-query based) lists
Email Campaigns	Full HTML + plain-text, open/click/bounce tracking, status lifecycle: `draft → scheduled → sending → sent/failed`
Automation Flows	Trigger-based steps: `send_email`, `add_tag`, `add_to_list`
Appointment Scheduling	Round-robin, class, collective, personal calendar types. Google Calendar + Calendly integrations.

3

Architecture

System diagram, database schemas, and external service integrations

Frontend (React / Next.js — not in this repo)

🖥 Web Interface

📱 Mobile (PWA)

💡 HTTPS + JWT Auth

↕

Supabase Platform

🔐 Auth Layer (Custom)

🗄 PostgreSQL 15 (3 schemas)

🗂 Storage (Buckets)

↕

Deno Edge Functions

⚡ ai-strategy-assistant (v1–v4)

🔄 strategic-planner-sync (v1–v2)

📧 email-notifications (v1–v7)

☁ cloud-sync (v1)

↕

External Services

🤖 AI Gateway (LLM)

✉ Resend (Email)

📅 Google Calendar API

🗓 Calendly API

🗃

Database Schemas

Three schema architecture with purpose separation

▾

Schema	Purpose
`prj_w80v7pKpQ8s2`	Main application — all planning and CRM tables
`prj_w80v7pKpQ8s2_auth`	Custom auth layer — users and OAuth identities
`prj_w80v7pKpQ8s2_storage`	File storage — buckets and objects

4

Database Schema

Entity relationships, core tables, and schema reference

📄

Full Schema SQL

The complete schema SQL is available in strategy_ai_planner_schema.sql. Apply it via the Supabase CLI or paste into the SQL Editor.

🔗

Entity Relationship Overview

High-level tree of all table relationships

▾

auth.users └── user_profiles └── strategic_plans ├── swot_items ├── strategic_options ├── bsc_objectives │ └── kpis │ └── mel_logs ├── paps ──────────── mel_logs ├── plan_shares ├── plan_comments (self-referencing, threaded) └── activity_log organizations └── organization_members └── plan_shares plan_templates ── template_ratings crm_contacts ├── crm_contact_lists ── crm_lists ── crm_campaigns ── crm_events └── crm_flow_logs crm_flows └── crm_flow_steps └── crm_flow_logs crm_calendars ├── crm_calendar_members ├── crm_availability ├── crm_appointments ── crm_contacts └── crm_calendly_connections

📐

Strategic Planning Tables

Root entity and all planning child records

▾

Table	Description
`strategic_plans`	Root entity. Owns all planning child records.
`swot_items`	SWOT items scored by impact (1–5) and likelihood (1–5).
`strategic_options`	SO / ST / WO / WT strategies derived from SWOT.
`bsc_objectives`	BSC objectives by perspective with weighting.
`kpis`	KPIs with baseline, target, and current values.
`paps`	Programs, Activities & Projects with budget and progress %.
`mel_logs`	Monitoring, Evaluation & Learning entries.

🤝

Collaboration & Template Tables

Organizations, permissions, comments, audit, templates

▾

Table	Description
`organizations`	Teams / agencies.
`organization_members`	Role-based org membership: `viewer`, `editor`, `admin`, `owner`
`plan_shares`	Grants access to a plan for a user or org. ⚠ VARCHAR(255) — see Known Issues
`plan_comments`	Threaded comments on any plan item (self-referencing).
`activity_log`	Immutable audit trail of plan actions.
`user_presence`	Real-time active viewer/editor tracking.
`plan_templates`	Reusable templates: `builtin`, `user`, `shared`
`template_ratings`	1–5 star ratings; one per user per template.

📇

CRM Tables

Contacts, lists, campaigns, flows, calendars, appointments

▾

Table	Description
`crm_contacts`	Contact records with email opt-in and e-commerce fields. Source: `form`, `ecom`, `import`, `manual`, `auth`. GIN-indexed `tags[]`.
`crm_lists`	Static or dynamic (filter-query) mailing lists.
`crm_contact_lists`	Many-to-many join of contacts and lists.
`crm_campaigns`	Email campaigns with send/open/click metrics. Status lifecycle: `draft → scheduled → sending → sent/failed`
`crm_events`	Per-contact engagement events: `sent`, `opened`, `clicked`, `bounced`, `unsubscribed`
`crm_flows`	Automation flows with trigger types.
`crm_flow_steps`	Ordered steps within a flow: `send_email`, `add_tag`, `add_to_list`
`crm_flow_logs`	Execution log: `executed`, `failed`, `skipped`
`crm_calendars`	Calendar types: `personal`, `round_robin`, `class`, `collective`. ⚠ Default TZ: America/New_York
`crm_appointments`	Booked appointments with Google/Calendly sync. Sources: `manual`, `public_link`, `google`, `calendly`
`crm_calendly_connections`	Encrypted Calendly OAuth credentials. Service-only — USING (false)

5

Edge Functions

Deno TypeScript functions — AI orchestration, sync, notifications

🦕

Deno Runtime

All functions are written in TypeScript and run on the Deno runtime via Supabase Edge Functions. Authentication is validated on every request and all data is scoped to the authenticated user.

ai-strategy-assistant

v1–v4 current

POST /functions/v1/ai-strategy-assistant

AI orchestration layer. Accepts a POST request with an action field and routes to the appropriate prompt builder. Every AI response is structured as JSON only (no markdown preamble), parsed and rendered directly by the frontend.

generate_swot generate_strategies generate_objectives generate_kpis generate_paps generate_insights analyze_trends suggest_archetypes build_ccd analyze_loops recommend_actions

📦

ai-strategy-assistant — Request Schema

Payload structure and authentication

▾

JSON

{
  "action": "generate_swot",
  "data": {
    "organization": "Bureau of Investments - MTIT",
    "industry": "Investment Promotion",
    "strategicIntent": "Attract FDI in priority sectors",
    "context": "Post-pandemic recovery, Islamic finance growth"
  },
  "plan": { } // Live plan context injected by client
}

🔐

Authentication

Requires a valid Supabase JWT in the Authorization header. Required env vars: GATEWAY_API_KEY, SUPABASE_URL, SUPABASE_ANON_KEY.

strategic-planner-sync

v1–v2 current

/functions/v1/strategic-planner-sync

Cloud sync for the full strategic planner state. Scopes all data to session.user.id. References a strategic_planner_state table — see Known Issues §2.

GET — Load state POST — Save state DELETE — Remove plan

email-notifications

v1–v7 current

via Resend · info@asilvainnovations.com

Transactional emails via Resend. Respects per-user preferences in notification_preferences on the user profile — returns 200 without sending if notification type is disabled. Queries user_profiles (note: not profiles — see Known Issues §5).

welcome kpi_alert weekly_digest stale_plan_reminder

cloud-sync

v1

/functions/v1/cloud-sync

General-purpose cloud sync for auxiliary state (non-plan data). Handles CORS and authentication in the same pattern as strategic-planner-sync.

6

AI Strategy Assistant

Domain expertise, context injection, and structured JSON output

✦

Grounded Context Injection

Every AI response includes the current plan's live context: organization, vision, mission, planning period, objectives count, PAP count, and KPI snapshot — ensuring all AI suggestions are grounded in the actual plan state rather than generic output.

🧠

Domain Knowledge Areas

Specialized training context for investment and development

▾

🌏 Global Investment Trends

Capital flows, macroeconomic analysis, FDI patterns across Southeast Asia, East Asia, South Asia

🕌 Islamic Finance

Sukuk markets, halal investment principles — relevant for Bangsamoro and BARMM planning

🏭 Investment Attraction

SEZs, PPPs, investment ecosystems, national/regional promotional strategies

🏦 Development Finance

ADB, IDB, World Bank frameworks — multi-lateral development bank project planning

⚙️

AI Actions Reference

All 11 supported actions with input/output description

▾

Action	Input	Output
`generate_swot`	Org context, industry, strategic intent, additional context	Scored SWOT items for all 4 quadrants
`generate_strategies`	Existing SWOT items	SO / ST / WO / WT strategic options
`generate_objectives`	BSC perspective, strategic goal	Suggested BSC objectives
`generate_kpis`	BSC objective text	Relevant, measurable KPI suggestions
`generate_paps`	Strategic objectives	Programs, Activities & Projects
`generate_insights`	Current performance data	MEL insights and observations
`analyze_trends`	Sector, region parameters	Investment & sector trend analysis
`suggest_archetypes`	Org dynamics description	Systems thinking archetypes with confidence
`build_ccd`	SWOT items + objectives	Causal loop diagram nodes and links
`analyze_loops`	CLD structure	Reinforcing and balancing loop analysis
`recommend_actions`	MEL insights	Prioritized action recommendations

7

CRM Module

Contact management, campaigns, automation flows, and scheduling

📇

Contact Management

Unique contacts, GIN-indexed tags, e-commerce linkage

▾

Unique contacts by email address — deduplication enforced at DB level
Source tracking: form, ecom, import, manual, auth
GIN-indexed tags[] array for fast tag-based filtering
E-commerce linkage: ecom_customer_id, total_orders, total_spent

⚠️

CRM RLS Gap — High Priority

All CRM tables currently use USING (true) — any authenticated user can read/write all CRM data. Scope to owner_user_id before multi-tenant production deployment. See Known Issues §1 for the fix SQL.

📧

Email Campaigns & Automation Flows

Campaign lifecycle and trigger-based automation

▾

Campaign Lifecycle

draft → scheduled → sending → sent

Automation Triggers

contact.subscribed order.placed contact.tagged user.registered appointment.booked

📅

Appointment Scheduling

Calendar types, availability, Google Calendar, Calendly

▾

Feature	Detail
Calendar Types	`personal`, `round_robin`, `class`, `collective`
Slot Configuration	Configurable slot duration, buffer times, max bookings per day, minimum notice
Availability	Weekly windows per calendar (0=Sunday … 6=Saturday)
Appointment Sources	`manual`, `public_link`, `google`, `calendly`
Google Sync	Via `google_refresh_token` on `crm_calendars`
Calendly Integration	Encrypted OAuth tokens in `crm_calendly_connections` — service-role only access

8

Security & Row Level Security

RLS policy tiers, JWT scoping, and service-only locks

🛡

Strategic Planning — User-Scoped (strict)

JWT sub claim scoping on all planning tables

▾

All core planning tables are scoped to the authenticated user's user_id via JWT claim:

SQL — strategic_plans RLS

-- Example: strategic_plans
USING ((current_setting('request.jwt.claim.sub', true))::uuid = user_id)

Child tables (bsc_objectives, kpis, paps, mel_logs, swot_items) use subquery policies that verify ownership via the parent strategic_plans record.

⚠️

CRM — Open Access (review required)

USING (true) policies — acceptable single-tenant only

▾

⚠️

Multi-Tenant Risk

All CRM tables use USING (true). Acceptable for single-tenant deployment but must be scoped to owner_user_id before multi-tenant production use.

SQL — Recommended CRM Fix

-- Recommended fix example for crm_contacts
CREATE POLICY "Users access own contacts"
  ON crm_contacts FOR ALL
  USING (owner_user_id = (current_setting('request.jwt.claim.sub', true))::uuid);

🔒

Calendly Connections — Service Only (locked)

USING (false) — no client access whatsoever

▾

SQL — crm_calendly_connections RLS

-- Blocks all client-side access
USING (false) WITH CHECK (false)

Calendly OAuth tokens are only accessible via the service role key in Edge Functions. Tokens are stored as encrypted_access_token and signing_key — must be encrypted at the application layer before insert.

9

Environment Variables

Required secrets for all Edge Functions

⚙️

Setting Secrets

Configure all secrets in the Supabase dashboard under Settings → Edge Functions → Secrets, or via the Supabase CLI using supabase secrets set KEY=value.

Variable

Used By

Description

SUPABASE_URL

All functions

Your Supabase project URL

SUPABASE_ANON_KEY

planner-sync, ai-assistant

Supabase anon key (client-facing)

SUPABASE_SERVICE_ROLE_KEY

email-notifications

Service role key — bypasses RLS. Handle with care.

GATEWAY_API_KEY

ai-strategy-assistant

API key for your AI gateway / LLM provider

RESEND_API_KEY

email-notifications

Resend transactional email API key

10

Getting Started

Prerequisites, schema deployment, Edge Functions, secrets, storage, and frontend config

📋

Prerequisites

Required tools before deployment

▾

Supabase CLI installed — supabase.com/docs/guides/cli
Deno v1.40+ installed — deno.land
Supabase project created at supabase.com

🚀

Deployment Steps

Schema → Functions → Secrets → Storage → Frontend

▾

1

Apply the Database Schema

Via CLI or paste strategy_ai_planner_schema.sql into the Supabase SQL Editor.

bash

supabase db push --db-url "postgresql://postgres:[PASSWORD]@[HOST]:5432/postgres" \
  < strategy_ai_planner_schema.sql

2

Deploy Edge Functions

bash

supabase functions deploy ai-strategy-assistant
supabase functions deploy strategic-planner-sync
supabase functions deploy email-notifications
supabase functions deploy cloud-sync

3

Set Environment Secrets

bash

supabase secrets set GATEWAY_API_KEY=your_key_here
supabase secrets set RESEND_API_KEY=your_key_here
supabase secrets set SUPABASE_SERVICE_ROLE_KEY=your_key_here

4

Create Storage Buckets

In the Supabase dashboard under Storage, create a public bucket named pending-tasks for app assets.

5

Configure the Frontend

.env.local

NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key

11

Known Issues & Recommendations

Active technical debt — prioritized for resolution

🔴 #1 — CRM RLS Gap High Priority

All CRM tables use USING (true). Any authenticated user can access all CRM data. If multiple organizations share this Supabase project, add an owner_user_id column and scope all CRM policies to it.

SQL — Fix

CREATE POLICY "Users access own contacts"
  ON crm_contacts FOR ALL
  USING (owner_user_id = (current_setting('request.jwt.claim.sub', true))::uuid);

🔴 #2 — Missing strategic_planner_state Table High Priority

The strategic-planner-sync function references a strategic_planner_state table for caching full plan snapshots, but this table is absent from the current schema dump.

SQL — Create Table

CREATE TABLE strategic_planner_state (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  state      jsonb NOT NULL DEFAULT '{}',
  updated_at timestamp with time zone DEFAULT now()
);

🟡 #3 — Encrypted Token Storage Medium Priority

crm_calendly_connections stores encrypted_access_token and signing_key as plain text columns. Ensure tokens are encrypted at the application layer before insert — do not rely solely on Supabase's at-rest encryption.

🟡 #4 — Default Timezone Medium Priority

crm_calendars defaults to America/New_York. Since this platform targets Philippine agencies, change the default to Asia/Manila.

🔵 #5 — Email Notification Profile Reference Low Priority

email-notifications queries a profiles table which does not exist in the schema — user_profiles is the correct table name. Verify the function references the right table name after any migrations.

🔵 #6 — plan_shares & plan_comments — UUID vs VARCHAR Low Priority

plan_id on plan_shares, plan_comments, activity_log, and user_presence is typed as VARCHAR(255) rather than UUID, while strategic_plans.id is a proper UUID. Align these types and add foreign key constraints for referential integrity.

12

Project Structure

Archive layout — database, functions, and storage

strategy-ai-planner/ ├── database.sql # Full PostgreSQL schema + seed data ├── functions/ │ ├── ai-strategy-assistant/ │ │ ├── v1/bundle.js │ │ ├── v2/bundle.js │ │ ├── v3/bundle.js │ │ └── v4/bundle.js # Current version │ ├── strategic-planner-sync/ │ │ ├── v1/bundle.js │ │ └── v2/bundle.js # Current version │ ├── email-notifications/ │ │ ├── v1/ … v6/bundle.js │ │ └── v7/bundle.js # Current version │ └── cloud-sync/ │ └── v1/bundle.js └── storage/ └── pending-tasks/public/ ├── Strat Planner Pro logo.png ├── DashboardPanel.png ├── SWOTAnalysisPage.jpeg ├── SWOTAnalysisPanel.png ├── TeamCollabPanel.png ├── SigninForm.png ├── Dashboard Screenshot.png ├── Strat-Planner-Pro.png ├── Investment ecosystem.png └── Bangsamoro Economic and Investment Ecosystem.png

13

Tech Stack

All technologies and external services used in production

🔧

Backend Platform

⚡SupabaseLatest

🗄PostgreSQL15

🦕Deno1.40+

🌐

External Services

✉Resend

📅Google Calendar API

🗓Calendly API

🤖AI Gateway (LLM)

📮

Maintained By

Maintained by A. Silva Innovations — admin@asilvainnovations.com · Support: support@deploypad.app (Mon–Fri 9AM–6PM GMT+8, ~4hr response)

Strat Planner ProDeveloper Docs

Strat Planner Pro
Developer Docs