4 event
TEXT NOT NULL PRIMARY KEY CHECK (typeof(event
) = 'text'),
5 epoch
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(epoch
) = 'integer')
8 CREATE TABLE authentication (
9 identifier_id
INTEGER NOT NULL PRIMARY KEY,
10 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created
) = 'integer'),
11 last_authentication
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(last_authentication
) = 'integer'),
12 identifier
TEXT NOT NULL UNIQUE CHECK (typeof(identifier
) = 'text'),
13 credential
TEXT CHECK (typeof(credential
) IN ('text', 'null'))
16 CREATE TABLE resource (
17 resource_id
TEXT NOT NULL PRIMARY KEY CHECK (typeof(resource_id
) = 'text'),
18 description
TEXT NOT NULL DEFAULT '' CHECK (typeof(description
) = 'text'),
19 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created
) = 'integer'),
20 secret
TEXT NOT NULL CHECK (typeof(secret
) = 'text')
23 CREATE TABLE profile (
24 profile_id
INTEGER NOT NULL PRIMARY KEY,
25 identifier_id
INTEGER NOT NULL REFERENCES authentication(identifier_id
) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
26 profile
TEXT NOT NULL CHECK (typeof(profile
) = 'text'),
27 UNIQUE (identifier_id
, profile
) ON CONFLICT
IGNORE
29 CREATE INDEX profile_identifier_id_idx
ON profile(identifier_id
);
30 CREATE INDEX profile_profile_idx
ON profile(profile
);
33 scope_id
INTEGER NOT NULL PRIMARY KEY,
34 scope TEXT NOT NULL UNIQUE CHECK (typeof(scope) = 'text'),
35 description
TEXT NOT NULL ON CONFLICT
REPLACE DEFAULT '' CHECK (typeof(description
) = 'text'),
36 application
TEXT NOT NULL ON CONFLICT
REPLACE DEFAULT '' CHECK (typeof(application
) = 'text'),
37 is_permanent
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_permanent
) = 'integer' AND is_permanent
IN (0, 1)),
38 is_manually_added
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_manually_added
) = 'integer' AND is_manually_added
IN (0, 1))
41 CREATE TABLE profile_scope (
42 profile_id
INTEGER NOT NULL REFERENCES profile(profile_id
) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
43 scope_id
INTEGER NOT NULL REFERENCES scope(scope_id
) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
44 PRIMARY KEY (profile_id
, scope_id
)
46 CREATE INDEX profile_scope_scope_id_idx
ON profile_scope(scope_id
);
49 code_id
TEXT NOT NULL PRIMARY KEY CHECK (typeof(code_id
) = 'text'),
50 profile_id
INTEGER NOT NULL REFERENCES profile (profile_id
) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created
) = 'integer'),
52 expires
INTEGER CHECK (typeof(expires
) IN ('integer', 'null')),
53 refresh_expires
INTEGER CHECK (typeof(refresh_expires
) IN ('integer', 'null')),
54 refreshed
INTEGER CHECK (typeof(refreshed
) IN ('integer', 'null')),
55 duration
INTEGER CHECK (typeof(duration
) IN ('integer', 'null')),
56 refresh_duration
INTEGER CHECK (typeof(refresh_duration
) IN ('integer', 'null')),
57 refresh_count
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(refresh_count
) = 'integer'),
58 is_revoked
INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_revoked
) = 'integer' AND is_revoked
IN (0, 1)),
59 is_token
INTEGER NOT NULL CHECK (typeof(is_token
) = 'integer' AND is_token
IN (0, 1)),
60 client_id
TEXT NOT NULL CHECK (typeof(client_id
) = 'text'),
61 resource
TEXT CHECK (typeof(resource
) IN ('integer', 'null')),
63 CONSTRAINT expires_needs_duration
CHECK (expires
IS NULL OR duration
IS NOT NULL),
64 CONSTRAINT refresh_expires_needs_refresh_duration
CHECK (refresh_expires
IS NULL OR refresh_duration
IS NOT NULL)
66 CREATE INDEX token_profile_id_idx
ON token(profile_id
);
67 CREATE INDEX token_created_idx
ON token(created
);
68 CREATE INDEX token_expires_idx
ON token(expires
) WHERE expires
IS NOT NULL;
69 CREATE INDEX token_refresh_expires_idx
ON token(refresh_expires
) WHERE refresh_expires
IS NOT NULL;
71 CREATE TABLE token_scope (
72 code_id
TEXT NOT NULL REFERENCES token(code_id
) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
73 scope_id
INTEGER NOT NULL REFERENCES scope(scope_id
) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
74 PRIMARY KEY (code_id
, scope_id
)
76 CREATE INDEX token_scope_scope_id_idx
ON token_scope(scope_id
);
78 -- Update schema version
79 INSERT INTO _meta_schema_version (major
, minor
, patch
) VALUES (1, 0, 0);
82 INSERT INTO scope (scope, description
, application
, is_permanent
) VALUES
83 ('profile', 'Access detailed profile information, including name, image, and url.', 'IndieAuth', 1),
84 ('email', 'Include email address with detailed profile information.', 'IndieAuth', 1),
85 ('create', 'Create new items.', 'MicroPub', 1),
86 ('draft', 'All created items are drafts.', 'MicroPub', 1),
87 ('update', 'Edit existing items.', 'MicroPub', 1),
88 ('delete', 'Remove items.', 'MicroPub', 1),
89 ('media', 'Allow file uploads.', 'MicroPub', 1),
90 ('read', 'Read access to channels.', 'MicroSub', 1),
91 ('follow', 'Management of following list.', 'MicroSub', 1),
92 ('mute', 'Management of user muting.', 'MicroSub', 1),
93 ('block', 'Management of user blocking.', 'MicroSub', 1),
94 ('channels', 'Management of channels.', 'MicroSub', 1)