# SCHEMA

This file represents the initial schema for the various data structures to be stored in the database.

These schema definitions target SQLite v3.

# Persons Table
The Persons table will contain data about Person entities (see MODELS.md)

``` sql
CREATE TABLE Persons (
	id TEXT(36),    /* UUIDv4 */
	remote_id TEXT, /* field to store an arbitrary remote identifier for this Person if they are not local */
	name TEXT,      /* "real" name */
	handle TEXT,    /* also commonly refered to as 'display_name' or 'screen_name', optional (if missing, name will be used as handle) */
	avatar TEXT,	/* optional URL to Person's avatar image / profile pic */
	cover TEXT,		/* optional URL to Person's cover image / header image */
	bio TEXT,		/* optional biographical text provided by Person */
	is_active INTEGER DEFAULT (1),    /* bool, default true */
	is_blocked INTEGER DEFAULT (0),   /* bool, default false */
	created_at INTEGER,               /* timestamp */
	modified_at INTEGER,              /* timestamp */
	modified_by TEXT(36),             /* UUIDv4 */
	deleted_at INTEGER,               /* timestamp */
	deleted_by TEXT(36),              /* UUIDv4 */
	last_seen INTEGER,                /* timestamp */
	shipping_address TEXT,            /* optional, should use \n between lines to keep localized format as needed */
	CONSTRAINT Persons_PK PRIMARY KEY (id),
	CONSTRAINT Persons_FK FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Persons_FK_1 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE UNIQUE INDEX Persons_remote_id_IDX ON Persons (remote_id);
CREATE INDEX Persons_name_IDX ON Persons (name);
CREATE INDEX Persons_handle_IDX ON Persons (handle);
CREATE INDEX Persons_is_active_IDX ON Persons (is_active);
CREATE INDEX Persons_is_blocked_IDX ON Persons (is_blocked);
CREATE INDEX Persons_deleted_at_IDX ON Persons (deleted_at);
```

# PersonEmails Table
The PersonEmails table is a one-to-many lookup table relating a Person to zero or more email addresses

``` sql
CREATE TABLE PersonEmails (
	person_id TEXT(36),
	email TEXT,
	is_verified INTEGER DEFAULT (0),
	is_primary INTEGER DEFAULT (0),
	CONSTRAINT PersonEmails_PK PRIMARY KEY (email),
	CONSTRAINT PersonEmails_FK FOREIGN KEY (person_id) REFERENCES Persons(id) ON DELETE CASCADE
);
CREATE INDEX PersonEmails_person_id_IDX ON PersonEmails (person_id);
CREATE INDEX PersonEmails_is_verified_IDX ON PersonEmails (is_verified);
CREATE INDEX PersonEmails_is_primary_IDX ON PersonEmails (is_primary);
```

# PersonCredentialProvider
The PersonCredentialProvider table will contain configuration for different authentication providers.

``` sql
CREATE TABLE PersonCredentialProvider (
	id TEXT(36),
	name TEXT,
	"type" TEXT,
	config TEXT,
	CONSTRAINT PersonCredentialProvider_PK PRIMARY KEY (id)
);
CREATE INDEX PersonCredentialProvider_name_IDX ON PersonCredentialProvider (name);
CREATE INDEX PersonCredentialProvider_type_IDX ON PersonCredentialProvider ("type");
```

# PersonCredential
The PersonCredential table will contain authentication credentials for Persons

``` sql
CREATE TABLE PersonCredential (
	id TEXT(36),
	person_id TEXT(36),
	provider_id TEXT(36),
	provider_user_id TEXT(36),
	is_enabled INTEGER DEFAULT (1),
	CONSTRAINT PersonCredential_PK PRIMARY KEY (id),
	CONSTRAINT PersonCredential_FK FOREIGN KEY (person_id) REFERENCES Persons(id) ON DELETE CASCADE,
	CONSTRAINT PersonCredential_FK_1 FOREIGN KEY (provider_id) REFERENCES PersonCredentialProvider(id) ON DELETE CASCADE
);
CREATE INDEX PersonCredential_person_id_IDX ON PersonCredential (person_id);
CREATE INDEX PersonCredential_provider_id_IDX ON PersonCredential (provider_id);
CREATE INDEX PersonCredential_is_enabled_IDX ON PersonCredential (is_enabled);
```

# PersonLocal
The PersonLocal table will contain hashed and salted passwords for users authenticating via PersonCredentialProvider.Local type provider

``` sql
CREATE TABLE PersonLocal (
	person_id TEXT(36),
	hash TEXT,
	modified_at INTEGER,
	modified_by TEXT(36),
	CONSTRAINT PersonLocal_PK PRIMARY KEY (person_id),
	CONSTRAINT PersonLocal_FK FOREIGN KEY (person_id) REFERENCES Persons(id) ON DELETE CASCADE
);
```

# Tags
The Tags table will contain tags for categorization of other entities. Tags use the originating host and the tag name as a composite key to allow for us to search for commonalities across instances (for example, "show me Albums matching *::tag::jazz" to see all the jazz albums this instance is aware of, "show me Albums matching sundog-isle.reclaim.technology::tag::jazz" to see only albums tagged as jazz on that one specific instance named in the host field).

``` sql
CREATE TABLE Tags (
	id TEXT(36),
	host TEXT,
	tag TEXT,
	CONSTRAINT Tags_PK PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Tags_host_IDX ON Tags (host,tag);
```

# Labels
The Labels table will contain data about record labels/imprints that manage one or more Artists and their content.

``` sql
CREATE TABLE Labels (
	id TEXT(36),
	name TEXT,
	description TEXT,
	website TEXT,
	is_enabled INTEGER DEFAULT (1),
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	CONSTRAINT Labels_PK PRIMARY KEY (id),
	CONSTRAINT Labels_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Labels_FK_1 FOREIGN KEY (modified_at) REFERENCES Persons(id),
	CONSTRAINT Labels_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Labels_name_IDX ON Labels (name);
CREATE INDEX Labels_description_IDX ON Labels (description);
CREATE INDEX Labels_is_enabled_IDX ON Labels (is_enabled);
CREATE INDEX Labels_created_by_IDX ON Labels (created_by);
CREATE INDEX Labels_deleted_at_IDX ON Labels (deleted_at);
```

# LabelContacts
The LabelContacts table will contain key-value pairs of methods to contact representatives of the Label (for example, "fediverse: sundog@toot-lab.reclaim.technology" or "email: sundog@reclaim.technology").

``` sql
CREATE TABLE LabelContacts (
	id TEXT(36),
	label_id TEXT(36),
	"method" TEXT,
	address TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	sort_order INTEGER,
	CONSTRAINT LabelContacts_PK PRIMARY KEY (id),
	CONSTRAINT LabelContacts_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT LabelContacts_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT LabelContacts_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id),
	CONSTRAINT LabelContacts_FK_3 FOREIGN KEY (label_id) REFERENCES Labels(id) ON DELETE CASCADE
);
CREATE INDEX LabelContacts_sort_order_IDX ON LabelContacts (sort_order);
```

# LabelTags
The LabelTags table will contain Tags that have been assigned to a Label.

``` sql
CREATE TABLE LabelTags (
	label_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT LabelTags_PK PRIMARY KEY (label_id,tag_id),
	CONSTRAINT LabelTags_FK FOREIGN KEY (label_id) REFERENCES Labels(id) ON DELETE CASCADE,
	CONSTRAINT LabelTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX LabelTags_is_approved_IDX ON LabelTags (is_approved);
CREATE INDEX LabelTags_tag_id_IDX ON LabelTags (tag_id);
CREATE INDEX LabelTags_label_id_IDX ON LabelTags (label_id);
```

# Artists
The Artists table will contain Artists!

``` sql
CREATE TABLE Artists (
	id TEXT(36),
	name TEXT,
	bio TEXT,
	website TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	is_enabled INTEGER DEFAULT (1),
	is_public INTEGER DEFAULT (1),
	CONSTRAINT Artists_PK PRIMARY KEY (id),
	CONSTRAINT Artists_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Artists_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Artists_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Artists_name_IDX ON Artists (name);
CREATE INDEX Artists_is_enabled_IDX ON Artists (is_enabled);
CREATE INDEX Artists_is_public_IDX ON Artists (is_public);
```

# ArtistContacts
The ArtistContacts tablekey-value pairs of methods to contact representatives of the Artist (for example, "fediverse: sundog@toot-lab.reclaim.technology" or "email: sundog@reclaim.technology").

``` sql
CREATE TABLE ArtistContacts (
	id TEXT(36),
	artist_id TEXT(36),
	"method" TEXT,
	address TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	sort_order INTEGER,
	CONSTRAINT ArtistContacts_PK PRIMARY KEY (id),
	CONSTRAINT ArtistContacts_FK FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE
	CONSTRAINT ArtistContacts_FK_1 FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT ArtistContacts_FK_2 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT ArtistContacts_FK_3 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX ArtistContacts_artist_id_IDX ON ArtistContacts (artist_id);
CREATE INDEX ArtistContacts_deleted_at_IDX ON ArtistContacts (deleted_at);
CREATE INDEX ArtistContacts_sort_order_IDX ON ArtistContacts (sort_order);
```

# ArtistTags
The ArtistTags table will contain Tags that have been assigned to an Artist.

``` sql
CREATE TABLE ArtistTags (
	artist_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT ArtistTags_PK PRIMARY KEY (artist_id,tag_id),
	CONSTRAINT ArtistTags_FK FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE,
	CONSTRAINT ArtistTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX ArtistTags_is_approved_IDX ON ArtistTags (is_approved);
CREATE INDEX ArtistTags_artist_id_IDX ON ArtistTags (artist_id);
CREATE INDEX ArtistTags_tag_id_IDX ON ArtistTags (tag_id);
```

# Tracks
The Tracks table will contain Tracks!

``` sql
CREATE TABLE Tracks (
	id TEXT(36),
	title TEXT,
	description TEXT,
	duration REAL,
	is_public INTEGER,
	is_available INTEGER DEFAULT (1),
	preview_source TEXT,
	"source" TEXT,
	price INTEGER DEFAULT (0),
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	lyrics TEXT,
	CONSTRAINT Tracks_PK PRIMARY KEY (id),
	CONSTRAINT Tracks_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Tracks_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Tracks_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Tracks_title_IDX ON Tracks (title);
CREATE INDEX Tracks_duration_IDX ON Tracks (duration);
CREATE INDEX Tracks_is_public_IDX ON Tracks (is_public);
CREATE INDEX Tracks_is_available_IDX ON Tracks (is_available);
CREATE INDEX Tracks_price_IDX ON Tracks (price);
CREATE INDEX Tracks_created_by_IDX ON Tracks (created_by);
CREATE INDEX Tracks_deleted_at_IDX ON Tracks (deleted_at);
CREATE INDEX Tracks_lyrics_IDX ON Tracks (lyrics);
```

# TrackArtists
The TrackArtists table will be a one-to-many lookup table mapping Artists to a particular Track.

``` sql
CREATE TABLE TrackArtists (
	track_id TEXT(36),
	artist_id TEXT(36),
	is_primary INTEGER DEFAULT (0),
	CONSTRAINT TrackArtists_PK PRIMARY KEY (track_id,artist_id),
	CONSTRAINT TrackArtists_FK FOREIGN KEY (track_id) REFERENCES Tracks(id) ON DELETE CASCADE,
	CONSTRAINT TrackArtists_FK_1 FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE
);
CREATE INDEX TrackArtists_is_primary_IDX ON TrackArtists (is_primary);
CREATE INDEX TrackArtists_track_id_IDX ON TrackArtists (track_id);
CREATE INDEX TrackArtists_artist_id_IDX ON TrackArtists (artist_id);
```

# TrackTags
The TrackTags table will contain Tags that have been assigned to a Track.

``` sql
CREATE TABLE TrackTags (
	track_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT TrackTags_PK PRIMARY KEY (track_id,tag_id),
	CONSTRAINT TrackTags_FK FOREIGN KEY (track_id) REFERENCES Tracks(id) ON DELETE CASCADE,
	CONSTRAINT TrackTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX TrackTags_is_approved_IDX ON TrackTags (is_approved);
CREATE INDEX TrackTags_track_id_IDX ON TrackTags (track_id);
CREATE INDEX TrackTags_tag_id_IDX ON TrackTags (tag_id);
```

# Albums
The Albums table will contain Albums!

``` sql
CREATE TABLE Albums (
	id TEXT(36),
	title TEXT,
	description TEXT,
	is_public INTEGER DEFAULT (0),
	is_available INTEGER DEFAULT (1),
	preview_source TEXT,
	"source" TEXT,
	price INTEGER DEFAULT (0),
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	CONSTRAINT Albums_PK PRIMARY KEY (id),
	CONSTRAINT Albums_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Albums_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Albums_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Albums_title_IDX ON Albums (title);
CREATE INDEX Albums_description_IDX ON Albums (description);
CREATE INDEX Albums_is_public_IDX ON Albums (is_public);
CREATE INDEX Albums_is_available_IDX ON Albums (is_available);
CREATE INDEX Albums_price_IDX ON Albums (price);
CREATE INDEX Albums_created_by_IDX ON Albums (created_by);
CREATE INDEX Albums_deleted_at_IDX ON Albums (deleted_at);
```

# AlbumArtists
The AlbumArtists table will be a one-to-many lookup table mapping Artists to a particular Album.

``` sql
CREATE TABLE AlbumArtists (
	album_id TEXT(36),
	artist_id TEXT(36),
	is_primary INTEGER DEFAULT (0),
	CONSTRAINT AlbumArtists_PK PRIMARY KEY (album_id,artist_id),
	CONSTRAINT AlbumArtists_FK FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
	CONSTRAINT AlbumArtists_FK_1 FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE
);
CREATE INDEX AlbumArtists_is_primary_IDX ON AlbumArtists (is_primary);
CREATE INDEX AlbumArtists_album_id_IDX ON AlbumArtists (album_id);
CREATE INDEX AlbumArtists_artist_id_IDX ON AlbumArtists (artist_id);
```

# AlbumTags
The AlbumTags table will contain Tags that have been assigned to an Album.

``` sql
CREATE TABLE AlbumTags (
	album_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT AlbumTags_PK PRIMARY KEY (album_id,tag_id),
	CONSTRAINT AlbumTags_FK FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
	CONSTRAINT AlbumTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX AlbumTags_is_approved_IDX ON AlbumTags (is_approved);
CREATE INDEX AlbumTags_album_id_IDX ON AlbumTags (album_id);
CREATE INDEX AlbumTags_tag_id_IDX ON AlbumTags (tag_id);
```

# AlbumTracks
The AlbumTracks table will be a many-to-many lookup table mapping Tracks to Albums

``` sql
CREATE TABLE AlbumTracks (
	album_id TEXT(36),
	track_id TEXT(36),
	track_number INTEGER,
	CONSTRAINT AlbumTracks_PK PRIMARY KEY (album_id,track_id),
	CONSTRAINT AlbumTracks_FK FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
	CONSTRAINT AlbumTracks_FK_1 FOREIGN KEY (track_id) REFERENCES Tracks(id) ON DELETE CASCADE
);
CREATE INDEX AlbumTracks_track_number_IDX ON AlbumTracks (track_number);
CREATE INDEX AlbumTracks_album_id_IDX ON AlbumTracks (album_id);
CREATE INDEX AlbumTracks_track_id_IDX ON AlbumTracks (track_id);
```

# Playlists
The Playlists table will contain Playlists!

``` sql
CREATE TABLE Playlists (
	id TEXT(36),
	title TEXT,
	description TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_at INTEGER,
	deleted_at INTEGER,
	is_public INTEGER DEFAULT (1),
	CONSTRAINT Playlists_PK PRIMARY KEY (id),
	CONSTRAINT Playlists_FK FOREIGN KEY (created_by) REFERENCES Persons(id)
);
CREATE INDEX Playlists_title_IDX ON Playlists (title);
CREATE INDEX Playlists_description_IDX ON Playlists (description);
CREATE INDEX Playlists_created_by_IDX ON Playlists (created_by);
CREATE INDEX Playlists_created_at_IDX ON Playlists (created_at);
CREATE INDEX Playlists_modified_at_IDX ON Playlists (modified_at);
CREATE INDEX Playlists_deleted_at_IDX ON Playlists (deleted_at);
CREATE INDEX Playlists_is_public_IDX ON Playlists (is_public);
```

# PlaylistTracks
The PlaylistTracks table will be a many-to-many lookup table mapping Tracks to Playlists

``` sql
CREATE TABLE PlaylistTracks (
	playlist_track_id TEXT(36),
	playlist_id TEXT(36),
	track_id TEXT,
	track_number INTEGER,
	CONSTRAINT PlaylistTracks_PK PRIMARY KEY (playlist_track_id),
	CONSTRAINT PlaylistTracks_FK FOREIGN KEY (playlist_id) REFERENCES Playlists(id) ON DELETE CASCADE,
	CONSTRAINT PlaylistTracks_FK_1 FOREIGN KEY (track_id) REFERENCES Tracks(id) ON DELETE CASCADE
);
CREATE INDEX PlaylistTracks_track_number_IDX ON PlaylistTracks (track_number);
CREATE INDEX PlaylistTracks_playlist_id_IDX ON PlaylistTracks (playlist_id);
CREATE INDEX PlaylistTracks_track_id_IDX ON PlaylistTracks (track_id);
```

# PlaylistTags
The PlaylistTags table will contain Tags that have been assigned to a Playlist.

``` sql
CREATE TABLE PlaylistTags (
	playlist_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT PlaylistTags_PK PRIMARY KEY (playlist_id,tag_id),
	CONSTRAINT PlaylistTags_FK FOREIGN KEY (playlist_id) REFERENCES Playlists(id) ON DELETE CASCADE,
	CONSTRAINT PlaylistTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX PlaylistTags_is_approved_IDX ON PlaylistTags (is_approved);
CREATE INDEX PlaylistTags_playlist_id_IDX ON PlaylistTags (playlist_id);
CREATE INDEX PlaylistTags_tag_id_IDX ON PlaylistTags (tag_id);
```

# Comments
The Comments table will contain Comments!

``` sql
CREATE TABLE Comments (
	id TEXT(36),
	body TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	is_public INTEGER DEFAULT (1),
	is_approved INTEGER DEFAULT (0),
	in_reply_to TEXT(36),
	CONSTRAINT Comments_PK PRIMARY KEY (id),
	CONSTRAINT Comments_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Comments_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Comments_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Comments_body_IDX ON Comments (body);
CREATE INDEX Comments_created_at_IDX ON Comments (created_at);
CREATE INDEX Comments_modified_at_IDX ON Comments (modified_at);
CREATE INDEX Comments_deleted_at_IDX ON Comments (deleted_at);
CREATE INDEX Comments_is_public_IDX ON Comments (is_public);
CREATE INDEX Comments_is_approved_IDX ON Comments (is_approved);
CREATE INDEX Comments_in_reply_to_IDX ON Comments (in_reply_to);
```

# LabelComments
The LabelComments table will relate Comments to the Label they are about, if pertinent.

``` sql
CREATE TABLE LabelComments (
	label_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT LabelComments_PK PRIMARY KEY (label_id,comment_id),
	CONSTRAINT LabelComments_FK FOREIGN KEY (label_id) REFERENCES Labels(id) ON DELETE CASCADE,
	CONSTRAINT LabelComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX LabelComments_label_id_IDX ON LabelComments (label_id);
CREATE INDEX LabelComments_comment_id_IDX ON LabelComments (comment_id);
```

# ArtistComments
The ArtistComments table will relate Comments to the Artist they are about, if pertinent.

``` sql
CREATE TABLE ArtistComments (
	artist_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT ArtistComments_PK PRIMARY KEY (artist_id,comment_id),
	CONSTRAINT ArtistComments_FK FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE,
	CONSTRAINT ArtistComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX ArtistComments_artist_id_IDX ON ArtistComments (artist_id);
CREATE INDEX ArtistComments_comment_id_IDX ON ArtistComments (comment_id);
```

# TrackComments
The TrackComments table will relate Comments to the Track they are about, if pertinent.

``` sql
CREATE TABLE TrackComments (
	track_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT TrackComments_PK PRIMARY KEY (track_id,comment_id),
	CONSTRAINT TrackComments_FK FOREIGN KEY (track_id) REFERENCES Tracks(id) ON DELETE CASCADE,
	CONSTRAINT TrackComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX TrackComments_track_id_IDX ON TrackComments (track_id);
CREATE INDEX TrackComments_comment_id_IDX ON TrackComments (comment_id);
```

# AlbumComments
The AlbumComments table will relate Comments to the Album they are about, if pertinent.

``` sql
CREATE TABLE AlbumComments (
	album_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT AlbumComments_PK PRIMARY KEY (album_id,comment_id),
	CONSTRAINT AlbumComments_FK FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
	CONSTRAINT AlbumComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX AlbumComments_album_id_IDX ON AlbumComments (album_id);
CREATE INDEX AlbumComments_comment_id_IDX ON AlbumComments (comment_id);
```

# PlaylistComments
The PlaylistComments table will relate Comments to the Playlist they are about, if pertinent.

``` sql
CREATE TABLE PlaylistComments (
	playlist_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT PlaylistComments_PK PRIMARY KEY (playlist_id,comment_id),
	CONSTRAINT PlaylistComments_FK FOREIGN KEY (playlist_id) REFERENCES Playlists(id) ON DELETE CASCADE,
	CONSTRAINT PlaylistComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX PlaylistComments_playlist_id_IDX ON PlaylistComments (playlist_id);
CREATE INDEX PlaylistComments_comment_id_IDX ON PlaylistComments (comment_id);
```

# Images
The Images table will contain Images!

``` sql
CREATE TABLE Images (
	id TEXT(36),
	name TEXT,
	"path" TEXT,
	url TEXT,
	width INTEGER,
	height INTEGER,
	alt_text TEXT,
	is_public INTEGER DEFAULT (1),
	allow_hotlinking INTEGER DEFAULT (0),
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	CONSTRAINT Images_PK PRIMARY KEY (id),
	CONSTRAINT Images_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Images_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Images_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Images_name_IDX ON Images (name);
CREATE INDEX Images_path_IDX ON Images ("path");
CREATE INDEX Images_url_IDX ON Images (url);
CREATE INDEX Images_alt_text_IDX ON Images (alt_text);
CREATE INDEX Images_is_public_IDX ON Images (is_public);
CREATE INDEX Images_allow_hotlinking_IDX ON Images (allow_hotlinking);
CREATE INDEX Images_created_by_IDX ON Images (created_by);
CREATE INDEX Images_deleted_at_IDX ON Images (deleted_at);
```

# Videos
The Videos table will contain Videos!

``` sql
CREATE TABLE Videos (
	id TEXT(36),
	name TEXT,
	"path" TEXT,
	url TEXT,
	width INTEGER,
	height INTEGER,
	duration NUMERIC,
	alt_text TEXT,
	is_public INTEGER DEFAULT (1),
	allow_hotlinking INTEGER DEFAULT (0),
	created_by TEXT(36),
	created_at INTEGER,
	modified_by TEXT(36),
	modified_at INTEGER,
	deleted_by TEXT(36),
	deleted_at INTEGER,
	CONSTRAINT Videos_PK PRIMARY KEY (id),
	CONSTRAINT Videos_FK FOREIGN KEY (created_by) REFERENCES Persons(id),
	CONSTRAINT Videos_FK_1 FOREIGN KEY (modified_by) REFERENCES Persons(id),
	CONSTRAINT Videos_FK_2 FOREIGN KEY (deleted_by) REFERENCES Persons(id)
);
CREATE INDEX Videos_name_IDX ON Videos (name);
CREATE INDEX Videos_path_IDX ON Videos ("path");
CREATE INDEX Videos_url_IDX ON Videos (url);
CREATE INDEX Videos_alt_text_IDX ON Videos (alt_text);
CREATE INDEX Videos_is_public_IDX ON Videos (is_public);
CREATE INDEX Videos_allow_hotlinking_IDX ON Videos (allow_hotlinking);
CREATE INDEX Videos_created_by_IDX ON Videos (created_by);
CREATE INDEX Videos_deleted_at_IDX ON Videos (deleted_at);
```

# Articles
The Articles table will contain Articles!

``` sql
CREATE TABLE Articles (
	id TEXT(36),
	title TEXT,
	body TEXT,
	description TEXT,
	created_by TEXT(36),
	created_at INTEGER,
	modified_at INTEGER,
	published_at INTEGER,
	deleted_at INTEGER,
	is_public INTEGER DEFAULT (0),
	is_draft INTEGER DEFAULT (1),
	CONSTRAINT Articles_PK PRIMARY KEY (id),
	CONSTRAINT Articles_FK FOREIGN KEY (created_by) REFERENCES Persons(id)
);
CREATE INDEX Articles_title_IDX ON Articles (title);
CREATE INDEX Articles_body_IDX ON Articles (body);
CREATE INDEX Articles_description_IDX ON Articles (description);
CREATE INDEX Articles_created_by_IDX ON Articles (created_by);
CREATE INDEX Articles_published_at_IDX ON Articles (published_at);
CREATE INDEX Articles_deleted_at_IDX ON Articles (deleted_at);
CREATE INDEX Articles_is_public_IDX ON Articles (is_public);
CREATE INDEX Articles_is_draft_IDX ON Articles (is_draft);
```

# ArticleTags
The ArticleTags table will contain Tags that have been assigned to an Article.

``` sql
CREATE TABLE ArticleTags (
	article_id TEXT(36),
	tag_id TEXT(36),
	is_approved INTEGER DEFAULT (0),
	CONSTRAINT ArticleTags_PK PRIMARY KEY (article_id,tag_id),
	CONSTRAINT ArticleTags_FK FOREIGN KEY (article_id) REFERENCES Articles(id) ON DELETE CASCADE,
	CONSTRAINT ArticleTags_FK_1 FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);
CREATE INDEX ArticleTags_is_approved_IDX ON ArticleTags (is_approved);
```

# ArticleComments
The ArticleComments table will relate Comments to the Article they are about, if pertinent.

``` sql
CREATE TABLE ArticleComments (
	article_id TEXT(36),
	comment_id TEXT(36),
	CONSTRAINT ArticleComments_PK PRIMARY KEY (article_id,comment_id),
	CONSTRAINT ArticleComments_FK FOREIGN KEY (article_id) REFERENCES Articles(id) ON DELETE CASCADE,
	CONSTRAINT ArticleComments_FK_1 FOREIGN KEY (comment_id) REFERENCES Comments(id) ON DELETE CASCADE
);
CREATE INDEX ArticleComments_article_id_IDX ON ArticleComments (article_id);
CREATE INDEX ArticleComments_comment_id_IDX ON ArticleComments (comment_id);
```

# LabelImages

# LabelVideos

# ArtistImages

# ArtistVideos

# AlbumImages

# AlbumVideos

# TrackImages

# TrackVideos

# PlaylistImages

# PlaylistVideos

# ArticleImages

# ArticleVideos