| Server IP : 167.235.224.122 / Your IP : 216.73.216.110 Web Server : Apache/2.4.58 (Ubuntu) System : Linux newplayground 6.8.0-136-generic #136-Ubuntu SMP PREEMPT_DYNAMIC Wed Jul 1 21:33:11 UTC 2026 aarch64 User : deploy ( 1000) PHP Version : 8.4.23 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : OFF Directory : /var/www/html/openskillpaths/src/ |
Upload File : |
-- OpenSkillPaths Registry Schema
-- PostgreSQL
-- Registered Badgau nodes (instances to crawl)
CREATE TABLE IF NOT EXISTS nodes (
id SERIAL PRIMARY KEY,
url VARCHAR(500) UNIQUE NOT NULL, -- base URL, e.g. https://badgau.net
name VARCHAR(255),
api_endpoint VARCHAR(500), -- e.g. https://badgau.net/api/v1/public/badge-classes
status VARCHAR(20) DEFAULT 'active', -- active | paused | error
last_crawled_at TIMESTAMP,
last_error TEXT,
crawl_interval INTEGER DEFAULT 3600, -- seconds between crawls
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Aggregated badge classes from all nodes
CREATE TABLE IF NOT EXISTS badge_classes (
id SERIAL PRIMARY KEY,
uuid TEXT UNIQUE NOT NULL,
node_id INTEGER REFERENCES nodes(id) ON DELETE CASCADE,
-- Core identity
name VARCHAR(500),
description TEXT,
credential_category VARCHAR(100),
image_url TEXT,
earn_url TEXT,
valid_for VARCHAR(50), -- ISO 8601 duration, e.g. P730D
-- Issuer
issuer_name VARCHAR(500),
issuer_did VARCHAR(500),
issuer_url TEXT,
issuer_logo_url TEXT,
-- Taxonomy
skills JSONB DEFAULT '[]', -- [{uri, label}]
alignments JSONB DEFAULT '[]', -- [{alignmentType, targetName, targetUrl, targetCode, educationalFramework}]
-- Proof
proof JSONB, -- JsonWebSignature2020 proof from issuer, null if unsigned
-- Housekeeping
first_seen_at TIMESTAMP DEFAULT NOW(),
last_seen_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_badge_classes_node ON badge_classes(node_id);
CREATE INDEX IF NOT EXISTS idx_badge_classes_category ON badge_classes(credential_category);
CREATE INDEX IF NOT EXISTS idx_badge_classes_issuer_did ON badge_classes(issuer_did);
CREATE INDEX IF NOT EXISTS idx_badge_classes_updated ON badge_classes(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_badge_classes_skills ON badge_classes USING GIN(skills);
CREATE INDEX IF NOT EXISTS idx_badge_classes_name ON badge_classes USING GIN(to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '')));
-- Peer OpenSkillPaths registries (for node list gossip)
CREATE TABLE IF NOT EXISTS peers (
id SERIAL PRIMARY KEY,
url VARCHAR(500) UNIQUE NOT NULL, -- base URL of the peer registry
status VARCHAR(20) DEFAULT 'active', -- active | paused | error
last_synced_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Seed with Badgau as the first node
INSERT INTO nodes (url, name, api_endpoint)
VALUES (
'https://badgau.net',
'Badgau',
'https://badgau.net/api/v1/public/badge-classes'
) ON CONFLICT (url) DO NOTHING;
-- ─── Migrations ──────────────────────────────────────────────────────────────
-- Widen identifier column to accept OB3 URIs (was VARCHAR(36), now TEXT)
DO $$
BEGIN
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'badge_classes'
AND column_name = 'uuid'
AND data_type = 'character varying'
) THEN
ALTER TABLE badge_classes ALTER COLUMN uuid TYPE TEXT;
END IF;
END $$;
-- Track which format a node serves: 'badgau' | 'ob3'
ALTER TABLE nodes ADD COLUMN IF NOT EXISTS node_type VARCHAR(20) DEFAULT 'badgau';
-- Track which format each badge class came from: 'badgau' | 'ob3'
ALTER TABLE badge_classes ADD COLUMN IF NOT EXISTS source_format VARCHAR(20);
-- Badge class lifecycle: active (seen in latest crawl) | archived (disappeared from source)
ALTER TABLE badge_classes ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'active';
CREATE INDEX IF NOT EXISTS idx_badge_classes_status ON badge_classes(status);
-- ─── Pathways (badge albums / skill paths) ──────────────────────────────────
CREATE TABLE IF NOT EXISTS pathways (
id SERIAL PRIMARY KEY,
uuid VARCHAR(500), -- client-generated UUID or URI (stable across re-publishes)
master_achievement_id TEXT NOT NULL, -- achievement_id (URL/URI) of the goal badge class (must have proof)
title VARCHAR(500) NOT NULL,
description TEXT,
image_url TEXT,
is_sequential BOOLEAN DEFAULT false, -- must sections be completed in order?
status VARCHAR(20) DEFAULT 'active', -- draft | active | archived
schema_version VARCHAR(10) DEFAULT '1.0',
submitted_by TEXT, -- DID or URL of the submitting organisation
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Add uuid to existing pathways tables that predate this column
ALTER TABLE pathways ADD COLUMN IF NOT EXISTS uuid VARCHAR(500);
CREATE UNIQUE INDEX IF NOT EXISTS idx_pathways_uuid ON pathways(uuid);
CREATE TABLE IF NOT EXISTS pathway_sections (
id SERIAL PRIMARY KEY,
pathway_id INTEGER NOT NULL REFERENCES pathways(id) ON DELETE CASCADE,
title VARCHAR(500),
description TEXT,
position INTEGER NOT NULL DEFAULT 0,
rule_type VARCHAR(20) NOT NULL DEFAULT 'all', -- all | one_of | n_of_m
rule_count INTEGER -- for n_of_m: how many required
);
CREATE TABLE IF NOT EXISTS pathway_slots (
id SERIAL PRIMARY KEY,
section_id INTEGER NOT NULL REFERENCES pathway_sections(id) ON DELETE CASCADE,
achievement_id TEXT NOT NULL, -- achievement_id (URL/URI) referencing a badge class
title VARCHAR(500), -- fallback title if badge not in registry
description TEXT,
image_url TEXT, -- fallback image
position INTEGER NOT NULL DEFAULT 0,
is_optional BOOLEAN DEFAULT false, -- bonus slot, not required for section completion
issuer_did VARCHAR(500), -- optional issuer restriction
issuer_name VARCHAR(500)
);
CREATE INDEX IF NOT EXISTS idx_pathways_master ON pathways(master_achievement_id);
CREATE INDEX IF NOT EXISTS idx_pathways_status ON pathways(status);
CREATE INDEX IF NOT EXISTS idx_pathway_sections_path ON pathway_sections(pathway_id);
CREATE INDEX IF NOT EXISTS idx_pathway_slots_section ON pathway_slots(section_id);
CREATE INDEX IF NOT EXISTS idx_pathway_slots_badge ON pathway_slots(achievement_id);
-- ─── Trust / verification fields ─────────────────────────────────────────────
-- Who externally verified this issuer node? e.g. "credential_engine", "ebsi", "eidas", "badgau"
ALTER TABLE nodes ADD COLUMN IF NOT EXISTS verified_by TEXT;
ALTER TABLE nodes ADD COLUMN IF NOT EXISTS verification_ref TEXT; -- URL to trust registry entry or certificate
ALTER TABLE nodes ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;
-- Same for individual badge classes (an issuer may be verified, but we can also
-- record per-credential verification from sources like Credential Engine)
ALTER TABLE badge_classes ADD COLUMN IF NOT EXISTS verified_by TEXT;
ALTER TABLE badge_classes ADD COLUMN IF NOT EXISTS verification_ref TEXT;
ALTER TABLE badge_classes ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;
-- ─── Credential Engine sync state ────────────────────────────────────────────
-- Single-row table tracking the last CE sync run and its outcome.
-- CE is not modelled as a node — it has its own dedicated sync service (src/ce.js).
CREATE TABLE IF NOT EXISTS ce_sync_state (
id INTEGER PRIMARY KEY DEFAULT 1,
synced_at TIMESTAMP,
badge_count INTEGER DEFAULT 0,
micro_count INTEGER DEFAULT 0,
last_error TEXT
);
-- Ensure the single row always exists
INSERT INTO ce_sync_state (id) VALUES (1) ON CONFLICT DO NOTHING;