013-contacts.sql

-- In order to be able to reach people without necessary knowing their
-- numbers and to be able to bill recipient cost centre, create an
-- address book of sorts.

create table contact (
	-- Boring primary key.
	id bigserial not null primary key,

	-- Arbitrary user identifier string for the API.
	userid varchar not null,

	-- Phone number of the contact for the Sender.
	number varchar not null,

	-- Cost centre of the user for billing.
	cost_centre varchar not null collate "und-x-icu",

	-- Replication & other metadata.
	meta jsonb not null default '{}',

	-- User identifiers are unique, but numbers can repeat.
	unique(userid)
);

-- Make sure we don't point outside of the cost_centre table.
alter table contact add constraint contact_cost_centre_fkey
	foreign key (cost_centre)
		references cost_centre (name)
		on delete restrict
		on update cascade;

-- Speed up some metadata-based searches.
create index contact_meta_idx on contact
	using gin (meta jsonb_path_ops);

-- Add billing method for recipients.
alter type billing_method add value 'bill-recipient';

-- Allow applications to use more billing methods at once.
alter table application
	add column billing_methods billing_method[] not null default '{}';

update application set billing_methods = ARRAY[billing];

alter table application drop column billing;

-- Add a column to history for posterity when sending to contacts.
alter table history add column userid varchar;