852 lines
44 KiB
PL/PgSQL
852 lines
44 KiB
PL/PgSQL
-- GENERATED FROM backend/src/db/schema.sql; DO NOT EDIT init.sql DIRECTLY.
|
|
-- GENERATED by npm run schema:generate from baseline.sql + migrations; DO NOT EDIT.
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm" WITH SCHEMA public;
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA public;
|
|
|
|
CREATE FUNCTION public.update_updated_at() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TABLE public.api_keys (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
name character varying(100) NOT NULL,
|
|
key character varying(128) NOT NULL,
|
|
key_hash character varying(64),
|
|
permissions jsonb DEFAULT '["upload"]'::jsonb,
|
|
enabled boolean DEFAULT true,
|
|
last_used_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.chunk_upload_chunks (
|
|
upload_id uuid NOT NULL,
|
|
chunk_index integer NOT NULL,
|
|
size bigint NOT NULL,
|
|
sha256 character varying(64) NOT NULL,
|
|
path character varying(1000) NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT chunk_upload_chunks_chunk_index_check CHECK ((chunk_index >= 0)),
|
|
CONSTRAINT chunk_upload_chunks_sha256_check CHECK (((sha256)::text ~ '^[0-9a-f]{64}$'::text)),
|
|
CONSTRAINT chunk_upload_chunks_size_check CHECK ((size > 0))
|
|
);
|
|
|
|
CREATE TABLE public.chunk_upload_reconciliations (
|
|
operation_id uuid NOT NULL,
|
|
upload_id uuid NOT NULL,
|
|
completion_token uuid NOT NULL,
|
|
provider character varying(50) NOT NULL,
|
|
account_id uuid,
|
|
stored_path character varying(2000),
|
|
file_id uuid,
|
|
object_state character varying(20) NOT NULL,
|
|
index_state character varying(20) NOT NULL,
|
|
reason text NOT NULL,
|
|
status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
|
|
resolved_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
resolution character varying(30),
|
|
lease_token uuid,
|
|
lease_expires_at timestamp with time zone,
|
|
attempts integer DEFAULT 0 NOT NULL,
|
|
CONSTRAINT chunk_upload_reconciliations_index_state_check CHECK (((index_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
|
|
CONSTRAINT chunk_upload_reconciliations_object_state_check CHECK (((object_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
|
|
CONSTRAINT chunk_upload_reconciliations_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'resolved'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.chunk_upload_sessions (
|
|
upload_id uuid NOT NULL,
|
|
owner_id character varying(64) NOT NULL,
|
|
filename character varying(255) NOT NULL,
|
|
mime_type character varying(100) NOT NULL,
|
|
folder character varying(255),
|
|
total_size bigint NOT NULL,
|
|
total_chunks integer NOT NULL,
|
|
received_bytes bigint DEFAULT 0 NOT NULL,
|
|
status character varying(20) DEFAULT 'open'::character varying NOT NULL,
|
|
target_provider character varying(50) NOT NULL,
|
|
target_account_id uuid,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
completion_token uuid,
|
|
completion_expires_at timestamp with time zone,
|
|
completed_file_id uuid,
|
|
last_error text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT chunk_upload_sessions_check CHECK (((received_bytes >= 0) AND (received_bytes <= total_size))),
|
|
CONSTRAINT chunk_upload_sessions_status_check CHECK (((status)::text = ANY ((ARRAY['open'::character varying, 'completing'::character varying, 'completed'::character varying, 'cancelled'::character varying, 'failed'::character varying])::text[]))),
|
|
CONSTRAINT chunk_upload_sessions_total_chunks_check CHECK ((total_chunks > 0)),
|
|
CONSTRAINT chunk_upload_sessions_total_size_check CHECK ((total_size > 0))
|
|
);
|
|
|
|
CREATE TABLE public.files (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
name character varying(255) NOT NULL,
|
|
stored_name character varying(255) NOT NULL,
|
|
type character varying(20) NOT NULL,
|
|
mime_type character varying(100),
|
|
size bigint NOT NULL,
|
|
path character varying(500) NOT NULL,
|
|
thumbnail_path character varying(500),
|
|
preview_path character varying(500),
|
|
derivative_status character varying(20) DEFAULT 'not_required'::character varying NOT NULL,
|
|
derivative_error text,
|
|
derivative_source_path character varying(1000),
|
|
derivative_cleanup_source boolean DEFAULT false NOT NULL,
|
|
derivative_attempts integer DEFAULT 0 NOT NULL,
|
|
derivative_started_at timestamp with time zone,
|
|
width integer,
|
|
height integer,
|
|
source character varying(50) DEFAULT 'web'::character varying,
|
|
folder character varying(255),
|
|
storage_account_id uuid,
|
|
is_favorite boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT files_derivative_status_check CHECK (((derivative_status)::text = ANY ((ARRAY['queued'::character varying, 'processing'::character varying, 'ready'::character varying, 'failed'::character varying, 'not_required'::character varying])::text[]))),
|
|
CONSTRAINT files_type_check CHECK (((type)::text = ANY ((ARRAY['image'::character varying, 'video'::character varying, 'audio'::character varying, 'document'::character varying, 'other'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.oauth_pending_flows (
|
|
state_hash character varying(64) NOT NULL,
|
|
provider character varying(32) NOT NULL,
|
|
auth_session_hash character varying(64) NOT NULL,
|
|
redirect_uri text NOT NULL,
|
|
pending_config jsonb NOT NULL,
|
|
flow_nonce character varying(128) NOT NULL,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.schema_migrations (
|
|
version bigint NOT NULL,
|
|
name text NOT NULL,
|
|
checksum character varying(64) NOT NULL,
|
|
applied_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.storage_account_cooldowns (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
storage_account_id uuid,
|
|
provider character varying(50) NOT NULL,
|
|
reason character varying(100) NOT NULL,
|
|
cooldown_until timestamp with time zone NOT NULL,
|
|
last_error text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.storage_account_leases (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
storage_account_id uuid NOT NULL,
|
|
purpose character varying(50) NOT NULL,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
released_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.storage_accounts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
type character varying(50) NOT NULL,
|
|
name character varying(255) NOT NULL,
|
|
config jsonb NOT NULL,
|
|
is_active boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
last_probe_status character varying(20),
|
|
last_probe_error text,
|
|
last_probed_at timestamp with time zone
|
|
);
|
|
|
|
CREATE TABLE public.system_settings (
|
|
key character varying(255) NOT NULL,
|
|
value text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.task_center_dismissals (
|
|
source_type character varying(30) NOT NULL,
|
|
task_id character varying(128) NOT NULL,
|
|
task_updated_at timestamp with time zone NOT NULL,
|
|
dismissed_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.telegram_account_source_access (
|
|
account_id uuid NOT NULL,
|
|
source_key text NOT NULL,
|
|
scope character varying(20) DEFAULT 'download'::character varying NOT NULL,
|
|
access_state character varying(20) DEFAULT 'unknown'::character varying NOT NULL,
|
|
last_error text,
|
|
checked_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_account_source_access_access_state_check CHECK (((access_state)::text = ANY ((ARRAY['unknown'::character varying, 'allowed'::character varying, 'denied'::character varying])::text[]))),
|
|
CONSTRAINT telegram_account_source_access_scope_check CHECK (((scope)::text = ANY ((ARRAY['download'::character varying, 'scan'::character varying, 'metadata'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_auth (
|
|
user_id bigint NOT NULL,
|
|
authenticated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.telegram_background_jobs (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id bigint NOT NULL,
|
|
chat_id bigint,
|
|
kind character varying(50) NOT NULL,
|
|
source text NOT NULL,
|
|
status text DEFAULT 'pending'::text NOT NULL,
|
|
scan_status text DEFAULT 'pending'::text,
|
|
download_status text DEFAULT 'pending'::text,
|
|
scan_cursor jsonb DEFAULT '{}'::jsonb,
|
|
cooldown_until timestamp with time zone,
|
|
paused_at timestamp with time zone,
|
|
cancelled_at timestamp with time zone,
|
|
params jsonb DEFAULT '{}'::jsonb,
|
|
total_count integer DEFAULT 0,
|
|
enqueued_count integer DEFAULT 0,
|
|
skipped_count integer DEFAULT 0,
|
|
duplicate_count integer DEFAULT 0,
|
|
error text,
|
|
started_at timestamp with time zone,
|
|
finished_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
ad_decision_id uuid,
|
|
assigned_account_id uuid
|
|
);
|
|
|
|
CREATE TABLE public.telegram_channel_subscriptions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id bigint NOT NULL,
|
|
chat_id bigint,
|
|
source text NOT NULL,
|
|
source_original text,
|
|
source_type text DEFAULT 'public'::text,
|
|
title text,
|
|
last_message_id integer DEFAULT 0,
|
|
folder_override text,
|
|
enabled boolean DEFAULT true,
|
|
disabled_reason text,
|
|
disabled_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
last_scan_at timestamp with time zone,
|
|
last_success_at timestamp with time zone,
|
|
last_error text,
|
|
last_result jsonb,
|
|
next_scan_at timestamp with time zone,
|
|
target_mode character varying(20) DEFAULT 'follow_global'::character varying NOT NULL,
|
|
target_provider character varying(50),
|
|
target_account_id uuid,
|
|
ad_filter_mode character varying(20) DEFAULT 'off'::character varying NOT NULL,
|
|
file_filter_mode text DEFAULT 'all'::text NOT NULL,
|
|
file_filter_types text[] DEFAULT '{}'::text[] NOT NULL,
|
|
CONSTRAINT telegram_subscription_ad_filter_mode_check CHECK (((ad_filter_mode)::text = ANY ((ARRAY['off'::character varying, 'conservative'::character varying, 'aggressive'::character varying])::text[]))),
|
|
CONSTRAINT telegram_subscription_file_filter_mode_check CHECK ((file_filter_mode = ANY (ARRAY['all'::text, 'include'::text, 'exclude'::text]))),
|
|
CONSTRAINT telegram_subscription_file_filter_types_check CHECK (((file_filter_types <@ ARRAY['images'::text, 'raw-images'::text, 'videos'::text, 'audio'::text, 'archives'::text, 'apps'::text, 'text'::text, 'word-docs'::text, 'pdfs'::text, 'spreadsheets'::text, 'presentations'::text, 'ebooks'::text, 'code'::text, 'fonts'::text, 'design'::text, 'torrents'::text, 'documents'::text, 'other'::text]) AND (array_position(file_filter_types, NULL::text) IS NULL) AND (((file_filter_mode = 'all'::text) AND (cardinality(file_filter_types) = 0)) OR ((file_filter_mode = ANY (ARRAY['include'::text, 'exclude'::text])) AND (cardinality(file_filter_types) > 0))))),
|
|
CONSTRAINT telegram_subscription_target_mode_check CHECK (((target_mode)::text = ANY ((ARRAY['follow_global'::character varying, 'fixed'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_download_attempts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
source_key text NOT NULL,
|
|
scope character varying(20) DEFAULT 'download'::character varying NOT NULL,
|
|
job_id uuid,
|
|
item_id uuid,
|
|
lease_token uuid,
|
|
status character varying(20) DEFAULT 'running'::character varying NOT NULL,
|
|
error text,
|
|
started_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
finished_at timestamp with time zone,
|
|
CONSTRAINT telegram_download_attempts_check CHECK (((((status)::text = 'running'::text) AND (finished_at IS NULL)) OR (((status)::text <> 'running'::text) AND (finished_at IS NOT NULL)))),
|
|
CONSTRAINT telegram_download_attempts_scope_check CHECK (((scope)::text = ANY ((ARRAY['download'::character varying, 'scan'::character varying, 'metadata'::character varying])::text[]))),
|
|
CONSTRAINT telegram_download_attempts_status_check CHECK (((status)::text = ANY ((ARRAY['running'::character varying, 'succeeded'::character varying, 'failed'::character varying, 'cancelled'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_download_items (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
job_id uuid,
|
|
source text NOT NULL,
|
|
source_peer text,
|
|
origin text DEFAULT 'channel'::text,
|
|
message_id integer NOT NULL,
|
|
grouped_id text,
|
|
shared_caption text,
|
|
group_index integer,
|
|
group_size integer,
|
|
channel_post_id integer,
|
|
file_name text,
|
|
mime_type text,
|
|
file_category text,
|
|
generated_name boolean DEFAULT false,
|
|
total_size bigint DEFAULT 0,
|
|
folder_override text,
|
|
status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
|
|
attempts integer DEFAULT 0,
|
|
error text,
|
|
last_error text,
|
|
locked_at timestamp with time zone,
|
|
completed_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
lease_token uuid,
|
|
lease_expires_at timestamp with time zone
|
|
);
|
|
|
|
CREATE TABLE public.telegram_notification_digest (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id bigint NOT NULL,
|
|
chat_id character varying(80) NOT NULL,
|
|
kind character varying(40) NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
claimed_at timestamp with time zone,
|
|
delivered_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.telegram_notification_preferences (
|
|
user_id bigint NOT NULL,
|
|
chat_id text NOT NULL,
|
|
preferences jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.telegram_path_states (
|
|
chat_id text NOT NULL,
|
|
mode character varying(10) NOT NULL,
|
|
folder text NOT NULL,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_path_states_mode_check CHECK (((mode)::text = ANY ((ARRAY['once'::character varying, 'session'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_subscription_ad_decisions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
subscription_id uuid NOT NULL,
|
|
source_peer text NOT NULL,
|
|
message_id integer NOT NULL,
|
|
grouped_id text,
|
|
message_ids integer[] DEFAULT '{}'::integer[] NOT NULL,
|
|
decision character varying(20) NOT NULL,
|
|
score integer DEFAULT 0 NOT NULL,
|
|
reasons jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
text_excerpt text,
|
|
text_fingerprint text,
|
|
domains text[] DEFAULT '{}'::text[] NOT NULL,
|
|
usernames text[] DEFAULT '{}'::text[] NOT NULL,
|
|
media_keys text[] DEFAULT '{}'::text[] NOT NULL,
|
|
matched_rule_ids uuid[] DEFAULT '{}'::uuid[] NOT NULL,
|
|
manual_label character varying(20),
|
|
manually_reviewed_at timestamp with time zone,
|
|
restored_job_id uuid,
|
|
restored_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
restore_status character varying(20) DEFAULT 'not_requested'::character varying NOT NULL,
|
|
restore_error text,
|
|
restore_attempted_at timestamp with time zone,
|
|
CONSTRAINT telegram_subscription_ad_decisions_decision_check CHECK (((decision)::text = ANY ((ARRAY['allow'::character varying, 'review'::character varying, 'blocked'::character varying])::text[]))),
|
|
CONSTRAINT telegram_subscription_ad_decisions_manual_label_check CHECK (((manual_label)::text = ANY ((ARRAY['ad'::character varying, 'normal'::character varying])::text[]))),
|
|
CONSTRAINT telegram_subscription_ad_decisions_score_check CHECK (((score >= 0) AND (score <= 100))),
|
|
CONSTRAINT telegram_subscription_ad_restore_status_check CHECK (((restore_status)::text = ANY ((ARRAY['not_requested'::character varying, 'restoring'::character varying, 'restored'::character varying, 'failed'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_subscription_ad_rules (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
subscription_id uuid NOT NULL,
|
|
kind character varying(20) NOT NULL,
|
|
action character varying(10) NOT NULL,
|
|
pattern text NOT NULL,
|
|
label text,
|
|
enabled boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_subscription_ad_rules_action_check CHECK (((action)::text = ANY ((ARRAY['allow'::character varying, 'block'::character varying])::text[]))),
|
|
CONSTRAINT telegram_subscription_ad_rules_kind_check CHECK (((kind)::text = ANY ((ARRAY['keyword'::character varying, 'domain'::character varying, 'username'::character varying, 'template'::character varying, 'media'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_target_states (
|
|
chat_id text NOT NULL,
|
|
mode character varying(10) NOT NULL,
|
|
provider character varying(50) NOT NULL,
|
|
account_id uuid,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_target_states_check CHECK (((((provider)::text = 'local'::text) AND (account_id IS NULL)) OR (((provider)::text <> 'local'::text) AND (account_id IS NOT NULL)))),
|
|
CONSTRAINT telegram_target_states_mode_check CHECK (((mode)::text = ANY ((ARRAY['once'::character varying, 'session'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_task_receipts (
|
|
id text NOT NULL,
|
|
chat_id text NOT NULL,
|
|
user_id bigint NOT NULL,
|
|
job_ids jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
group_ids jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
failures jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.telegram_user_accounts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
telegram_user_id text NOT NULL,
|
|
username text,
|
|
display_name text,
|
|
session_ciphertext text NOT NULL,
|
|
enabled boolean DEFAULT true NOT NULL,
|
|
health_state character varying(24) DEFAULT 'healthy'::character varying NOT NULL,
|
|
cooldown_until timestamp with time zone,
|
|
weight numeric(8,3) DEFAULT 1 NOT NULL,
|
|
priority integer DEFAULT 0 NOT NULL,
|
|
max_connections integer DEFAULT 4 NOT NULL,
|
|
last_error text,
|
|
last_connected_at timestamp with time zone,
|
|
last_failure_at timestamp with time zone,
|
|
session_expired_at timestamp with time zone,
|
|
is_legacy boolean DEFAULT false NOT NULL,
|
|
deleted_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_user_accounts_health_state_check CHECK (((health_state)::text = ANY ((ARRAY['healthy'::character varying, 'degraded'::character varying, 'session_expired'::character varying])::text[]))),
|
|
CONSTRAINT telegram_user_accounts_max_connections_check CHECK ((max_connections > 0)),
|
|
CONSTRAINT telegram_user_accounts_weight_check CHECK ((weight > (0)::numeric))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_user_locales (
|
|
user_id bigint NOT NULL,
|
|
locale character varying(10) DEFAULT 'zh-CN'::character varying NOT NULL,
|
|
explicit boolean DEFAULT false NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_user_locales_locale_check CHECK (((locale)::text = ANY ((ARRAY['zh-CN'::character varying, 'en'::character varying, 'ru'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.telegram_write_reconciliations (
|
|
operation_id uuid NOT NULL,
|
|
job_id uuid NOT NULL,
|
|
item_id uuid NOT NULL,
|
|
child_lease_token uuid NOT NULL,
|
|
provider character varying(50) NOT NULL,
|
|
account_id uuid,
|
|
stored_path character varying(2000),
|
|
file_id uuid,
|
|
object_state character varying(20) NOT NULL,
|
|
index_state character varying(20) NOT NULL,
|
|
reason text NOT NULL,
|
|
status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
|
|
resolution character varying(30),
|
|
lease_token uuid,
|
|
lease_expires_at timestamp with time zone,
|
|
attempts integer DEFAULT 0 NOT NULL,
|
|
resolved_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT telegram_write_reconciliations_index_state_check CHECK (((index_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
|
|
CONSTRAINT telegram_write_reconciliations_object_state_check CHECK (((object_state)::text = ANY ((ARRAY['unknown'::character varying, 'present'::character varying, 'deleted'::character varying])::text[]))),
|
|
CONSTRAINT telegram_write_reconciliations_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'resolved'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.transfer_tasks (
|
|
source_type character varying(30) NOT NULL,
|
|
id character varying(128) NOT NULL,
|
|
kind character varying(50) NOT NULL,
|
|
title text NOT NULL,
|
|
status character varying(30) NOT NULL,
|
|
stage character varying(50) DEFAULT 'waiting'::character varying NOT NULL,
|
|
progress numeric(5,2) DEFAULT 0 NOT NULL,
|
|
owner_user_id bigint,
|
|
chat_id text,
|
|
source text,
|
|
target_provider character varying(50),
|
|
target_account_id uuid,
|
|
target_folder text,
|
|
total_items integer DEFAULT 0 NOT NULL,
|
|
completed_items integer DEFAULT 0 NOT NULL,
|
|
failed_items integer DEFAULT 0 NOT NULL,
|
|
total_bytes bigint DEFAULT 0 NOT NULL,
|
|
transferred_bytes bigint DEFAULT 0 NOT NULL,
|
|
payload jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
error text,
|
|
retryable boolean DEFAULT false NOT NULL,
|
|
cancel_requested boolean DEFAULT false NOT NULL,
|
|
started_at timestamp with time zone,
|
|
finished_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
execution_generation bigint DEFAULT 0 NOT NULL,
|
|
snapshot_version bigint DEFAULT 0 NOT NULL,
|
|
lease_token uuid,
|
|
lease_expires_at timestamp with time zone
|
|
);
|
|
|
|
CREATE TABLE public.update_notification_deliveries (
|
|
version character varying(64) NOT NULL,
|
|
channel character varying(32) NOT NULL,
|
|
recipient_id character varying(255) NOT NULL,
|
|
status character varying(32) DEFAULT 'pending'::character varying NOT NULL,
|
|
attempt_count integer DEFAULT 0 NOT NULL,
|
|
last_attempt_at timestamp with time zone,
|
|
delivered_at timestamp with time zone,
|
|
last_error text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT update_notification_deliveries_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'sending'::character varying, 'delivered'::character varying, 'failed'::character varying])::text[])))
|
|
);
|
|
|
|
CREATE TABLE public.web_sessions (
|
|
token_hash character varying(64) NOT NULL,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE ONLY public.api_keys
|
|
ADD CONSTRAINT api_keys_key_hash_key UNIQUE (key_hash);
|
|
|
|
ALTER TABLE ONLY public.api_keys
|
|
ADD CONSTRAINT api_keys_key_key UNIQUE (key);
|
|
|
|
ALTER TABLE ONLY public.api_keys
|
|
ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_chunks
|
|
ADD CONSTRAINT chunk_upload_chunks_pkey PRIMARY KEY (upload_id, chunk_index);
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_reconciliations
|
|
ADD CONSTRAINT chunk_upload_reconciliations_pkey PRIMARY KEY (operation_id);
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_sessions
|
|
ADD CONSTRAINT chunk_upload_sessions_pkey PRIMARY KEY (upload_id);
|
|
|
|
ALTER TABLE ONLY public.files
|
|
ADD CONSTRAINT files_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.oauth_pending_flows
|
|
ADD CONSTRAINT oauth_pending_flows_pkey PRIMARY KEY (state_hash);
|
|
|
|
ALTER TABLE ONLY public.schema_migrations
|
|
ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
|
|
|
|
ALTER TABLE ONLY public.storage_account_cooldowns
|
|
ADD CONSTRAINT storage_account_cooldowns_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.storage_account_cooldowns
|
|
ADD CONSTRAINT storage_account_cooldowns_storage_account_id_provider_reaso_key UNIQUE (storage_account_id, provider, reason);
|
|
|
|
ALTER TABLE ONLY public.storage_account_leases
|
|
ADD CONSTRAINT storage_account_leases_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.storage_accounts
|
|
ADD CONSTRAINT storage_accounts_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.system_settings
|
|
ADD CONSTRAINT system_settings_pkey PRIMARY KEY (key);
|
|
|
|
ALTER TABLE ONLY public.task_center_dismissals
|
|
ADD CONSTRAINT task_center_dismissals_pkey PRIMARY KEY (source_type, task_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_account_source_access
|
|
ADD CONSTRAINT telegram_account_source_access_pkey PRIMARY KEY (account_id, source_key, scope);
|
|
|
|
ALTER TABLE ONLY public.telegram_auth
|
|
ADD CONSTRAINT telegram_auth_pkey PRIMARY KEY (user_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_background_jobs
|
|
ADD CONSTRAINT telegram_background_jobs_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_channel_subscriptions
|
|
ADD CONSTRAINT telegram_channel_subscriptions_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_channel_subscriptions
|
|
ADD CONSTRAINT telegram_channel_subscriptions_user_id_source_key UNIQUE (user_id, source);
|
|
|
|
ALTER TABLE ONLY public.telegram_download_attempts
|
|
ADD CONSTRAINT telegram_download_attempts_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_download_items
|
|
ADD CONSTRAINT telegram_download_items_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_notification_digest
|
|
ADD CONSTRAINT telegram_notification_digest_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_notification_preferences
|
|
ADD CONSTRAINT telegram_notification_preferences_pkey PRIMARY KEY (user_id, chat_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_path_states
|
|
ADD CONSTRAINT telegram_path_states_pkey PRIMARY KEY (chat_id, mode);
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_decisions
|
|
ADD CONSTRAINT telegram_subscription_ad_deci_subscription_id_source_peer_m_key UNIQUE (subscription_id, source_peer, message_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_decisions
|
|
ADD CONSTRAINT telegram_subscription_ad_decisions_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_rules
|
|
ADD CONSTRAINT telegram_subscription_ad_rules_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_target_states
|
|
ADD CONSTRAINT telegram_target_states_pkey PRIMARY KEY (chat_id, mode);
|
|
|
|
ALTER TABLE ONLY public.telegram_task_receipts
|
|
ADD CONSTRAINT telegram_task_receipts_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_user_accounts
|
|
ADD CONSTRAINT telegram_user_accounts_pkey PRIMARY KEY (id);
|
|
|
|
ALTER TABLE ONLY public.telegram_user_accounts
|
|
ADD CONSTRAINT telegram_user_accounts_telegram_user_id_key UNIQUE (telegram_user_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_user_locales
|
|
ADD CONSTRAINT telegram_user_locales_pkey PRIMARY KEY (user_id);
|
|
|
|
ALTER TABLE ONLY public.telegram_write_reconciliations
|
|
ADD CONSTRAINT telegram_write_reconciliations_pkey PRIMARY KEY (operation_id);
|
|
|
|
ALTER TABLE ONLY public.transfer_tasks
|
|
ADD CONSTRAINT transfer_tasks_pkey PRIMARY KEY (source_type, id);
|
|
|
|
ALTER TABLE ONLY public.update_notification_deliveries
|
|
ADD CONSTRAINT update_notification_deliveries_pkey PRIMARY KEY (version, channel, recipient_id);
|
|
|
|
ALTER TABLE ONLY public.web_sessions
|
|
ADD CONSTRAINT web_sessions_pkey PRIMARY KEY (token_hash);
|
|
|
|
CREATE INDEX idx_chunk_upload_reconciliations_claim ON public.chunk_upload_reconciliations USING btree (status, lease_expires_at, created_at);
|
|
|
|
CREATE INDEX idx_chunk_upload_reconciliations_pending ON public.chunk_upload_reconciliations USING btree (status, created_at);
|
|
|
|
CREATE UNIQUE INDEX idx_chunk_upload_reconciliations_pending_completion ON public.chunk_upload_reconciliations USING btree (upload_id, completion_token) WHERE ((status)::text = 'pending'::text);
|
|
|
|
CREATE INDEX idx_chunk_upload_sessions_budget ON public.chunk_upload_sessions USING btree (status, expires_at);
|
|
|
|
CREATE INDEX idx_chunk_upload_sessions_completion_lease ON public.chunk_upload_sessions USING btree (status, completion_expires_at);
|
|
|
|
CREATE INDEX idx_chunk_upload_sessions_owner ON public.chunk_upload_sessions USING btree (owner_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_files_account_created ON public.files USING btree (storage_account_id, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_account_fav_created ON public.files USING btree (storage_account_id, is_favorite, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_account_folder_created ON public.files USING btree (storage_account_id, folder, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_created_at ON public.files USING btree (created_at DESC);
|
|
|
|
CREATE INDEX idx_files_derivative_pending ON public.files USING btree (derivative_status, created_at, id) WHERE ((derivative_status)::text = ANY ((ARRAY['queued'::character varying, 'processing'::character varying])::text[]));
|
|
|
|
CREATE INDEX idx_files_folder ON public.files USING btree (folder);
|
|
|
|
CREATE INDEX idx_files_folder_trgm ON public.files USING gin (folder public.gin_trgm_ops) WHERE (folder IS NOT NULL);
|
|
|
|
CREATE INDEX idx_files_is_favorite ON public.files USING btree (is_favorite);
|
|
|
|
CREATE INDEX idx_files_name_trgm ON public.files USING gin (name public.gin_trgm_ops);
|
|
|
|
CREATE INDEX idx_files_source_created ON public.files USING btree (source, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_source_fav_created ON public.files USING btree (source, is_favorite, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_source_folder_created ON public.files USING btree (source, folder, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX idx_files_storage_account_id ON public.files USING btree (storage_account_id);
|
|
|
|
CREATE INDEX idx_files_type ON public.files USING btree (type);
|
|
|
|
CREATE INDEX idx_oauth_pending_flows_expiry ON public.oauth_pending_flows USING btree (expires_at);
|
|
|
|
CREATE UNIQUE INDEX idx_oauth_pending_flows_session ON public.oauth_pending_flows USING btree (auth_session_hash);
|
|
|
|
CREATE INDEX idx_storage_account_cooldowns_until ON public.storage_account_cooldowns USING btree (cooldown_until);
|
|
|
|
CREATE INDEX idx_storage_account_leases_active ON public.storage_account_leases USING btree (storage_account_id, expires_at) WHERE (released_at IS NULL);
|
|
|
|
CREATE INDEX idx_task_center_dismissals_version ON public.task_center_dismissals USING btree (source_type, task_id, task_updated_at);
|
|
|
|
CREATE INDEX idx_telegram_account_source_access_lookup ON public.telegram_account_source_access USING btree (source_key, scope, access_state, checked_at DESC);
|
|
|
|
CREATE INDEX idx_telegram_download_attempts_account_started ON public.telegram_download_attempts USING btree (account_id, started_at DESC);
|
|
|
|
CREATE INDEX idx_telegram_download_attempts_job_item ON public.telegram_download_attempts USING btree (job_id, item_id, started_at DESC);
|
|
|
|
CREATE INDEX idx_telegram_download_attempts_running ON public.telegram_download_attempts USING btree (account_id, source_key) WHERE ((status)::text = 'running'::text);
|
|
|
|
CREATE INDEX idx_telegram_notification_digest_pending ON public.telegram_notification_digest USING btree (user_id, chat_id, created_at) WHERE (delivered_at IS NULL);
|
|
|
|
CREATE INDEX idx_telegram_path_states_expiry ON public.telegram_path_states USING btree (expires_at);
|
|
|
|
CREATE INDEX idx_telegram_target_states_account ON public.telegram_target_states USING btree (account_id) WHERE (account_id IS NOT NULL);
|
|
|
|
CREATE INDEX idx_telegram_target_states_expiry ON public.telegram_target_states USING btree (expires_at);
|
|
|
|
CREATE INDEX idx_telegram_user_accounts_schedulable ON public.telegram_user_accounts USING btree (enabled, health_state, cooldown_until, priority DESC);
|
|
|
|
CREATE INDEX idx_telegram_user_accounts_visible ON public.telegram_user_accounts USING btree (created_at, id) WHERE (deleted_at IS NULL);
|
|
|
|
CREATE UNIQUE INDEX idx_tg_background_jobs_ad_decision ON public.telegram_background_jobs USING btree (ad_decision_id) WHERE (ad_decision_id IS NOT NULL);
|
|
|
|
CREATE INDEX idx_tg_background_jobs_assigned_account ON public.telegram_background_jobs USING btree (assigned_account_id, status);
|
|
|
|
CREATE INDEX idx_tg_background_jobs_cooldown ON public.telegram_background_jobs USING btree (cooldown_until);
|
|
|
|
CREATE INDEX idx_tg_background_jobs_pipeline ON public.telegram_background_jobs USING btree (status, scan_status, download_status);
|
|
|
|
CREATE INDEX idx_tg_background_jobs_status ON public.telegram_background_jobs USING btree (status);
|
|
|
|
CREATE INDEX idx_tg_background_jobs_user_created ON public.telegram_background_jobs USING btree (user_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_tg_channel_subscriptions_enabled ON public.telegram_channel_subscriptions USING btree (enabled);
|
|
|
|
CREATE INDEX idx_tg_channel_subscriptions_target_account ON public.telegram_channel_subscriptions USING btree (target_account_id) WHERE (target_account_id IS NOT NULL);
|
|
|
|
CREATE INDEX idx_tg_channel_subscriptions_user_id ON public.telegram_channel_subscriptions USING btree (user_id);
|
|
|
|
CREATE UNIQUE INDEX idx_tg_download_items_job_peer_msg ON public.telegram_download_items USING btree (job_id, source_peer, message_id);
|
|
|
|
CREATE INDEX idx_tg_download_items_job_status ON public.telegram_download_items USING btree (job_id, status);
|
|
|
|
CREATE INDEX idx_tg_download_items_lease_expiry ON public.telegram_download_items USING btree (status, lease_expires_at);
|
|
|
|
CREATE INDEX idx_tg_download_items_recover ON public.telegram_download_items USING btree (status, locked_at);
|
|
|
|
CREATE INDEX idx_tg_subscription_ad_decisions_blocked ON public.telegram_subscription_ad_decisions USING btree (subscription_id, decision, created_at DESC);
|
|
|
|
CREATE INDEX idx_tg_subscription_ad_decisions_subscription ON public.telegram_subscription_ad_decisions USING btree (subscription_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_tg_subscription_ad_decisions_template ON public.telegram_subscription_ad_decisions USING btree (subscription_id, manual_label, created_at DESC) WHERE (manual_label IS NOT NULL);
|
|
|
|
CREATE INDEX idx_tg_subscription_ad_rules_subscription ON public.telegram_subscription_ad_rules USING btree (subscription_id, enabled, created_at DESC);
|
|
|
|
CREATE UNIQUE INDEX idx_tg_subscription_ad_rules_unique ON public.telegram_subscription_ad_rules USING btree (subscription_id, kind, action, pattern);
|
|
|
|
CREATE INDEX idx_tg_write_reconciliations_claim ON public.telegram_write_reconciliations USING btree (status, lease_expires_at, created_at);
|
|
|
|
CREATE UNIQUE INDEX idx_tg_write_reconciliations_pending_item ON public.telegram_write_reconciliations USING btree (item_id) WHERE ((status)::text = 'pending'::text);
|
|
|
|
CREATE INDEX idx_transfer_tasks_claim ON public.transfer_tasks USING btree (source_type, status, lease_expires_at, updated_at);
|
|
|
|
CREATE INDEX idx_transfer_tasks_owner ON public.transfer_tasks USING btree (owner_user_id, chat_id, updated_at DESC);
|
|
|
|
CREATE INDEX idx_transfer_tasks_status ON public.transfer_tasks USING btree (status, updated_at DESC);
|
|
|
|
CREATE INDEX idx_transfer_tasks_updated ON public.transfer_tasks USING btree (updated_at DESC);
|
|
|
|
CREATE INDEX idx_update_notification_pending ON public.update_notification_deliveries USING btree (status, last_attempt_at) WHERE ((status)::text <> 'delivered'::text);
|
|
|
|
CREATE INDEX idx_web_sessions_expires ON public.web_sessions USING btree (expires_at);
|
|
|
|
CREATE TRIGGER chunk_upload_sessions_updated_at BEFORE UPDATE ON public.chunk_upload_sessions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER files_updated_at BEFORE UPDATE ON public.files FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER storage_account_cooldowns_updated_at BEFORE UPDATE ON public.storage_account_cooldowns FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER storage_accounts_updated_at BEFORE UPDATE ON public.storage_accounts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER system_settings_updated_at BEFORE UPDATE ON public.system_settings FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_account_source_access_updated_at BEFORE UPDATE ON public.telegram_account_source_access FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_background_jobs_updated_at BEFORE UPDATE ON public.telegram_background_jobs FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_channel_subscriptions_updated_at BEFORE UPDATE ON public.telegram_channel_subscriptions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_download_items_updated_at BEFORE UPDATE ON public.telegram_download_items FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_notification_preferences_updated_at BEFORE UPDATE ON public.telegram_notification_preferences FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_path_states_updated_at BEFORE UPDATE ON public.telegram_path_states FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_subscription_ad_decisions_updated_at BEFORE UPDATE ON public.telegram_subscription_ad_decisions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_subscription_ad_rules_updated_at BEFORE UPDATE ON public.telegram_subscription_ad_rules FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_target_states_updated_at BEFORE UPDATE ON public.telegram_target_states FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_user_accounts_updated_at BEFORE UPDATE ON public.telegram_user_accounts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER telegram_user_locales_updated_at BEFORE UPDATE ON public.telegram_user_locales FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER transfer_tasks_updated_at BEFORE UPDATE ON public.transfer_tasks FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
CREATE TRIGGER update_notification_deliveries_updated_at BEFORE UPDATE ON public.update_notification_deliveries FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_chunks
|
|
ADD CONSTRAINT chunk_upload_chunks_upload_id_fkey FOREIGN KEY (upload_id) REFERENCES public.chunk_upload_sessions(upload_id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_reconciliations
|
|
ADD CONSTRAINT chunk_upload_reconciliations_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_reconciliations
|
|
ADD CONSTRAINT chunk_upload_reconciliations_upload_id_fkey FOREIGN KEY (upload_id) REFERENCES public.chunk_upload_sessions(upload_id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_sessions
|
|
ADD CONSTRAINT chunk_upload_sessions_completed_file_id_fkey FOREIGN KEY (completed_file_id) REFERENCES public.files(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.chunk_upload_sessions
|
|
ADD CONSTRAINT chunk_upload_sessions_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.files
|
|
ADD CONSTRAINT files_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.storage_account_cooldowns
|
|
ADD CONSTRAINT storage_account_cooldowns_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.storage_account_leases
|
|
ADD CONSTRAINT storage_account_leases_storage_account_id_fkey FOREIGN KEY (storage_account_id) REFERENCES public.storage_accounts(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_account_source_access
|
|
ADD CONSTRAINT telegram_account_source_access_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_background_jobs
|
|
ADD CONSTRAINT telegram_background_jobs_ad_decision_id_fkey FOREIGN KEY (ad_decision_id) REFERENCES public.telegram_subscription_ad_decisions(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_background_jobs
|
|
ADD CONSTRAINT telegram_background_jobs_assigned_account_id_fkey FOREIGN KEY (assigned_account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_channel_subscriptions
|
|
ADD CONSTRAINT telegram_channel_subscriptions_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE ONLY public.telegram_download_attempts
|
|
ADD CONSTRAINT telegram_download_attempts_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.telegram_user_accounts(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE ONLY public.telegram_download_attempts
|
|
ADD CONSTRAINT telegram_download_attempts_item_id_fkey FOREIGN KEY (item_id) REFERENCES public.telegram_download_items(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_download_attempts
|
|
ADD CONSTRAINT telegram_download_attempts_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_download_items
|
|
ADD CONSTRAINT telegram_download_items_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_decisions
|
|
ADD CONSTRAINT telegram_subscription_ad_decisions_restored_job_id_fkey FOREIGN KEY (restored_job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_decisions
|
|
ADD CONSTRAINT telegram_subscription_ad_decisions_subscription_id_fkey FOREIGN KEY (subscription_id) REFERENCES public.telegram_channel_subscriptions(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_subscription_ad_rules
|
|
ADD CONSTRAINT telegram_subscription_ad_rules_subscription_id_fkey FOREIGN KEY (subscription_id) REFERENCES public.telegram_channel_subscriptions(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_target_states
|
|
ADD CONSTRAINT telegram_target_states_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE ONLY public.telegram_write_reconciliations
|
|
ADD CONSTRAINT telegram_write_reconciliations_account_id_fkey FOREIGN KEY (account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE ONLY public.telegram_write_reconciliations
|
|
ADD CONSTRAINT telegram_write_reconciliations_item_id_fkey FOREIGN KEY (item_id) REFERENCES public.telegram_download_items(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.telegram_write_reconciliations
|
|
ADD CONSTRAINT telegram_write_reconciliations_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.telegram_background_jobs(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE ONLY public.transfer_tasks
|
|
ADD CONSTRAINT transfer_tasks_target_account_id_fkey FOREIGN KEY (target_account_id) REFERENCES public.storage_accounts(id) ON DELETE SET NULL;
|