A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- +goose StatementBegin
3CREATE TABLE identity_cache (
4 -- Can lookup by either handle or DID
5 identifier TEXT PRIMARY KEY,
6
7 -- Cached resolution data
8 did TEXT NOT NULL,
9 handle TEXT,
10 pds_url TEXT,
11
12 -- Resolution metadata
13 resolved_at TIMESTAMP WITH TIME ZONE NOT NULL,
14 resolution_method TEXT NOT NULL, -- 'dns', 'https', 'cache'
15
16 -- Cache management
17 expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
18 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
19 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
20);
21
22-- Index for reverse lookup (DID → handle)
23CREATE INDEX idx_identity_cache_did ON identity_cache(did);
24
25-- Index for expiry cleanup
26CREATE INDEX idx_identity_cache_expires ON identity_cache(expires_at);
27
28-- Function to normalize handles to lowercase
29CREATE OR REPLACE FUNCTION normalize_handle() RETURNS TRIGGER AS $$
30BEGIN
31 IF NEW.handle IS NOT NULL THEN
32 NEW.handle = LOWER(TRIM(NEW.handle));
33 END IF;
34 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
35 -- Normalize identifier if it looks like a handle (contains a dot)
36 IF NEW.identifier LIKE '%.%' THEN
37 NEW.identifier = LOWER(TRIM(NEW.identifier));
38 END IF;
39 END IF;
40 NEW.updated_at = CURRENT_TIMESTAMP;
41 RETURN NEW;
42END;
43$$ LANGUAGE plpgsql;
44
45-- Trigger to normalize handles automatically
46CREATE TRIGGER normalize_handle_trigger
47 BEFORE INSERT OR UPDATE ON identity_cache
48 FOR EACH ROW
49 EXECUTE FUNCTION normalize_handle();
50
51-- +goose StatementEnd
52
53-- +goose Down
54-- +goose StatementBegin
55DROP TRIGGER IF EXISTS normalize_handle_trigger ON identity_cache;
56DROP FUNCTION IF EXISTS normalize_handle();
57DROP TABLE IF EXISTS identity_cache;
58-- +goose StatementEnd