CREATE TABLE IF NOT EXISTS posts (
    id          INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
    user        TEXT NOT NULL,
    service     TEXT NOT NULL,
    identifier  TEXT NOT NULL,
    parent      INTEGER NULL REFERENCES posts(id),
    root        INTEGER NULL REFERENCES posts(id),
    reposted    INTEGER NULL REFERENCES posts(id),
    extra_data  TEXT NULL
);

CREATE TABLE IF NOT EXISTS mappings (
    original INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    mapped   INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    UNIQUE(original, mapped)
);
