🌱🏠 a cloud service to enable your own server (owned by you and running on your computer) to be accessible on the internet in seconds, no credit card required https://greenhouse.server.garden/
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

103 lines
3.2 KiB

CREATE TABLE tenants (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
subdomain TEXT UNIQUE,
hashed_password TEXT NOT NULL,
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
lax_cookie BOOLEAN NOT NULL DEFAULT TRUE,
sms_alarm_number TEXT NULL,
billing_alarm_cents INTEGER NOT NULL DEFAULT 150,
service_limit_cents INTEGER NOT NULL DEFAULT 200,
port_start INTEGER NOT NULL DEFAULT 0,
port_end INTEGER NOT NULL DEFAULT 0,
port_bucket INTEGER NOT NULL DEFAULT 0,
created TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE api_tokens (
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
key_name TEXT NOT NULL,
hashed_token TEXT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
created TIMESTAMP NOT NULL DEFAULT NOW(),
last_used TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (tenant_id, key_name)
);
CREATE INDEX by_hashed_token ON api_tokens (hashed_token);
CREATE TABLE vps_instances (
service_provider TEXT NOT NULL,
provider_instance_id TEXT NOT NULL,
tenant_id INTEGER NULL REFERENCES tenants(id) ON DELETE RESTRICT,
ipv4 TEXT NOT NULL,
ipv6 TEXT NOT NULL,
bytes_monthly NUMERIC NOT NULL,
created TIMESTAMP NOT NULL DEFAULT NOW(),
deprecated BOOLEAN NOT NULL DEFAULT FALSE,
deleted BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (service_provider, provider_instance_id)
);
CREATE TABLE tenant_vps_instance (
billing_year INTEGER NOT NULL,
billing_month INTEGER NOT NULL,
service_provider TEXT NOT NULL,
provider_instance_id TEXT NOT NULL,
tenant_id INTEGER NULL REFERENCES tenants(id) ON DELETE RESTRICT,
shadow_config TEXT NOT NULL,
bytes NUMERIC NOT NULL DEFAULT 0,
active BOOLEAN NOT NULL DEFAULT FALSE,
deactivated_at TIMESTAMP NULL,
CONSTRAINT vps_instance
FOREIGN KEY(service_provider, provider_instance_id)
REFERENCES vps_instances(service_provider, provider_instance_id) ON DELETE RESTRICT,
CONSTRAINT pk_tenant_vps_instance PRIMARY KEY (billing_year, billing_month, tenant_id, service_provider, provider_instance_id)
);
CREATE TABLE tenant_metrics_bandwidth (
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
measured TIMESTAMP NOT NULL DEFAULT NOW(),
bytes NUMERIC NOT NULL,
PRIMARY KEY (tenant_id, measured)
);
CREATE TABLE email_verification_tokens (
token TEXT PRIMARY KEY NOT NULL,
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
expires TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE session_cookies (
id TEXT PRIMARY KEY NOT NULL,
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
expires TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE external_domains (
tenant_id INTEGER REFERENCES tenants(id) ON DELETE RESTRICT,
domain_name TEXT NOT NULL UNIQUE,
last_verified TIMESTAMP NOT NULL DEFAULT NOW(),
created TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE pki_key_pairs (
ca_name TEXT NOT NULL,
name TEXT NOT NULL,
key_bytes BYTEA NOT NULL,
cert_bytes BYTEA NOT NULL,
CONSTRAINT pk_pki_key_pairs PRIMARY KEY (ca_name, name)
);
-- CREATE TABLE task_queue (
-- id SERIAL PRIMARY KEY,
-- task TEXT NOT NULL
-- );
CREATE TABLE reserved_ports_counter (
port INTEGER NOT NULL,
bucket INTEGER NOT NULL
);
INSERT INTO reserved_ports_counter (port, bucket) VALUES (10000, 0);