Class: Heathrow::Migrations::InitialSchema

Inherits:
Object
  • Object
show all
Defined in:
lib/heathrow/migrations/001_initial_schema.rb

Constant Summary collapse

VERSION = 
1

Class Method Summary collapse

Class Method Details

.down(db) ⇒ Object 



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/heathrow/migrations/001_initial_schema.rb', line 223

def self.down(db)
  # Reverse migration (drop all tables)
  db.exec "DROP TABLE IF EXISTS settings"
  db.exec "DROP TABLE IF EXISTS filters"
  db.exec "DROP TABLE IF EXISTS drafts"
  db.exec "DROP TABLE IF EXISTS contacts"
  db.exec "DROP TABLE IF EXISTS views"
  db.exec "DROP TABLE IF EXISTS sources"

  # Drop FTS triggers
  db.exec "DROP TRIGGER IF EXISTS messages_au"
  db.exec "DROP TRIGGER IF EXISTS messages_ad"
  db.exec "DROP TRIGGER IF EXISTS messages_ai"

  # Drop FTS table
  db.exec "DROP TABLE IF EXISTS messages_fts"

  # Drop messages table
  db.exec "DROP TABLE IF EXISTS messages"

  # Remove migration record
  db.exec "DELETE FROM schema_version WHERE version = ?", [VERSION]
end

.up(db) ⇒ Object 



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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/heathrow/migrations/001_initial_schema.rb', line 9

def self.up(db)
  # Schema version tracking
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS schema_version (
        version INTEGER PRIMARY KEY,
        applied_at INTEGER NOT NULL
    );
  SQL

  # Messages table - normalized structure
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        source_id INTEGER NOT NULL,
        external_id TEXT NOT NULL,
        thread_id TEXT,
        parent_id INTEGER,

        sender TEXT NOT NULL,
        sender_name TEXT,

        recipients TEXT NOT NULL,
        cc TEXT,
        bcc TEXT,

        subject TEXT,
        content TEXT NOT NULL,
        html_content TEXT,

        timestamp INTEGER NOT NULL,
        received_at INTEGER NOT NULL,
        read BOOLEAN DEFAULT 0,
        starred BOOLEAN DEFAULT 0,
        archived BOOLEAN DEFAULT 0,

        labels TEXT,
        attachments TEXT,
        metadata TEXT,

        UNIQUE(source_id, external_id),
        FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE CASCADE,
        FOREIGN KEY(parent_id) REFERENCES messages(id) ON DELETE SET NULL
    );
  SQL

  # Indices for performance
  db.exec "CREATE INDEX IF NOT EXISTS idx_messages_source ON messages(source_id)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp DESC)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages(thread_id)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_messages_read ON messages(read)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_messages_sender ON messages(sender)"

  # Full-text search
  db.exec <<-SQL
    CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
        subject,
        content,
        sender,
        content='messages',
        content_rowid='id'
    );
  SQL

  # FTS triggers
  db.exec <<-SQL
    CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
        INSERT INTO messages_fts(rowid, subject, content, sender)
        VALUES (new.id, new.subject, new.content, new.sender);
    END;
  SQL

  db.exec <<-SQL
    CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
        DELETE FROM messages_fts WHERE rowid = old.id;
    END;
  SQL

  db.exec <<-SQL
    CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
        UPDATE messages_fts
        SET subject = new.subject, content = new.content, sender = new.sender
        WHERE rowid = new.id;
    END;
  SQL

  # Sources table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS sources (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        plugin_type TEXT NOT NULL,
        enabled BOOLEAN DEFAULT 1,

        config TEXT NOT NULL,
        capabilities TEXT NOT NULL,

        last_sync INTEGER,
        last_error TEXT,

        message_count INTEGER DEFAULT 0,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );
  SQL

  db.exec "CREATE INDEX IF NOT EXISTS idx_sources_enabled ON sources(enabled)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_sources_plugin_type ON sources(plugin_type)"

  # Views table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS views (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        key_binding TEXT UNIQUE,

        filters TEXT NOT NULL,

        sort_order TEXT DEFAULT 'timestamp DESC',
        is_remainder BOOLEAN DEFAULT 0,

        show_count BOOLEAN DEFAULT 1,
        color INTEGER,
        icon TEXT,

        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );
  SQL

  db.exec "CREATE INDEX IF NOT EXISTS idx_views_key_binding ON views(key_binding)"

  # Contacts table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        primary_email TEXT,

        identities TEXT,

        phone TEXT,
        avatar_url TEXT,

        tags TEXT,
        notes TEXT,

        message_count INTEGER DEFAULT 0,
        last_contact INTEGER,

        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );
  SQL

  db.exec "CREATE INDEX IF NOT EXISTS idx_contacts_email ON contacts(primary_email)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name)"

  # Drafts table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS drafts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        source_id INTEGER,
        reply_to_id INTEGER,

        recipients TEXT NOT NULL,
        cc TEXT,
        bcc TEXT,
        subject TEXT,
        content TEXT NOT NULL,
        attachments TEXT,

        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,

        FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE SET NULL,
        FOREIGN KEY(reply_to_id) REFERENCES messages(id) ON DELETE SET NULL
    );
  SQL

  db.exec "CREATE INDEX IF NOT EXISTS idx_drafts_updated ON drafts(updated_at DESC)"

  # Filters table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS filters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        enabled BOOLEAN DEFAULT 1,
        priority INTEGER DEFAULT 0,

        conditions TEXT NOT NULL,
        actions TEXT NOT NULL,

        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );
  SQL

  db.exec "CREATE INDEX IF NOT EXISTS idx_filters_enabled ON filters(enabled)"
  db.exec "CREATE INDEX IF NOT EXISTS idx_filters_priority ON filters(priority DESC)"

  # Settings table
  db.exec <<-SQL
    CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at INTEGER NOT NULL
    );
  SQL

  # Record migration
  db.exec "INSERT INTO schema_version (version, applied_at) VALUES (?, ?)",
          [VERSION, Time.now.to_i]
end