Something went wrong. Try again.
A community based topic aggregation platform built on atproto
Something went wrong. Try again.
1.8 kB · 58 lines
SQL
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859-- +goose Up-- +goose StatementBeginCREATE TABLE identity_cache ( -- Can lookup by either handle or DID identifier TEXT PRIMARY KEY,
-- Cached resolution data did TEXT NOT NULL, handle TEXT, pds_url TEXT,
-- Resolution metadata resolved_at TIMESTAMP WITH TIME ZONE NOT NULL, resolution_method TEXT NOT NULL, -- 'dns', 'https', 'cache'
-- Cache management expires_at TIMESTAMP WITH TIME ZONE NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);
-- Index for reverse lookup (DID → handle)CREATE INDEX idx_identity_cache_did ON identity_cache(did);
-- Index for expiry cleanupCREATE INDEX idx_identity_cache_expires ON identity_cache(expires_at);
-- Function to normalize handles to lowercaseCREATE OR REPLACE FUNCTION normalize_handle() RETURNS TRIGGER AS $$BEGIN IF NEW.handle IS NOT NULL THEN NEW.handle = LOWER(TRIM(NEW.handle)); END IF; IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN -- Normalize identifier if it looks like a handle (contains a dot) IF NEW.identifier LIKE '%.%' THEN NEW.identifier = LOWER(TRIM(NEW.identifier)); END IF; END IF; NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW;END;$$ LANGUAGE plpgsql;
-- Trigger to normalize handles automaticallyCREATE TRIGGER normalize_handle_trigger BEFORE INSERT OR UPDATE ON identity_cache FOR EACH ROW EXECUTE FUNCTION normalize_handle();
-- +goose StatementEnd
-- +goose Down-- +goose StatementBeginDROP TRIGGER IF EXISTS normalize_handle_trigger ON identity_cache;DROP FUNCTION IF EXISTS normalize_handle();DROP TABLE IF EXISTS identity_cache;-- +goose StatementEnd