Jira progress: loading…

EWA-AUTH

EcoWorld Academy — Database Architecture & Registration Flow

Developer Integration Specification v1.0

1. Purpose

This document explains:

the Academy database architecture,
the separation of responsibilities between schemas,
the runtime learning infrastructure,
the registration/payment/access flow,
and the relationship between EcoWorld Academy, HubSpot, Stripe, Cognito, and ZAYAZ.

The architecture is designed to support:

ESG operational intelligence learning,
AI personalization,
enterprise scalability,
white-label neutrality,
and contextual integration with ZAYAZ workflows.

2. High-Level System Architecture

User
  ↓
EcoWorld Academy Frontend
  ↓
Registration API
  ↓
-----------------------------------------
| Identity Layer (Cognito + ECO Number) |
-----------------------------------------
  ↓
-----------------------------------------
| Commerce Layer (HubSpot + Stripe)     |
-----------------------------------------
  ↓
-----------------------------------------
| Entitlement Layer                     |
-----------------------------------------
  ↓
-----------------------------------------
| Academy Runtime Layer                 |
-----------------------------------------
  ↓
-----------------------------------------
| AI + Knowledge Graph + Telemetry      |
-----------------------------------------

3. Schema Responsibilities

Schema	Responsibility
eco	ESG identity and entity layer
prodreg	Product registry, commerce, enrollments
academy	Learning runtime, progress, quizzes, certificates
zayaz	ESG operational workflows and telemetry

3.1. eco tables

schema_name	table_name	table_comment
eco	entities	Master registry of organizations, suppliers, clients, NGOs, authorities, verifiers, educational institutions, and other sustainability ecosystem entities.
eco	entity_contacts	Registry of contacts linked to ECO entities, including learners, company administrators, verifiers, supplier contacts, and operational stakeholders.
eco	numbers	Issued E-C-O-Number™ registry containing public and private sustainability identity numbers, lifecycle states, verification levels, and visibility metadata.
eco	supplier_invitations	Supplier onboarding and survey invitation workflow registry used for ESG data collection, supplier engagement, and sustainability assessments.
eco	supplier_survey_responses	Structured ESG supplier survey response registry containing submitted sustainability responses, evidence payloads, review status, and assessment metadata.

3.2. prodreg tables

Schema	Name	Type	Owner
prodreg	`academy_bundles`	table	prodreg_admin
prodreg	`academy_companies`	table	prodreg_admin
prodreg	`academy_courses`	table	prodreg_admin
prodreg	`academy_enrollments`	table	prodreg_admin
prodreg	`academy_learners`	table	prodreg_admin
prodreg	`academy_policy_acknowledgements`	table	prodreg_admin
prodreg	`academy_progress_events`	table	prodreg_admin
prodreg	`audit_log`	table	prodreg_admin
prodreg	`product_public_codes`	table	prodreg_admin
prodreg	`product_variants`	table	prodreg_admin
prodreg	`products`	table	prodreg_admin
prodreg	`sku_counters`	table	prodreg_admin
prodreg	`sync_jobs`	table	prodreg_admin
prodreg	`target_url_rules`	table	prodreg_admin
prodreg	`tax_category_mapping`	table	prodreg_admin

3.3. academy tables

schema_name	table_name	table_comment
academy	certificates	Stores issued course certificates and diploma records, including learner identity, course completion metadata, scores, and certificate verification references.
academy	content_registry	Central knowledge graph registry for Academy learning content. Stores semantic metadata, concepts, frameworks, operational contexts, and AI-relevant tags extracted from MDX frontmatter.
academy	courses	Registry of all Academy courses. Stores high-level course metadata including identifiers, difficulty, language, estimated duration, and publication status. Linked to MDX content and quiz infrastructure.
academy	entitlements	Source of truth for Academy access control. Determines which users or organizations have access to specific courses, bundles, or learning products. Integrated with HubSpot and Stripe payment workflows.
academy	learning_signal_events	Stores operational learning signals and AI-triggered educational events originating from Academy activity or integrated ZAYAZ telemetry systems. Used for adaptive learning and contextual learning injection.
academy	module_progress	Tracks learner progression through Academy modules, including completion state, timestamps, and learning activity telemetry.
academy	modules	Registry of course modules. Each module maps to a structured MDX learning file and represents a logical learning unit within a course.
academy	quiz_attempt_answers	Stores detailed learner responses for individual quiz questions. Used for misconception analysis, AI diagnostics, adaptive learning, telemetry, and spaced recall generation.
academy	quiz_attempts	Stores learner quiz attempt sessions, scores, pass/fail status, timestamps, and AI-generated remediation feedback.
academy	registration_requests	Stores pending and completed Academy registration flows before entitlement activation. Used for onboarding, payment initiation, CRM synchronization, and registration lifecycle tracking.
academy	spaced_recall_queue	Stores scheduled spaced recall reinforcement events, including follow-up questions, refresh prompts, and memory reinforcement activities for long-term ESG competency retention.
academy	users	Stores Academy learner identities and links users to Cognito identities, E-C-O™ Numbers, and organizational relationships. Acts as the primary learner identity table for EcoWorld Academy.
academy	workbook_responses	Stores learner responses to workbook exercises, reflective prompts, operational tasks, and written learning activities associated with Academy modules.

4. ECO Schema

The eco schema acts as the ESG identity infrastructure.

eco.entities

Stores:

companies,
organizations,
suppliers,
NGOs,
authorities,
clients.

Acts as:

the ESG organizational identity registry.

eco.entity_contacts

Stores:

contacts linked to organizations,
stakeholder mappings,
contact-role relationships.

eco.numbers

Stores:

E-C-O™ Numbers

Used as:

global ESG identity references,
cross-platform identifiers,
Academy/ZAYAZ linkage keys.

eco.supplier_invitations

Stores supplier onboarding invitations.

Supports:

supplier assessments,
ESG questionnaires,
supply chain workflows.

eco.supplier_survey_responses

Stores:

supplier ESG responses,
questionnaire data,
supplier disclosures.

5. PRODREG Schema

The prodreg schema acts as:

the commerce and product orchestration layer.

prodreg.products

Canonical product registry.

Stores:

products,
SKUs,
pricing references,
subscriptions,
bundles.

prodreg.product_variants

Stores:

product versions,
regional variants,
pricing variants,
enterprise editions.

prodreg.academy_courses

Commercial course registry.

Maps:

products to:
Academy course IDs.

prodreg.academy_bundles

Stores:

bundled learning products,
course packages,
enterprise learning bundles.

prodreg.academy_enrollments

Stores:

commercial enrollment state

Examples:

purchased,
assigned,
pending,
expired.

Commerce-oriented.

prodreg.academy_learners

Commercial learning identity layer.

Supports:

enterprise onboarding,
seat assignment,
CRM synchronization.

prodreg.academy_companies

Stores:

enterprise Academy customers,
organizational learning relationships,
enterprise licenses.

prodreg.product_public_codes

Stores:

public registration codes,
campaign links,
referral identifiers,
course landing identifiers.

Used for:

go.ecoworld.ai/*

routing.

prodreg.target_url_rules

Defines:

intelligent redirects,
campaign routing,
dynamic course registration URLs.

prodreg.sync_jobs

Tracks:

synchronization jobs,
integration events,
import/export pipelines.

prodreg.audit_log

Stores:

commercial audit events,
enrollment actions,
synchronization activity.

6. ACADEMY Schema

The academy schema acts as:

the operational learning runtime layer.

6.1. Identity & Registration

academy.users

Stores Academy learner identities.

Links:

Cognito users,
E-C-O™ Numbers,
organizational relationships.

Primary learner identity table.

academy.registration_requests

Stores:

pending registrations,
onboarding state,
payment initiation state,
registration lifecycle tracking.

Used before entitlement activation.

6.2. Course Runtime Registry

academy.courses

Runtime registry for Academy courses.

Stores:

learning metadata,
duration,
difficulty,
publication status,
language,
learning configuration.

Linked to:

MDX content,
quizzes,
knowledge graph.

academy.modules

Runtime registry for course modules.

Each module maps to:

one MDX learning file.

Represents:

logical learning units.

academy.content_registry

Knowledge graph registry.

Stores:

concepts,
frameworks,
operational contexts,
learning signal tags,
semantic metadata.

Populated from:

MDX frontmatter extraction.

Used by:

AI search,
contextual learning injection,
semantic recommendations.

6.3. Access & Authorization

academy.entitlements

Source of truth for:

runtime course access.

Controls:

active access,
revocation,
expiration,
temporary access states.

IMPORTANT: This is separate from commercial enrollment state.

6.4. Learning Runtime

academy.module_progress

Tracks:

learner progress,
completion,
timestamps,
activity telemetry.

academy.quiz_attempts

Stores:

quiz sessions,
scores,
pass/fail state,
AI remediation feedback.

academy.quiz_attempt_answers

Stores:

individual learner answers,
selected options,
correctness,
misconception telemetry.

Used for:

AI diagnostics,
spaced recall,
adaptive learning.

academy.workbook_responses

Stores:

workbook answers,
reflections,
operational exercises,
written learning activities.

6.5. Reinforcement & AI

academy.learning_signal_events

Stores:

operational learning signals,
AI intervention triggers,
telemetry-linked learning events.

Examples:

weak_evidence_quality
materiality_confusion
supplier_risk

Supports:

adaptive learning,
contextual learning injection,
remediation.

academy.spaced_recall_queue

Stores:

scheduled reinforcement prompts,
recall questions,
spaced repetition activities.

Used for:

long-term ESG competency retention.

6.6. Certification

academy.certificates

Stores:

issued certificates,
diploma metadata,
verification references,
completion scores.

7. Course Content Architecture

Academy content is stored in:

GitHub content repositories

Example:

ecoworld-academy-content/
  courses/
    AC2605-d31211dd/
      course.json
      modules/
      quizzes/
      source/

course.json

Defines:

course orchestration,
module registry,
certification rules,
learning structure.

MDX Module Files

Defines:

lessons,
concepts,
operational contexts,
spaced recall,
semantic metadata,
AI learning structure.

Quiz JSON Files

Defines:

questions,
answers,
explanations,
misconception metadata,
learning signal tags.

8. Registration & Purchase Flow

Step 1 — Course Entry

User visits:

go.ecoworld.ai/procurement-sustainability-esrs-g1-2

Routing rules resolve:

product,
course,
campaign,
bundle,
registration flow.

Step 2 — Registration Page

The registration page:

preloads the selected course,
collects learner information,
collects company data if required,
prepares identity and payment flows.

Step 3 — Internal Registration Records

System creates:

registration request,
pending learner profile,
pending entitlement.

Stored in:

academy.registration_requests
academy.users

Step 4 — Cognito User Creation

The learner is registered in Cognito.

Cognito stores:

authentication,
password management,
MFA,
session management.

Academy stores:

runtime learner identity.

Step 5 — CRM Synchronization

Learner and company data are synchronized to:

HubSpot

HubSpot manages:

lifecycle,
CRM state,
deals,
automation,
enterprise onboarding.

Step 6 — Payment Processing

HubSpot initiates:

Stripe Checkout

Stripe manages:

payments,
invoices,
VAT,
subscriptions,
refunds.

Step 7 — Stripe Webhook Confirmation

After successful payment:

Stripe webhook fires,
payment is verified,
HubSpot deal state updates.

Step 8 — Entitlement Activation

The entitlement service activates:

academy.entitlements

The learner now receives:

runtime course access.

Step 9 — Learning Access

The learner is redirected to:

the Academy course experience.

The runtime checks:

active entitlement,
course availability,
user authorization.

9. Important Architectural Principle

Commerce ≠ Runtime Access

The system intentionally separates:

Layer	Purpose
Commercial enrollment	Commerce/payment state
Runtime entitlement	Actual learning access

This allows:

refunds,
revocations,
enterprise licenses,
temporary suspensions,
manual overrides,
seat assignment,
white-label flexibility.

10. AI & ZAYAZ Integration

The Academy is designed for:

contextual ESG operational intelligence.

Examples:

quiz misconceptions trigger remediation,
ZAYAZ telemetry triggers contextual lessons,
supplier risk events inject learning guidance,
weak evidence quality triggers verifier training.

The Academy knowledge graph powers:

semantic search,
AI coaching,
contextual injection,
adaptive learning,
reinforcement orchestration.

11. Long-Term Vision

EcoWorld Academy evolves into:

an ESG competence infrastructure,
an operational learning graph,
an AI-guided ESG mentor system,
and the educational intelligence layer connected directly to ZAYAZ.

APPENDIX - Detailed Architecture Clarification (Q&A)

Questions:

Q.1. Who creates the Stripe Checkout session?

Step 6 says "HubSpot initiates Stripe Checkout." Is HubSpot creating the Stripe session directly (via a HubSpot workflow/payment link), or does the Academy backend create the session after syncing to HubSpot? This matters for where payment logic lives.

Q.2. User creation path — self-service vs. registration API

The flow shows the system creating the Cognito user at Step 4 (backend-driven). The current Academy login page has a sign-up form that does direct Cognito SignUp (note: this login will be used for returning clients). In the new architecture, should self-service sign-up go through the registration API instead (so it creates the academy.users record, syncs to HubSpot, etc. in the right order)? Or do some users still self-register directly?

Q.3. go.ecoworld.ai routing

The entry URL is go.ecoworld.ai/{slug} → resolves product/campaign/bundle via prodreg.target_url_rules. That needs a backend to do the lookup and redirect. Is this a Lambda behind API Gateway, or does it live elsewhere?

Q.4. Stripe webhook receiver

Step 7–8: Stripe fires a webhook → payment verified → academy.entitlements activated. Where does the webhook handler live? A Lambda? The database writes touch academy and prodreg schemas, so the handler needs DB access.

Q.5. E-C-O™ Number assignment

When in the flow does a learner get their E-C-O Number? At registration (Step 3), after payment (Step 8), or later when the company onboards into the ESG ecosystem?

Q.6. Enterprise / seat-based flow

The spec mentions prodreg.academy_companies and seat assignment. For enterprise customers: does a company admin buy seats → get a bulk entitlement → then assign individual learners? Or does each employee go through the same individual flow?

Answers

A.1. Who creates the Stripe Checkout Session?

Academy backend creates the Stripe Checkout session

NOT HubSpot directly.

HubSpot should orchestrate:

CRM state
lifecycle
deal tracking
automation

But:

payment session creation should live in the backend.

Correct Flow

Frontend
  ↓
Academy Registration API
  ↓
Academy Backend
  ├── Create registration request
  ├── Create/update user
  ├── Sync HubSpot
  └── Create Stripe Checkout Session
          ↓
      Return Stripe Checkout URL

Why This Is Better

Because then:

we control metadata
we control entitlement linkage
we control E-C-O™ Number linkage
we control idempotency
we control retries
we control enterprise logic
we control multi-product flows

This is MUCH cleaner long-term.

HubSpot’s Role

HubSpot should receive:

contact
course/product interest
deal
lifecycle stage
payment state updates

NOT own payment orchestration logic.

ALL registration should go through the Registration API

INCLUDING:

self-registration
course purchase
enterprise onboarding
white-label onboarding

Why?

Because: direct Cognito signup bypasses:

Academy user creation
E-C-O™ generation
CRM sync
entity creation
entitlement preparation
telemetry initialization

We want:

one canonical onboarding pipeline.

Recommended Model

Cognito becomes:

authentication infrastructure only

NOT:

business registration logic.

Correct Flow

Frontend Signup Form
    ↓
Registration API
    ↓
Backend:
  - create eco.entity
  - generate E-C-O™ Number
  - create academy.users
  - create Cognito user
  - sync HubSpot
  - create registration request
  - create pending entitlement

This is the correct architecture.

Returning Users

Returning users:

authenticate directly with Cognito.

Only:

new registrations
purchases
onboarding

go through Registration API.

A.3. go.ecoworld.ai Routing

Lambda behind API Gateway

That is exactly the right architecture.

Recommended Flow

go.ecoworld.ai/procurement-sustainability-esrs-g1-2
    ↓
CloudFront
    ↓
API Gateway
    ↓
Lambda
    ↓
Lookup:
  prodreg.target_url_rules
  prodreg.product_public_codes
    ↓
Resolve:
  - course
  - campaign
  - bundle
  - locale
  - redirect rules
    ↓
Redirect

This is scalable, cheap, serverless, and flexible.

Why This Is Powerful

Later we can support:

campaigns
affiliate links
enterprise onboarding
white-label routing
localization
A/B testing
dynamic bundles
temporary offers
QR codes
certifications

without changing frontend code.

Excellent long-term architecture.

A.4. Stripe Webhook Receiver

Lambda function

Absolutely.

Recommended Architecture

Stripe
   ↓
Webhook Endpoint
(API Gateway)
   ↓
Lambda
   ↓
DB updates:
  - academy.entitlements
  - prodreg.academy_enrollments
  - audit logs
  - sync jobs
   ↓
HubSpot sync

VERY Important

Webhook handler should:

ONLY trust Stripe events

Never trust frontend payment success.

Critical.

VERY Important Recommendation

Use:

idempotent webhook processing

Meaning:

store processed Stripe event IDs
never process same event twice

We absolutely want this.

Table

stripe_webhook_events

with:

event_id
processed_at
status
payload_hash

VERY important.

A.5. E-C-O™ Number Assignment

Assign immediately during registration

NOT after payment.

Why?

Because: E-C-O™ Number is:

ESG identity

NOT:

payment identity.

Even free users should get one.

Correct Timing

Registration API
   ↓
Create eco.entity
   ↓
Generate E-C-O™ Number
   ↓
Create academy.users

THEN:

payment
entitlements
onboarding

Why This Is Important

Because later:

free courses
supplier onboarding
verifier workflows
NGO participation
white-label ecosystems
ESG surveys

all benefit from:

unified ESG identity.

Very important strategic decision.

A.6. Enterprise / Seat-Based Flow

BOTH models should exist

But:

enterprise should be seat-based.

Recommended Enterprise Model

Step 1

Company purchases:

bundle
seats
enterprise access

Example:

100 seats Procurement Sustainability Programme

Step 2

System creates:

company enrollment
bulk entitlement pool

Step 3

Company admin assigns seats

Table:

academy_company_seat_assignments

Step 4

Employee receives:

invitation
registration link
entitlement activation

Step 5

Employee completes registration

At this point:

Cognito user created
E-C-O™ Number created
entitlement activated

Important Distinction

Enterprise Flow

Company buys first
User registers second

Individual Flow

User registers first
User buys second

Both should coexist.

Additional Table

academy.company_seat_assignments

Columns:

company_id course_id seat_count assigned_count assigned_user_id assigned_at

Very important later.

FINAL RECOMMENDED ARCHITECTURE

Registration Logic
Academy Backend
Authentication
Cognito
CRM/Lifecycle
HubSpot
Payments
Stripe
Access Control
academy.entitlements
Commerce/Product Registry
prodreg
ESG Identity
eco + E-C-O™ Numbers
Routing
API Gateway + Lambda
Webhooks
Lambda
Learning Runtime
Academy schema

GitHub Repo Request for Change (RFC)

1. Purpose​

2. High-Level System Architecture​

3. Schema Responsibilities​

3.1. eco tables​

3.2. prodreg tables​

3.3. academy tables​

4. ECO Schema​

5. PRODREG Schema​

6. ACADEMY Schema​

6.1. Identity & Registration​

6.2. Course Runtime Registry​

6.3. Access & Authorization​

6.4. Learning Runtime​

6.5. Reinforcement & AI​

6.6. Certification​

7. Course Content Architecture​

8. Registration & Purchase Flow​

9. Important Architectural Principle​

10. AI & ZAYAZ Integration​

11. Long-Term Vision​

APPENDIX - Detailed Architecture Clarification (Q&A)​

Questions:​

Q.1. Who creates the Stripe Checkout session?​

Q.2. User creation path — self-service vs. registration API​

Q.3. go.ecoworld.ai routing​

Q.4. Stripe webhook receiver​

Q.5. E-C-O™ Number assignment​

Q.6. Enterprise / seat-based flow​

Answers​

A.1. Who creates the Stripe Checkout Session?​

A.2. Self-Service Sign-Up vs Registration API​

A.3. go.ecoworld.ai Routing​

A.4. Stripe Webhook Receiver​

A.5. E-C-O™ Number Assignment​

A.6. Enterprise / Seat-Based Flow​

1. Purpose

2. High-Level System Architecture

3. Schema Responsibilities

3.1. eco tables

3.2. prodreg tables

3.3. academy tables

4. ECO Schema

5. PRODREG Schema

6. ACADEMY Schema

6.1. Identity & Registration

6.2. Course Runtime Registry

6.3. Access & Authorization

6.4. Learning Runtime

6.5. Reinforcement & AI

6.6. Certification

7. Course Content Architecture

8. Registration & Purchase Flow

9. Important Architectural Principle

10. AI & ZAYAZ Integration

11. Long-Term Vision

APPENDIX - Detailed Architecture Clarification (Q&A)

Questions:

Q.1. Who creates the Stripe Checkout session?

Q.2. User creation path — self-service vs. registration API

Q.3. go.ecoworld.ai routing

Q.4. Stripe webhook receiver

Q.5. E-C-O™ Number assignment

Q.6. Enterprise / seat-based flow

Answers

A.1. Who creates the Stripe Checkout Session?

A.2. Self-Service Sign-Up vs Registration API

A.3. go.ecoworld.ai Routing

A.4. Stripe Webhook Receiver

A.5. E-C-O™ Number Assignment

A.6. Enterprise / Seat-Based Flow