Relational Blueprint V1

Intent

Define the first concrete relational blueprint for the core platform model in PostgreSQL/Drizzle.

This document provides implementation-ready table groups, constraints, and indexing guidance for the first schema tranche.

Scope

Included:

Core person-centric identity tables
Learning structure and participation tables
Assessment, feedback, and evidence tables
Competency framework tables
Qualification and portability tables
Sync-facing columns needed for LWW baseline

Excluded:

Full DDL syntax
Materialized analytics projections
Mid-term standards-specific projection tables

Cross-Cutting Conventions

Primary keys Use stable surrogate IDs (UUID or equivalent stable opaque IDs).
Timestamps Every mutable core table includes created_at_us and updated_at_us.
LWW support columns Every mutable sync-participating table includes:
- updated_at_us (server authoritative)
- last_operation_id (pgxsinkit operation-log linkage, FK or validated identifier contract)
- last_apply_sequence (server authoritative deterministic apply ordering value)
Audit traceability Mutations should preserve created_by_person_id or equivalent actor reference when available.
Soft deletion Prefer status and temporal end fields over hard delete for person-linked records.
Shared column packages Audit and LWW columns should be declared from centralized schema helpers (not duplicated inline) so naming, defaults, and type modes remain consistent across tables.

Table Group A: Identity and Context

`person`

Purpose: durable person identity independent of institution.

Key columns:

id (PK)
global_handle (optional unique)
status (active, suspended, archived)
created_at_us, updated_at_us

Constraints and indexes:

Unique index on global_handle when present
Index on status

`person_profile`

Purpose: mutable profile attributes separated from identity core.

Key columns:

id (PK)
person_id (FK -> person.id, unique)
display_name
locale
timezone
profile_meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Unique (person_id)
Index on locale

`identity_binding`

Purpose: map external identities (LTI/SIS/IdP) to person.

Key columns:

id (PK)
person_id (FK -> person.id)
provider
issuer
external_subject
effective_from, effective_until
binding_meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Unique (provider, issuer, external_subject, effective_from)
Index on (person_id, effective_until)
Check: effective_until is null or effective_until >= effective_from

`organization`

Purpose: administrative context for offerings and permissions.

Key columns:

id (PK)
slug (unique)
name
status
created_at_us, updated_at_us

Constraints and indexes:

Unique (slug)
Index on status

`organization_affiliation`

Purpose: person membership in organization over time.

Key columns:

id (PK)
organization_id (FK)
person_id (FK)
affiliation_role
effective_from, effective_until
status
created_at_us, updated_at_us

Constraints and indexes:

Index (organization_id, person_id, status)
Check temporal window validity

`person_role_grant`

Purpose: role capabilities for person over time.

Key columns:

id (PK)
person_id (FK)
scope_type (global, organization, offering)
scope_id (nullable depending on scope type)
role (learner, teacher, assistant, admin, observer, mentor)
effective_from, effective_until
created_by_person_id (nullable FK)
created_at_us, updated_at_us

Constraints and indexes:

Index (person_id, role, effective_until)
Check for valid scope combinations

Table Group B: Learning Structure and Participation

`learning_container`

Purpose: optional long-lived grouping of offerings.

Key columns:

id (PK)
organization_id (FK)
container_type
title
status
start_at, end_at
meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (organization_id, status)
Check temporal window validity

`offering`

Purpose: concrete delivery context where memberships and units live.

Key columns:

id (PK)
organization_id (FK)
learning_container_id (nullable FK)
title
offering_type
status (draft, scheduled, active, paused, completed, archived, canceled)
start_at, end_at
meta (JSONB)
created_by_person_id (nullable FK)
created_at_us, updated_at_us

Constraints and indexes:

Index (organization_id, status, start_at)
Index (learning_container_id)
Check temporal window validity

`offering_membership`

Purpose: unified participation model for learner/teacher roles.

Key columns:

id (PK)
offering_id (FK)
person_id (FK)
membership_role
membership_status (invited, active, suspended, withdrawn, completed, expired)
effective_from, effective_until
created_by_person_id (nullable FK)
meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Unique (offering_id, person_id, membership_role, effective_from)
Index (person_id, membership_status, effective_until)
Index (offering_id, membership_role, membership_status)
Check temporal window validity

`learning_unit`

Purpose: sequenced instructional elements in an offering.

Key columns:

id (PK)
offering_id (FK)
parent_unit_id (nullable FK self)
sequence_no
unit_type
title
status
created_at_us, updated_at_us

Constraints and indexes:

Unique (offering_id, sequence_no)
Index (offering_id, status)
Index (parent_unit_id)

`unit_release_rule`

Purpose: release and sequencing rules for learning units.

Key columns:

id (PK)
learning_unit_id (FK)
rule_type
rule_payload (JSONB)
status
created_at_us, updated_at_us

Constraints and indexes:

Index (learning_unit_id, status)

Table Group C: Assessment and Feedback

`assessment_definition`

Purpose: assessment configuration and lifecycle metadata.

Key columns:

id (PK)
offering_id (FK)
learning_unit_id (nullable FK)
assessment_type
title
state (draft, published, active, retired)
max_score (nullable)
rubric_schema (JSONB)
version_no
created_at_us, updated_at_us

Constraints and indexes:

Index (offering_id, state)
Index (learning_unit_id)
Unique (id, version_no)

`assessment_attempt`

Purpose: each person attempt against an assessment definition.

Key columns:

id (PK)
assessment_definition_id (FK)
person_id (FK)
attempt_no
state (started, submitted, evaluated, finalized, invalidated)
started_at, submitted_at, finalized_at
attempt_payload (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Unique (assessment_definition_id, person_id, attempt_no)
Index (person_id, state, updated_at_us)
Index (assessment_definition_id, state)

`assessment_score`

Purpose: scoring outcome with provenance.

Key columns:

id (PK)
assessment_attempt_id (FK)
score_raw (nullable)
score_scaled (nullable)
score_meta (JSONB, rubric and criterion breakdown)
evaluated_by_person_id (nullable FK)
evaluation_source (auto, human, hybrid)
is_final
created_at_us, updated_at_us

Constraints and indexes:

Index (assessment_attempt_id, is_final)
Index (evaluated_by_person_id)

`feedback_entry`

Purpose: complete feedback record history linked to attempts.

Key columns:

id (PK)
assessment_attempt_id (FK)
author_person_id (nullable FK)
feedback_type (auto, instructor, peer, system)
body
feedback_meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (assessment_attempt_id, created_at_us)
Index (author_person_id, feedback_type)

`submission_artifact`

Purpose: files/links/content units submitted with attempts.

Key columns:

id (PK)
assessment_attempt_id (FK)
artifact_type
artifact_uri
artifact_hash (nullable)
meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (assessment_attempt_id, artifact_type)

Table Group D: Competency Framework

`competency_framework`

Purpose: framework root metadata.

Key columns:

id (PK)
organization_id (nullable FK)
code
title
status
created_at_us, updated_at_us

Constraints and indexes:

Unique (organization_id, code)

`competency_framework_version`

Purpose: immutable framework versions.

Key columns:

id (PK)
competency_framework_id (FK)
version_label
state (draft, published, retired)
published_at (nullable)
created_at_us, updated_at_us

Constraints and indexes:

Unique (competency_framework_id, version_label)
Index (competency_framework_id, state)

`competency_node`

Purpose: concrete competency statement in one framework version.

Key columns:

id (PK)
competency_framework_version_id (FK)
node_key
title
description
level_hint (nullable)
created_at_us, updated_at_us

Constraints and indexes:

Unique (competency_framework_version_id, node_key)
Index (competency_framework_version_id)

`competency_node_edge`

Purpose: graph edges between competency nodes.

Key columns:

id (PK)
competency_framework_version_id (FK)
from_node_id (FK)
to_node_id (FK)
edge_type (parent, prerequisite, related)
created_at_us, updated_at_us

Constraints and indexes:

Unique (competency_framework_version_id, from_node_id, to_node_id, edge_type)
Check (from_node_id <> to_node_id)

`competency_alignment`

Purpose: map learning units/assessments to competency nodes over time.

Key columns:

id (PK)
competency_framework_version_id (FK)
competency_node_id (FK)
target_type (learning_unit, assessment_definition)
target_id
alignment_strength
effective_from, effective_until
created_at_us, updated_at_us

Constraints and indexes:

Index (target_type, target_id)
Index (competency_node_id, effective_until)
Check temporal window validity

`person_competency_evidence`

Purpose: append-oriented competency evidence records.

Key columns:

id (PK)
person_id (FK)
competency_framework_version_id (FK)
competency_node_id (FK)
source_type (assessment-attempt, rubric-evaluation, instructor-attestation, observation-event, external-import)
source_id
evidence_weight (nullable)
captured_at
evidence_payload (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (person_id, competency_node_id, captured_at)
Index (source_type, source_id)
Index (competency_framework_version_id, competency_node_id)

`person_competency_state`

Purpose: derived current state per person and competency node.

Key columns:

id (PK)
person_id (FK)
competency_framework_version_id (FK)
competency_node_id (FK)
state (not-started, in-progress, demonstrated, mastered, regressed)
last_derived_at
derivation_policy_version
derivation_meta (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Unique (person_id, competency_framework_version_id, competency_node_id)
Index (state, last_derived_at)

Table Group E: Qualification and Portability

`qualification_definition`

Purpose: credential or qualification rule definitions.

Key columns:

id (PK)
organization_id (nullable FK)
code
title
criteria_payload (JSONB)
status
created_at_us, updated_at_us

Constraints and indexes:

Unique (organization_id, code)

`qualification_award`

Purpose: person achievement records with provenance.

Key columns:

id (PK)
qualification_definition_id (FK)
person_id (FK)
state (proposed, granted, revoked, superseded)
granted_at (nullable)
revoked_at (nullable)
issuer_organization_id (nullable FK)
evidence_payload (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (person_id, state, granted_at)
Index (qualification_definition_id, state)

`progress_snapshot`

Purpose: compact progress summary for fast reads.

Key columns:

id (PK)
person_id (FK)
offering_id (FK)
snapshot_at
progress_payload (JSONB)
created_at_us, updated_at_us

Constraints and indexes:

Index (person_id, offering_id, snapshot_at)

`activity_event`

Purpose: internal append-oriented activity ledger.

Key columns:

id (PK)
person_id (nullable FK)
organization_id (nullable FK)
event_type
event_time
event_payload (JSONB)
source_operation_id (nullable)
created_at_us

Constraints and indexes:

Index (person_id, event_time)
Index (event_type, event_time)
Index (source_operation_id)

`change_audit_entry`

Purpose: mutation trace for regulated and debugging workflows.

Key columns:

id (PK)
table_name
record_id
operation (insert, update, delete, state-transition)
actor_person_id (nullable FK)
change_time
before_payload (JSONB)
after_payload (JSONB)
source_operation_id (nullable)

Constraints and indexes:

Index (table_name, record_id, change_time)
Index (actor_person_id, change_time)

`export_import_batch`

Purpose: traceable export/import jobs for portable learner record flows.

Key columns:

id (PK)
person_id (nullable FK for scoped exports)
batch_type (export, import)
format_type (clr, xapi, internal-json)
status
checksum
source_ref (nullable)
created_at_us, updated_at_us

Constraints and indexes:

Index (batch_type, status, created_at_us)
Index (person_id, created_at_us)

Required Export Baseline Coverage

Portable exports must include by default:

Full attempt-level history (assessment_attempt)
Full feedback-level history (feedback_entry)
Scoring outcomes (assessment_score)
Competency evidence and state history
Qualification awards

Sync-Specific Notes For V1

Mutable sync tables should carry updated_at_us, last_operation_id, and last_apply_sequence.
V1 conflict behavior uses server-side apply order LWW by default.
Every sync write should be represented in pgxsinkit append-only operation log (see sync write model doc).

Unified Mutation Ingress Policy

All syncable domain-table mutations must flow through pgxsinkit batch ingress (POST /api/mutations) under bulk-plpgsql-artifact mode.

Admin/import/batch/system workflows remain supported, but they must emit writes through the same ingress contract so operation logging and apply ordering stay unified.

Implementation Anchors

Table definitions and migrations: packages/db
Type contracts and validation: packages/domain
Sync projection and apply behavior: packages/offline-data
Mutation services and policy checks: apps/api