Anki 2 annotated schema

a.md

see https://github.com/ankidroid/Anki-Android/wiki/Database-Structure for a more maintained version of this

schema.sql

-- cards are what you review. easy!
CREATE TABLE cards (
    id              integer primary key,
      -- the epoch milliseconds of when the card was created
    nid             integer not null,
      -- notes.id
    did             integer not null,
      -- deck id (available in col table)
    ord             integer not null,
      -- ordinal, seems like. for when a model has multiple templates, or thereabouts
    mod             integer not null,
      -- modified time as epoch seconds
    usn             integer not null,
      -- "From the source code it appears Anki increments this number each time you synchronize with AnkiWeb and applies this number to the cards that were synchronized. My database is up to 1230 for the collection and my cards have various numbers up to 1229." -- contributed by Fletcher Moore
    type            integer not null,
      -- in anki1, type was whether the card was suspended, etc. seems to be the same. values are 0 (suspended), 1 (maybe "learning"?), 2 (normal)
    queue           integer not null,
      -- "queue in the cards table refers to if the card is "new" = 0, "learning" = 1 or 3, "review" = 2 (I don't understand how a 3 occurs, but I have 3 notes out of 23,000 with this value.)" -- contributed by Fletcher Moore
    due             integer not null,
    ivl             integer not null,
      -- interval (used in SRS algorithm)
    factor          integer not null,
      -- factor (used in SRS algorithm)
    reps            integer not null,
      -- number of reviews
    lapses          integer not null,
      -- possibly the number of times the card went from a "was answered correctly" to "was answered incorrectly" state
    left            integer not null,
      -- 0 for all my cards
    odue            integer not null,
      -- 0 for all my cards
    odid            integer not null,
      -- 0 for all my cards
    flags           integer not null,
      -- 0 for all my cards
    data            text not null
      -- currently unused for decks imported from anki1. maybe extra data for plugins?
);

-- col is a collection of decks
CREATE TABLE col (
    id              integer primary key,
      -- seems to be an autoincrement
    crt             integer not null,
      -- there's the created timestamp
    mod             integer not null,
      -- last modified in milliseconds
    scm             integer not null,
      -- a timestamp in milliseconds. "schema mod time" - contributed by Fletcher Moore
    ver             integer not null,
      -- version? I have "11"
    dty             integer not null,
      -- 0
    usn             integer not null,
      -- 0
    ls              integer not null,
      -- "last sync time" - contributed by Fletcher Moore
    conf            text not null,
      -- json blob of configuration
    models          text not null,
      -- json object with keys being ids(epoch ms), values being configuration
    decks           text not null,
      -- json object with keys being ids(epoch ms), values being configuration
    dconf           text not null,
      -- json object. deck configuration?
    tags            text not null
      -- a cache of tags used in this collection (probably for autocomplete etc)
);

-- dunno what this is yet. my deck has 7. usn is -1, type is 1, and oid varies.
CREATE TABLE graves (
    usn             integer not null,
    oid             integer not null,
    type            integer not null
);

-- notes are the new facts, which can be used by different cards.
CREATE TABLE notes (
    id              integer primary key,
      -- epoch seconds of when the note was created
    guid            text not null,
      -- globally unique id, almost certainly used for syncing
    mid             integer not null,
      -- model id
    mod             integer not null,
      -- modified timestamp, epoch seconds
    usn             integer not null,
      -- -1 for all my notes
    tags            text not null,
      -- space-separated string of tags. seems to include space at the beginning and end of the field, almost certainly for LIKE "% tag %" queries
    flds            text not null,
      -- the values of the fields in this note. separated by 0x1f (31).
    sfld            integer not null,
      -- the text of the first field, used for anki2's new (simplistic) uniqueness checking
    csum            integer not null,
      -- dunno. not a unique field, but very few repeats
    flags           integer not null,
      -- 0 for all my notes
    data            text not null
      -- empty string for all my notes
);

-- revlog is review history; it has a row for every single review you've ever done!
CREATE TABLE revlog (
    id              integer primary key,
       -- epoch-seconds timestamp of when you did the review
    cid             integer not null,
       -- cards.id
    usn             integer not null,
       -- all my reviews have -1
    ease            integer not null,
       -- which button you pushed to score your recall. 1(wrong), 2(hard), 3(ok), 4(easy)
    ivl             integer not null,
       -- interval
    lastIvl         integer not null,
       -- last interval
    factor          integer not null,
      -- factor
    time            integer not null,
       -- how many milliseconds your review took, up to 60000 (60s)
    type            integer not null
);


CREATE INDEX ix_cards_nid on cards (nid);
CREATE INDEX ix_cards_sched on cards (did, queue, due);
CREATE INDEX ix_cards_usn on cards (usn);
CREATE INDEX ix_notes_csum on notes (csum);
CREATE INDEX ix_notes_usn on notes (usn);
CREATE INDEX ix_revlog_cid on revlog (cid);
CREATE INDEX ix_revlog_usn on revlog (usn);

col.models describes specifically model configuration. It's JSON: keys are the model IDs (epoch ms) and here's my crack at describing its fields:

{
    css : "CSS, shared for all templates",
    did :
        "Deck ID, presumably of the first deck that used this model, since "
        "multiple decks can use the same model. 1 if unused in any deck?",
    flds : [
             "Array of objects with keys:",
             {
               font : "display font",
               media : "?",
               name : "field name",
               ord : "matches cards.ord: the template number",
               rtl : "boolean, right-to-left script",
               size : "? number",
               sticky : "?"
             }
           ],
    id : "string, model ID, matches cards.mid",
    latexPost : "suffix for Latex",
    latexPre : "prefix for Latex",
    mod : "? number",
    name : "model name",
    req : [
            "Array of 3 elements:",
            [
              "array index, 0, 1, ...",
              '? string, "all"',
              "another array",
              ["appears to be the array index again"]
            ]
          ],
    sortf : "? number, 0 or 5 in mine",
    tags : "? array of 0 length",
    tmpls : [
              "array of objects representing templates",
              {
                afmt : "answer template string",
                bafmt : "? some other template string?",
                bqfmt : "? some other template string?",
                did : "? null",
                name : "template name",
                ord : "template number, see flds",
                qfmt : "question format string"
              }
            ],
    type : "? 0 or 1",
    usn :
        "? perhaps related to usn elsewhere in database (Ankiweb.net "
        "synchronization counter)",
    vers : "? zero-length array in mine"
}

cards.ord indeed seems to be an index, starting at 0, of the cards associated with a note, i.e., a cloze-deletion note with six clozes will have six cards, with their own cards.id, associated with the same note id cards.nid, with cards.ord differentiating them. I imagine it's the same for the front-and-back cards too.

Thanks for posting this! I was browsing anki's source code and found the following clarifications:
cards.lapses is the number of times a review card is failed back to relearning. Failed learning and relearning cards are not counted.
cards.type seems to be "learning" = 0, "review" = 1, "lapse/relearn" = 2, "filtered" = 3
cards.queue seems to be "suspended" = -1 "new" = 0, "learn" = 1, "review" = 2, "relearn" = 3

I found this clarification in the cards.py:

# Type: 0=new, 1=learning, 2=due
# Queue: same as above, and:
#        -1=suspended, -2=user buried, -3=sched buried
# Due is used differently for different queues.
# - new queue: note id or random int
# - rev queue: integer day
# - lrn queue: integer timestamp

Also, cards.odid and cards.odue seem to be serve the same function for cards in filtered decks as cards.did and cards.due in regular decks.

Thanks everyone! I was just starting to annotate the source myself, when I decided to google anki ord mod usn and found this!

graves.type denotes the type of data that was deleted: 0 stands for a card, 1 for a note and 2 for a deck.
see the consts REM_CARD, REM_NOTE and REM_DECK defined in https://github.com/dae/anki/blob/master/anki/consts.py and used in sync.py

I've filled in most of the gaps, corrected some errors, and added it to the AnkiDroid wiki. Thanks all:
https://github.com/ankidroid/Anki-Android/wiki/Database-Structure

Just stumbled upon all this activity 😂 Added a comment to the top pointing to the AnkiDroid wiki. Thanks!

Based