SCB QR REGISTRY SYSTEM

Database Schema + Credential Lifecycle Workflow

PART A — SYSTEM OVERVIEW

Purpose

To provide tamper-proof, real-time verification of all Specialty Coffee Barista (SCB) credentials via QR code scanning.

Core Principles

One barista = one permanent SCB ID
One credential = one verifiable registry record
Public can verify, but only the authority can issue, edit, revoke
If it’s not in the registry, it’s not valid

PART B — DATABASE SCHEMA (CORE TABLES)

1. `scb_professionals`

(Master identity table – never deleted)

Field Name	Type	Description
`scb_id`	VARCHAR(20)	Unique permanent SCB ID (e.g. SCB-KE-000124)
`first_name`	VARCHAR(100)	Legal first name
`last_name`	VARCHAR(100)	Legal surname
`date_of_birth`	DATE	Identity validation
`nationality`	VARCHAR(50)	Country
`gender`	ENUM	Optional
`photo_url`	TEXT	Profile photo
`phone`	VARCHAR(30)	Contact
`email`	VARCHAR(150)	Contact
`county_city`	VARCHAR(100)	Location
`created_at`	TIMESTAMP	First registration date
`status`	ENUM	Active / Inactive / Deceased

2. `scb_credentials`

(Each certification issued)

Field Name	Type	Description
`credential_id`	UUID	Unique credential record
`scb_id`	VARCHAR(20)	Links to professional
`credential_level`	ENUM	SCB / SCB-C / SCB-P / SCB-A
`issue_date`	DATE	Certification date
`expiry_date`	DATE	Optional (CPD-based)
`status`	ENUM	Active / Suspended / Revoked / Expired
`issuing_body`	VARCHAR(150)	Kenya Coffee School
`program_pathway`	VARCHAR(150)	Kenya Coffee School / Barista Mtaani
`assessment_reference`	VARCHAR(100)	Internal assessment ID
`qr_hash`	VARCHAR(255)	Encrypted verification hash
`revocation_reason`	TEXT	If applicable
`last_verified_at`	TIMESTAMP	Last QR scan

3. `scb_badges`

(Digital & physical badge tracking)

Field Name	Type	Description
`badge_id`	UUID	Badge identifier
`credential_id`	UUID	Linked credential
`badge_type`	ENUM	Digital / Physical
`badge_url`	TEXT	Image or badge link
`qr_code_url`	TEXT	QR image
`issued_at`	TIMESTAMP	Date issued
`is_active`	BOOLEAN	True / False

4. `scb_registry_public_view`

(Optimized read-only verification view)

Field Shown	Source
Full Name	scb_professionals
Photo	scb_professionals
SCB ID	scb_professionals
Credential Level	scb_credentials
Status	scb_credentials
Issuing Authority	scb_credentials
Issue Date	scb_credentials
Expiry Date	scb_credentials
Code of Ethics	Boolean confirmation

⚠️ No private data (ID numbers, contacts) exposed publicly.

5. `scb_disciplinary_log`

(Audit & governance)

Field Name	Type	Description
`log_id`	UUID	Record ID
`scb_id`	VARCHAR(20)	Professional
`action`	ENUM	Warning / Suspension / Revocation
`reason`	TEXT	Ethics or misconduct
`decision_by`	VARCHAR(150)	Authority
`effective_date`	DATE	Action start
`appeal_status`	ENUM	None / Pending / Upheld / Reversed

6. `scb_system_audit`

(Security & compliance)

Field Name	Type
`event_id`	UUID
`user_role`	ENUM (Admin / Evaluator / Viewer)
`action`	TEXT
`timestamp`	TIMESTAMP
`ip_address`	VARCHAR(50)

PART C — QR CODE STRUCTURE

QR Encoded URL (Example)

https://registry.kenyacoffeeschool.ac.ke/scb/SCB-KE-000124

Behind the QR:

SCB ID
Credential hash validation
Status check
Live database fetch

PART D — REGISTRY WORKFLOW (END-TO-END)

STEP 1: Training Completion

Learner completes SCB assessment
Results approved by evaluators

STEP 2: Credential Issuance

Admin creates record in scb_professionals
Credential issued in scb_credentials
QR hash generated
Digital badge created

STEP 3: Badge & Certificate Delivery

Digital badge emailed
Certificate PDF generated
Physical badge (optional)

STEP 4: Public Verification

Anyone scans QR →

Registry page loads
Credential status displayed
Employer confidence established

STEP 5: Ongoing Monitoring

CPD updates
Periodic verification
Status auto-updates (expiry, suspension)

STEP 6: Suspension or Revocation (If Needed)

Ethics panel decision logged
Credential status updated
QR immediately reflects new status

PART E — USER ROLES & ACCESS CONTROL

Roles

Super Admin (Kenya Coffee School) – Full control
Evaluator – Assessment input only
Registry Officer – Issue & manage credentials
Public User – Read-only verification

PART F — LEGAL & DATA PROTECTION

GDPR-aligned consent at enrollment
Right to correction & appeal
No commercial resale of personal data
Registry is proof-of-status, not surveillance

PART G — FAILURE & FRAUD PREVENTION

QR hash mismatch → “Invalid Credential”
Revoked credential → Red warning banner
Screenshot or copied QR → status still live-checked

OFFICIAL REGISTRY STATEMENT (PUBLIC)

This registry is the sole authoritative source for verifying Specialty Coffee Barista (SCB) credentials issued by Kenya Coffee School and Barista Mtaani.