Built for the Canadian Cultural Mosaic Foundation

Three hate-incident tools rebuilt as one secure Oracle platform

DB Realm moved the Canadian Cultural Mosaic Foundation's incident database and dashboards onto one Oracle APEX 26.1 application, with a public dashboard, a guided data-entry workflow and a news-monitoring review queue.

database objects migrated, 0 invalid
443
database objects migrated, 0 invalid
pages in the rebuilt application
47
pages in the rebuilt application
Canadian news feeds, checked every 6 hours
28
Canadian news feeds, checked every 6 hours
legacy pages carried over at component parity
74
legacy pages carried over at component parity

What the platform delivers

One platform instead of four

An APEX data-entry app plus R Shiny, Dash and Streamlit dashboards became one APEX 26.1 application.

A migration you can prove

Objects and rows were count-validated against the source; text and binary columns were checked byte for byte.

Security that fails closed

Hardened passwords, lockout, audit logging and role-based feature access replace checks that let users through by default.

The news comes to the reviewer

Canadian news feeds land in an approval queue; nothing reaches the incident record without a person's decision.

Built with
  • Oracle APEX 26.1
  • Oracle AI Database 26ai
  • PL/SQL
  • SQLcl
  • APEXlang in git
  • DBMS_SCHEDULER
  • Plotly.js
  • OpenStreetMap
  • Playwright
The application

Ten capabilities, one data model

The public sees aggregates; contributors see incident records; administrators maintain the reference data. All of it reads one migrated schema.

Public dashboard

Five no-login pages: overview, geography, demographics, incident analysis, data explorer.

Drill-down charts

Bar and sunburst views; click a category to drill into its sub-categories.

Cross-page filters

Year, date range, province to city, and six match-any filters on every page.

Map and rates

Incident geography with province rates per 100,000 people from census data.

KPI home

Signed-in landing page with headline figures and trends.

Guided incident entry

One form with seven related collections, replacing three overlapping legacy entry paths.

Incident detail

Read-only record with a 12-point data-completeness meter and checklist.

Administration hub

Fourteen lookup tables made maintainable, grouped by geography, categories and system.

Users and roles

Create, lock, unlock, deactivate and reset users; assign roles; every action audited.

News review queue

Approve a fetched article into an incident, or reject it with a reason.

From headline to record

How an incident reaches the dashboard

The public path and the contributor path meet in one database, and a person decides at every step that matters.

  1. 01 Public

    Open the dashboard

    A visitor opens the public dashboard without signing in.

  2. 02 Public

    Filter and drill

    Filter by year, province or category; every chart and KPI updates. Drill from category to sub-category.

  3. 03 Access control

    Aggregates only

    Incident-level rows need a sign-in; the public only ever sees aggregates.

  4. 04 Scheduler

    News arrives

    Every 6 hours the database checks 28 Canadian news feeds and queues new articles.

  5. 05 Reviewer

    A person decides

    Approve an article into an incident through the table APIs, or reject it with a reason.

  6. 06 Contributor

    Complete the record

    Victims, perpetrators, community, identity, type, context and articles, in one guided form.

  7. 07 Quality

    See what is missing

    The detail page scores each record out of 12 and lists the gaps.

AI-ready, human-reviewed

Built for AI, switched on only when the Foundation chooses

The news pipeline was designed so AI can help later without ever deciding on its own. Today it runs in manual mode: a person classifies every article.

One provider switch

Manual, Select AI or OCI Generative AI, chosen in one setting. It ships as manual.

Near-duplicate detection

Vector similarity would show the five closest existing incidents to the reviewer.

Advisory by design

Even when enabled, AI only proposes fields. Nothing is recorded without approval.

AI classification, embedding and duplicate detection are built but ship disabled; no outside model is called.

Security & quality

Sensitive data, handled like it

The migration was also a security rebuild. Every change was verified before and after.

Hardened passwords

Peppered key derivation, 15,000 iterations of HMAC-SHA256; old hashes upgrade on next login.

Lockout

Six failures lock an account for 15 minutes; unknown and wrong users get the same answer.

Feature-level access

View, edit and admin permissions per feature, refusing by default.

Audit trail

Sign-ins, user administration and system events in audit logs; settings stored encrypted.

A leak closed

A public page exposing incident-level rows was moved behind sign-in.

Resilient news fetch

Per-feed timeout and isolation; one failing feed never stops the others.

authentication tests passed
12/12
authentication tests passed
permission smoke tests passed
8/8
permission smoke tests passed
tables at exact row parity after migration
54/55
tables at exact row parity after migration
re-runnable, versioned migrations
13
re-runnable, versioned migrations
How we delivered

From four tools to one platform

The original 2020 dashboards were built by a Data for Good Calgary volunteer team. DB Realm carried the work forward: first the dashboards, then the full migration and rebuild.

  1. 01

    Migrations that can be re-run

    Numbered, idempotent migrations with a schema-version table; each was re-run to prove it.

  2. 02

    APEX source in git

    The application lives in git as APEXlang and deploys with SQLcl validate and import.

  3. 03

    Parity, verified

    The 74-page legacy app was inventoried component by component before anything was retired.

  4. 04

    Handover with every release

    A changelog and handover document travel with every release.

  1. 2020
    Volunteer dashboards (Data for Good Calgary)
  2. Feb 2026
    Dashboards redeveloped and containerised
  3. Jul 2026
    Migration to 26ai, security rebuild, new app
  4. Jul 2026
    News monitoring and administration hub

The rebuilt application is running; the legacy application stays available as a fallback until the Foundation completes cutover.

The full story

Canadian Cultural Mosaic Foundation — Hate Incident Tracker

Canadian Cultural Mosaic Foundation (via Data for Good Calgary)

The Canadian Cultural Mosaic Foundation tracks race-based hate incidents across Canada, drawn from English-language media reports, and publishes what the data shows. Census figures are used for one thing only: turning counts into rates per 100,000 people so provinces can be compared fairly.

Where it started

The Foundation’s data lived in an Oracle 19c database with an APEX 24.2 entry application of 74 pages, and its public analysis had been rebuilt more than once by volunteers: R Shiny in 2020, then Dash and Streamlit dashboards. Each tool worked; together they meant four places to maintain and three overlapping ways to enter an incident.

A migration you can prove

DB Realm moved all 443 database objects (55 tables, 32 packages, 30 triggers and the rest) to Oracle AI Database 26ai with zero invalid objects at the end. Row counts were compared table by table against the source, and text and binary columns were compared byte for byte. The legacy application was inventoried down to its regions, items and buttons so that nothing was retired by accident.

Security, rebuilt to fail closed

The legacy pages checked permissions in a way that let a user through if a check was missing. The rebuild inverts that: role-based access per feature, refusing by default, with hardened password storage, lockout after repeated failures, and audit logs for sign-ins and user administration. During the work a public page was found to expose incident-level rows; it now sits behind sign-in, and the public sees aggregates only.

News monitoring with a human in the loop

Every six hours the database checks 28 Canadian news feeds and queues new articles for review. A reviewer approves an article into an incident record, through the same table APIs as manual entry, or rejects it with a reason. The pipeline is ready for AI classification and duplicate detection, but ships in manual mode: the Foundation decides if and when a model is involved.

Ready to Build Something Similar?

Let's discuss how we can bring the same expertise to your organization.

Start a Conversation