qbit-file-gateway/docs/ddl/file_gateway.sql

-- File Gateway schema. PostgreSQL and H2. Timestamps are UTC, matching QQQ RDBMS binding.

CREATE TABLE file_gateway_account (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   username VARCHAR(1024) NOT NULL UNIQUE,
   label VARCHAR(1024),
   account_type VARCHAR(1024),
   user_id VARCHAR(1024),
   owner_user_id VARCHAR(1024),
   status VARCHAR(1024),
   root_mode VARCHAR(1024),
   allowed_cidrs TEXT,
   expires_at TIMESTAMP,
   formats VARCHAR(1024),
   max_concurrent_sessions INTEGER,
   max_operations_per_minute INTEGER,
   max_file_size_mb INTEGER,
   max_upload_mb_per_day INTEGER,
   max_download_mb_per_day INTEGER,
   dedupe_uploads BOOLEAN,
   notify_on_success BOOLEAN,
   pgp_public_key TEXT,
   pgp_decrypt_uploads BOOLEAN,
   pgp_encrypt_downloads BOOLEAN,
   operation_window_at TIMESTAMP,
   operation_window_count INTEGER,
   failed_login_count INTEGER,
   failed_login_window_at TIMESTAMP,
   locked_until TIMESTAMP,
   last_login_at TIMESTAMP,
   last_login_ip VARCHAR(1024)
);

CREATE TABLE file_gateway_ssh_key (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   account_id INTEGER,
   label VARCHAR(1024),
   public_key TEXT,
   fingerprint VARCHAR(1024) NOT NULL UNIQUE,
   key_type VARCHAR(1024),
   key_bits INTEGER,
   expires_at TIMESTAMP,
   revoked_at TIMESTAMP,
   last_used_at TIMESTAMP,
   last_used_ip VARCHAR(1024)
);

CREATE TABLE file_gateway_alias (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(1024) NOT NULL UNIQUE,
   path VARCHAR(1024) NOT NULL UNIQUE,
   kind VARCHAR(1024),
   account_id INTEGER,
   table_name VARCHAR(1024),
   saved_bulk_load_profile_id INTEGER,
   saved_view_id INTEGER,
   saved_report_id INTEGER,
   format VARCHAR(1024),
   file_name VARCHAR(1024),
   consumer_name VARCHAR(1024),
   is_active BOOLEAN,
   description TEXT
);

CREATE TABLE file_gateway_upload_job (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   account_id INTEGER,
   alias_id INTEGER,
   connection_log_id INTEGER,
   remote_address VARCHAR(1024),
   table_name VARCHAR(1024),
   saved_bulk_load_profile_id INTEGER,
   consumer_name VARCHAR(1024),
   consumer_reference VARCHAR(1024),
   path VARCHAR(1024),
   file_name VARCHAR(1024),
   size_bytes BIGINT,
   sha256 VARCHAR(1024),
   storage_key VARCHAR(1024),
   status VARCHAR(1024),
   duplicate_of_job_id INTEGER,
   process_uuid VARCHAR(1024),
   inserted_count INTEGER,
   error_count INTEGER,
   result_summary TEXT,
   results_storage_key VARCHAR(1024),
   results_size_bytes BIGINT,
   plaintext_storage_key VARCHAR(1024),
   plaintext_sha256 VARCHAR(1024),
   plaintext_size_bytes BIGINT,
   error_message TEXT,
   attempts INTEGER,
   next_attempt_at TIMESTAMP,
   claimed_by VARCHAR(1024),
   claimed_at TIMESTAMP,
   queued_at TIMESTAMP,
   started_at TIMESTAMP,
   finished_at TIMESTAMP,
   acknowledged_at TIMESTAMP
);

CREATE TABLE file_gateway_outbox_file (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   alias_id INTEGER,
   file_name VARCHAR(1024),
   size_bytes BIGINT,
   sha256 VARCHAR(1024),
   storage_key VARCHAR(1024),
   status VARCHAR(1024),
   first_downloaded_at TIMESTAMP,
   download_count INTEGER,
   on_downloaded_pending BOOLEAN DEFAULT FALSE,
   removed_at TIMESTAMP,
   expires_at TIMESTAMP,
   created_by_consumer VARCHAR(1024)
);

CREATE TABLE file_gateway_transfer_log (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   account_id INTEGER,
   connection_log_id INTEGER,
   operation VARCHAR(1024),
   path VARCHAR(1024),
   object_kind VARCHAR(1024),
   object_id VARCHAR(1024),
   format VARCHAR(1024),
   bytes BIGINT,
   sha256 VARCHAR(1024),
   duration_ms BIGINT,
   outcome VARCHAR(1024),
   message TEXT,
   client_channel VARCHAR(1024),
   upload_job_id INTEGER
);

CREATE TABLE file_gateway_connection_log (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   account_id INTEGER,
   username VARCHAR(1024),
   remote_ip VARCHAR(1024),
   client_version VARCHAR(1024),
   key_fingerprint VARCHAR(1024),
   outcome VARCHAR(1024),
   session_id VARCHAR(1024),
   pod_name VARCHAR(1024),
   connected_at TIMESTAMP,
   last_seen_at TIMESTAMP,
   disconnected_at TIMESTAMP,
   end_requested_at TIMESTAMP,
   disconnect_reason TEXT,
   bytes_in BIGINT,
   bytes_out BIGINT,
   operation_count BIGINT,
   client_channel VARCHAR(1024)
);

CREATE TABLE file_gateway_server (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   pod_name VARCHAR(1024) NOT NULL UNIQUE,
   host_key_fingerprint VARCHAR(1024),
   port INTEGER,
   started_at TIMESTAMP,
   last_seen_at TIMESTAMP
);

-- Indexes for the job queue, dedupe, live sessions, home queries and retention.
CREATE INDEX file_gateway_transfer_log_account_date ON file_gateway_transfer_log (account_id, create_date);
CREATE INDEX file_gateway_transfer_log_date ON file_gateway_transfer_log (create_date);
CREATE INDEX file_gateway_connection_log_account_live ON file_gateway_connection_log (account_id, disconnected_at);
CREATE INDEX file_gateway_connection_log_date ON file_gateway_connection_log (create_date);
CREATE INDEX file_gateway_upload_job_status_next ON file_gateway_upload_job (status, next_attempt_at);
CREATE INDEX file_gateway_upload_job_dedupe ON file_gateway_upload_job (account_id, sha256);
CREATE INDEX file_gateway_upload_job_date ON file_gateway_upload_job (create_date);
CREATE INDEX file_gateway_outbox_file_alias_status ON file_gateway_outbox_file (alias_id, status);
CREATE INDEX file_gateway_ssh_key_account ON file_gateway_ssh_key (account_id);