agentmail/agentmail_schema.sql at main · ethanpost/agentmail · GitHub

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
/*
  Agentmail schema: tables and types.
  Run after agentmail_utils.sql.
*/
set define off;

-- 100% aware of the security issues here. Use at your own risk or re-design. This is
-- the easiest way to build something that work both in and out of the cloud.
exec drop_table('agentmail_api_token');
begin
    if not does_table_exist('agentmail_api_token') then
        execute immediate q'<
        create table agentmail_api_token (
            token_name VARCHAR2(100),
            token_value varchar2(512) NOT NULL
        )>';
    end if;
    add_primary_key('agentmail_api_token', 'token_name');
end;
/

/*
Main messages table. Stores message metadata from the agentmail API.
message_id is the external API id (primary key). raw_json keeps the original payload for debugging.
*/

exec drop_table('agentmail_messages');
begin
    if not does_table_exist('agentmail_messages') then
        execute immediate q'<create table agentmail_messages (
            inbox_id      varchar2(100) default null,
            thread_id     varchar2(100) default null,
            message_id    varchar2(100) not null,
            labels        varchar2(4000) default null,
            timestamp_    timestamp with time zone default null,
            from_addr     varchar2(500) default null,
            subject       varchar2(1000) default null,
            preview       varchar2(4000) default null,
            size_bytes    number default null,
            created_at    timestamp with time zone default null,
            updated_at    timestamp with time zone default null,
            in_reply_to   varchar2(500) default null,
            raw_json      clob default null
        )>';
        add_primary_key('agentmail_messages', 'message_id');
    end if;
end;
/

/*
Child table for recipients (to/cc/bcc), normalized. One row per recipient per message.
*/

exec drop_table('agentmail_recipients');
begin
    if not does_table_exist('agentmail_recipients') then
        execute immediate q'<create table agentmail_recipients (
            message_id     varchar2(100) not null references agentmail_messages(message_id) on delete cascade,
            recipient_type varchar2(10) not null,
            email_addr     varchar2(500) not null,
            primary key (message_id, recipient_type, email_addr)
        )>';
    end if;
end;
/

/*
Attachments table. One row per attachment per message.
*/

exec drop_table('agentmail_attachments');
begin
    if not does_table_exist('agentmail_attachments') then
        execute immediate q'<create table agentmail_attachments (
            message_id            varchar2(100) not null references agentmail_messages(message_id) on delete cascade,
            attachment_id         varchar2(100) not null,
            filename              varchar2(500) default null,
            size_bytes            number default null,
            content_type          varchar2(200) default null,
            content_disposition   varchar2(100) default null,
            content_id            varchar2(200) default null,
            primary key (message_id, attachment_id)
        )>';
    end if;
end;
/

/*
Message detail table. Stores the full payload from GET Message (single message),
including bodies, headers, and reference arrays/objects.
*/

exec drop_table('agentmail_message_detail');
begin
    if not does_table_exist('agentmail_message_detail') then
        execute immediate q'<create table agentmail_message_detail (
            inbox_id        varchar2(100) default null,
            thread_id       varchar2(100) default null,
            message_id      varchar2(100) not null,
            labels_json     clob default null,
            timestamp_      timestamp with time zone default null,
            from_addr       varchar2(500) default null,
            reply_to_json   clob default null,
            to_json         clob default null,
            cc_json         clob default null,
            bcc_json        clob default null,
            subject         varchar2(1000) default null,
            preview         varchar2(4000) default null,
            text_body       clob default null,
            html_body       clob default null,
            extracted_text  clob default null,
            extracted_html  clob default null,
            attachments_json clob default null,
            in_reply_to     varchar2(500) default null,
            references_json clob default null,
            headers_json    clob default null,
            size_bytes      number default null,
            created_at      timestamp with time zone default null,
            updated_at      timestamp with time zone default null,
            fetched_at      timestamp with time zone default null,
            raw_json        clob default null
        )>';
        add_primary_key('agentmail_message_detail', 'message_id');
    end if;
end;
/

/*
Attachment content table. Stores the result of Get Attachment API: metadata plus
the binary content fetched from download_url. One row per attachment.
*/
exec drop_table('agentmail_attachment_content');
begin
    if not does_table_exist('agentmail_attachment_content') then
        execute immediate q'<create table agentmail_attachment_content (
            message_id            varchar2(100) not null references agentmail_messages(message_id) on delete cascade,
            attachment_id         varchar2(100) not null,
            filename              varchar2(500) default null,
            size_bytes            number default null,
            content_type          varchar2(200) default null,
            content_disposition   varchar2(100) default null,
            content_id            varchar2(200) default null,
            download_url          varchar2(2000) default null,
            expires_at            timestamp with time zone default null,
            content               blob default null,
            fetched_at            timestamp with time zone default null,
            raw_json              clob default null,
            primary key (message_id, attachment_id)
        )>';
    end if;
end;
/

/*
Inboxes table. Stores inbox metadata from the List Inboxes API.
See https://docs.agentmail.to/api-reference/inboxes/list
*/

exec drop_table('agentmail_inboxes');
begin
    if not does_table_exist('agentmail_inboxes') then
        execute immediate q'<create table agentmail_inboxes (
            pod_id        varchar2(100) default null,
            inbox_id      varchar2(100) not null,
            display_name  varchar2(500) default null,
            client_id     varchar2(200) default null,
            updated_at    timestamp with time zone default null,
            created_at    timestamp with time zone default null,
            raw_json      clob default null
        )>';
        add_primary_key('agentmail_inboxes', 'inbox_id');
    end if;
end;
/

CREATE OR REPLACE TYPE label_array AS TABLE OF VARCHAR2(4000);
/