forked from PaulSonOfLars/tgbot
-
Notifications
You must be signed in to change notification settings - Fork 7
Expand file tree
/
Copy pathschema.sql
More file actions
102 lines (87 loc) · 2.62 KB
/
Copy pathschema.sql
File metadata and controls
102 lines (87 loc) · 2.62 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- PostgreSQL schema for Python Italy Telegram Bot
-- Run against your DATABASE_URL before using PostgresRepository
CREATE TABLE IF NOT EXISTS pending_verifications (
user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE IF NOT EXISTS verified_users (
user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE IF NOT EXISTS bans (
user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
admin_id BIGINT NOT NULL,
reason TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE IF NOT EXISTS mutes (
user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
admin_id BIGINT NOT NULL,
reason TEXT,
until_ts TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE IF NOT EXISTS reports (
id SERIAL PRIMARY KEY,
reporter_id BIGINT NOT NULL,
reported_user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
message_id BIGINT,
reason TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS group_settings (
chat_id BIGINT PRIMARY KEY,
welcome_message TEXT,
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS globally_verified_users (
user_id BIGINT PRIMARY KEY,
verified_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS bot_chats (
chat_id BIGINT PRIMARY KEY,
title TEXT,
added_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS global_bans (
user_id BIGINT PRIMARY KEY,
admin_id BIGINT NOT NULL,
reason TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS known_users (
user_id BIGINT PRIMARY KEY,
username TEXT,
first_name TEXT,
last_name TEXT,
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_known_users_username
ON known_users (LOWER(username))
WHERE username IS NOT NULL;
CREATE TABLE IF NOT EXISTS welcomed_users (
user_id BIGINT NOT NULL,
chat_id BIGINT NOT NULL,
welcomed_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (user_id, chat_id)
);
-- Add welcome_delay_minutes column to group_settings (idempotent).
ALTER TABLE group_settings
ADD COLUMN IF NOT EXISTS welcome_delay_minutes INTEGER;
-- Add title column to bot_chats for group name lookup (idempotent).
ALTER TABLE bot_chats
ADD COLUMN IF NOT EXISTS title TEXT;
CREATE TABLE IF NOT EXISTS welcome_messages (
chat_id BIGINT NOT NULL,
message_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (chat_id, message_id)
);