Modelling SCORM Attempt Data in Postgres

Nearly every LMS build reaches the same fork within a fortnight: how do you store what the course player sends back? It looks trivial. It is not, and the wrong answer is expensive because you usually discover it a year later when someone from compliance asks a question your database physically cannot answer.

The trap: SCORM 1.2 has one field where you need two

SCORM 1.2 reports progress through cmi.core.lesson_status, a single value that can be passed, failed, completed, incomplete, browsed or not attempted. Read that list again and the problem appears: it mixes two unrelated ideas. "Did the learner finish?" and "did the learner pass?" are different questions, and 1.2 crushes them into one column.

SCORM 2004 fixed this by splitting it into cmi.completion_status and cmi.success_status. But most authoring tools still export 1.2, so the naive move is to store lesson_status as it arrives and be done.

Then compliance asks: show me everyone who finished the course but failed the assessment. With one column you cannot answer it — a row saying failed tells you nothing about whether they reached the end, and a row saying completed has silently discarded the result. The data was never wrong; it was never captured.

Store the richer model, map the poorer one into it

The rule we follow: persist the most expressive standard you may ever need, and translate older inputs on the way in. Store the SCORM 2004 shape even when the course speaks 1.2. Translating 1.2 into two columns is lossless. Going the other direction is not, and that asymmetry decides the schema.

CREATE TABLE attempt (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    learner_id      bigint      NOT NULL,
    activity_id     bigint      NOT NULL,
    attempt_number  int         NOT NULL,

    completion_status text NOT NULL DEFAULT 'unknown'
        CHECK (completion_status IN ('completed','incomplete','not attempted','unknown')),
    success_status    text NOT NULL DEFAULT 'unknown'
        CHECK (success_status IN ('passed','failed','unknown')),

    score_scaled    numeric(4,3) CHECK (score_scaled BETWEEN -1 AND 1),
    score_raw       numeric(9,2),
    total_time      interval    NOT NULL DEFAULT '0 seconds',
    location        text,
    suspend_data    text,

    started_at      timestamptz NOT NULL DEFAULT now(),
    completed_at    timestamptz,

    UNIQUE (learner_id, activity_id, attempt_number),
    CONSTRAINT completed_needs_timestamp
        CHECK (completion_status <> 'completed' OR completed_at IS NOT NULL)
);

A few of those choices are load-bearing:

• attempt_number in the unique key. Attempts are not updates. Compliance regimes routinely need the second attempt without destroying the first, and if you overwrite one row per learner per course, that history is gone forever. This is the single most common irreversible mistake in LMS schemas.
• total_time as interval, not text. SCORM sends time as a formatted string — 0000:04:12.00 in 1.2, an ISO 8601 duration like PT4M12S in 2004. Both are presentation formats. Parse once at the boundary and let Postgres own the arithmetic, or you will be summing strings in application code forever.
• score_scaled as numeric, never float. Scores decide certification. Binary floating point is the wrong tool for a number a human will dispute.
• The completed_needs_timestamp CHECK. A course marked complete with no completion date is not a curiosity, it is an audit failure. Constraints are cheaper than the report that finds them later.

The mapping, as a function

Do the 1.2 translation once, at the edge, rather than scattering the conditional across your codebase:

CREATE FUNCTION map_lesson_status(lesson_status text)
RETURNS TABLE (completion_status text, success_status text)
LANGUAGE sql IMMUTABLE AS $$
    SELECT
        CASE lesson_status
            WHEN 'passed'        THEN 'completed'
            WHEN 'failed'        THEN 'completed'
            WHEN 'completed'     THEN 'completed'
            WHEN 'incomplete'    THEN 'incomplete'
            WHEN 'browsed'       THEN 'incomplete'
            WHEN 'not attempted' THEN 'not attempted'
            ELSE 'unknown'
        END,
        CASE lesson_status
            WHEN 'passed' THEN 'passed'
            WHEN 'failed' THEN 'failed'
            ELSE 'unknown'
        END;
$$;

The important line is 'failed' -> ('completed', 'failed'). A learner who failed did reach the assessment, so their completion is real and their success is not. That one row is exactly the compliance question from earlier, and it is now answerable with a trivial WHERE.

browsed is the judgement call: 1.2 uses it for someone reviewing in browse mode, which we treat as incomplete rather than completed, because skimming is not finishing. Reasonable teams differ — the point is to decide deliberately and write it down, not to let it fall out of whichever CASE branch happened to come first.

Index for the question you actually ask

The query an LMS runs constantly is "who is still outstanding?", not "who finished". Over time the completed rows dominate the table, so a partial index keeps the working set small and stays proportional to the people you still care about:

CREATE INDEX attempt_in_progress
    ON attempt (activity_id, learner_id)
    WHERE completion_status <> 'completed';

Does it work?

Every statement above was run against Postgres 16 before publishing. Accumulating two sessions of 00:04:12 and 00:57:03 into total_time yields 01:01:15, and inserting a completed row without a timestamp is correctly rejected by the constraint rather than quietly stored. We would rather ship code we have executed than code that merely reads well.

Where this sits

This is the layer underneath the reporting that clients actually see — the dashboards, the completion reports, the certificates. It is the same tracking foundation behind Pragyanta, our own learning platform. If you are specifying an LMS and want the data model right before anything is built on top of it, that is the work we do: SCORM / xAPI / Articulate integration and custom LMS development, with the reporting layer in student dashboards and admin.

Planning a platform where the tracking has to stand up to an audit? Book a free LMS consultation.