weiro/sql/schema/02_upload.up.sql

CREATE TABLE uploads (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    site_id    INT  NOT NULL,
    guid       TEXT NOT NULL,
    mime_type  TEXT NOT NULL,
    filename   TEXT NOT NULL,
    file_size  INT  NOT NULL,
    slug       TEXT NOT NULL,
    alt        TEXT NOT NULL,
    created_at INT  NOT NULL,
    FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);
CREATE INDEX idx_uploads_site ON uploads (site_id);
CREATE UNIQUE INDEX idx_uploads_guid ON uploads (guid);
CREATE UNIQUE INDEX idx_uploads_site_slug ON uploads (site_id, slug);

CREATE TABLE pending_uploads (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    site_id           INT  NOT NULL,
    guid              TEXT NOT NULL,
    user_id           INT  NOT NULL,
    filename          TEXT NOT NULL,
    file_size         INT  NOT NULL,
    mime_type         TEXT NOT NULL,
    upload_started_at INT  NOT NULL,
    FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX idx_pending_uploads_guid ON pending_uploads (guid);