-- 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);