parsav  Diff

			delete from parsav_config where
				key = $1::text 
		]];
	};

	actor_fetch_uid = {
		params = {uint64}, sql = [[
			select a.id, a.nym, a.handle, a.origin, a.bio,
			       a.avataruri, a.rank, a.quota, a.key, a.epithet,
			       extract(epoch from a.knownsince)::bigint,
				   coalesce(a.handle || '@' || s.domain,
				            '@' || a.handle) as xid,
			       a.invites

			from      parsav_actors  as a
			left join parsav_servers as s
				on a.origin = s.id
			where a.id = $1::bigint
		]];
	};

	actor_fetch_xid = {
		params = {pstring}, sql = [[
			select a.id, a.nym, a.handle, a.origin, a.bio,
			       a.avataruri, a.rank, a.quota, a.key, a.epithet,
			       extract(epoch from a.knownsince)::bigint,
				   coalesce(a.handle || '@' || s.domain,
				            '@' || a.handle) as xid,
			       a.invites,

				coalesce(s.domain,
				        (select value from parsav_config
							where key='domain' limit 1)) as domain

			from      parsav_actors  as a
			left join parsav_servers as s
				on a.origin = s.id

			where $1::text = (a.handle || '@' || domain) or
			      $1::text = ('@' || a.handle || '@' || domain) or
				  (a.origin is null and
					  $1::text = a.handle or
					  $1::text = ('@' || a.handle))

		]];
	};

	actor_purge_uid = {
		params = {uint64}, cmd = true, sql = [[
			with d as ( -- cheating
				delete from parsav_sanctions where victim = $1::bigint
................................................................................
	};

	actor_create = {
		params = {
			rawstring, rawstring, uint64, lib.store.timepoint,
			rawstring, rawstring, lib.mem.ptr(uint8),
			rawstring, uint16, uint32, uint32
		};
		sql = [[
			insert into parsav_actors (
				nym,handle,
				origin,knownsince,
				bio,avataruri,key,
				epithet,rank,quota,
				invites
			) values ($1::text, $2::text,
				case when $3::bigint = 0 then null
				     else $3::bigint end,
				to_timestamp($4::bigint),
				$5::bigint, $6::bigint, $7::bytea,
				$8::text, $9::smallint, $10::integer,
				$11::integer
			) returning id
		]];
	};

	actor_auth_pw = {
		params = {pstring,rawstring,pstring,lib.store.inet}, sql = [[
			select a.aid, a.uid, a.name from parsav_auth as a
................................................................................
		]];
	};

	actor_enum_local = {
		params = {}, sql = [[
			select id, nym, handle, origin, bio,
			       null::text, rank, quota, key, epithet,
			       extract(epoch from knownsince)::bigint,
					'@' || handle,
				   invites
			from parsav_actors where origin is null
			order by nullif(rank,0) nulls last, handle
		]];
	};

	actor_enum = {
		params = {}, sql = [[
			select a.id, a.nym, a.handle, a.origin, a.bio,
			       a.avataruri, a.rank, a.quota, a.key, a.epithet,
			       extract(epoch from a.knownsince)::bigint,
				   coalesce(a.handle || '@' || s.domain,
				            '@' || a.handle) as xid,
				   invites
			from parsav_actors a
			left join parsav_servers s on s.id = a.origin

			order by nullif(a.rank,0) nulls last, a.handle, a.origin
		]];
	};

	actor_stats = {
		params = {uint64}, sql = ([[
			with tweets as (
				select from parsav_posts where author = $1::bigint
			),
			follows as (
				select relatee as user from parsav_rels
					where relator = $1::bigint and kind = <follow>
			),
			followers as (
				select relator as user from parsav_rels
					where relatee = $1::bigint and kind = <follow>
			),
			mutuals as (
				select * from follows  intersect  select * from followers
			)

			values (
				(select count(tweets.*)::bigint from tweets),
				(select count(follows.*)::bigint from follows),
				(select count(followers.*)::bigint from followers),
				(select count(mutuals.*)::bigint from mutuals)
			)
		]]):gsub('<(%w+)>',function(r) return tostring(lib.store.relation.idvmap[r]) end)
	};

	actor_auth_how = {
		params = {rawstring, lib.store.inet}, sql = [[
		with mts as (select a.kind from parsav_auth as a
			left join parsav_actors as u on u.id = a.uid
			where (a.uid is null or u.handle = $1::text or (
................................................................................
				(select count(*) from mts where kind like 'challenge-%') > 0,
				(select count(*) from mts where kind = 'trust') > 0
		]]; -- cheat
	};

	actor_session_fetch = {
		params = {uint64, lib.store.inet, int64}, sql = [[
			select a.id, a.nym, a.handle, a.origin, a.bio,
			       a.avataruri, a.rank, a.quota, a.key, a.epithet,
			       extract(epoch from a.knownsince)::bigint,
				   coalesce(a.handle || '@' || s.domain,
				            '@' || a.handle) as xid,

			       au.restrict,
						array['post'    ] <@ au.restrict,
						array['edit'    ] <@ au.restrict,
						array['account' ] <@ au.restrict,
						array['upload'  ] <@ au.restrict,
						array['moderate'] <@ au.restrict,
						array['admin'   ] <@ au.restrict

			from      parsav_auth au
			left join parsav_actors a     on au.uid = a.id
			left join parsav_servers s    on a.origin = s.id

			where au.aid = $1::bigint and au.blacklist = false and
				(au.netmask is null or au.netmask >> $2::inet) and
				($3::bigint = 0 or --slightly abusing the epoch time fmt here, but
					((a.authtime   is null or a.authtime   <= to_timestamp($3::bigint)) and
					 (au.valperiod is null or au.valperiod <= to_timestamp($3::bigint))))
		]];
	};

	actor_powers_fetch = {
		params = {uint64}, sql = [[
			select key, allow from parsav_rights where actor = $1::bigint
		]]
................................................................................
	actor_power_delete = {
		params = {uint64,lib.mem.ptr(int8)}, cmd = true, sql = [[
			delete from parsav_rights where
				actor = $1::bigint and
				key = $2::text
		]]
	};

































	auth_sigtime_user_fetch = {
		params = {uint64}, sql = [[
			select extract(epoch from authtime)::bigint

			from parsav_actors where id = $1::bigint
		]];
	};

	auth_sigtime_user_alter = {
		params = {uint64,int64}, cmd = true, sql = [[
			update parsav_actors set
				authtime = to_timestamp($2::bigint)
				where id = $1::bigint
		]];
	};

	auth_create_pw = {
		params = {uint64, binblob, pstring}, cmd = true, sql = [[
			insert into parsav_auth (uid, name, kind, cred, comment) values (
				$1::bigint,
				(select handle from parsav_actors where id = $1::bigint),
				'pw-sha256', $2::bytea,
				$3::text
			)
		]]
	};

	auth_purge_type = {
		params = {rawstring, uint64, rawstring}, cmd = true, sql = [[
			delete from parsav_auth where
................................................................................
			rawstring, rawstring, rawstring;
		}, cmd = true, sql = [[
			update parsav_posts set
				subject = $4::text,
				acl = $5::text,
				body = $6::text,
				chgcount = $2::integer,
				edited = to_timestamp($3::bigint)
			where id = $1::bigint
		]]
	};

	post_create = {
		params = {
			uint64, rawstring, rawstring, rawstring,
................................................................................
			insert into parsav_posts (
				author, subject, acl, body,
				parent, posted, discovered,
				circles, mentions, convoheaduri
			) values (
				$1::bigint, case when $2::text = '' then null else $2::text end,
				$3::text, $4::text, 
				$5::bigint, to_timestamp($6::bigint), now(),
				array[]::bigint[], array[]::bigint[], $7::text
			) returning id
		]]; -- TODO array handling
	};

	post_destroy_prepare = {
		params = {uint64}, cmd = true, sql = [[
................................................................................
		params = {uint64}, cmd = true, sql = [[
			delete from parsav_posts where id = $1::bigint
		]]
	};
	
	post_fetch = {
		params = {uint64}, sql = [[
			with counts as (
				select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
					inner join parsav_posts as p on p.id = a.subject
				group by a.kind, p.id
			)

			select a.origin is null,
				p.id, p.author, p.subject, p.acl, p.body,
				extract(epoch from p.posted    )::bigint,
				extract(epoch from p.discovered)::bigint,
				extract(epoch from p.edited    )::bigint,
				p.parent, p.convoheaduri, p.chgcount,
				coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint,
				coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
				coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer

			from parsav_posts as p
				inner join parsav_actors          as a on p.author = a.id
				left join  parsav_actor_conf_ints as c on c.uid    = a.id and c.key = 'ui-accent'
			where p.id = $1::bigint
		]];
	};

	post_enum_parent = {
		params = {uint64}, sql = [[
			with counts as (
				select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
					inner join parsav_posts as p on p.id = a.subject
				group by a.kind, p.id
			)

			select a.origin is null,
				p.id, p.author, p.subject, p.acl, p.body,
				extract(epoch from p.posted    )::bigint,
				extract(epoch from p.discovered)::bigint,
				extract(epoch from p.edited    )::bigint,
				p.parent, p.convoheaduri, p.chgcount,
				coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint,
				coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
				coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer

			from parsav_posts as p
				inner join parsav_actors as a on a.id = p.author
				left join  parsav_actor_conf_ints as c on c.uid = a.id and c.key = 'ui-accent'


			where p.parent = $1::bigint
			order by p.posted, p.discovered asc

		]]
	};

	thread_latest_arrival_calc = {
		params = {uint64}, sql = [[
			with recursive posts(id) as (
				select id from parsav_posts where parent = $1::bigint
			union
................................................................................
			maxes as (
				select unnest(array[max(p.posted), max(p.discovered), max(p.edited)]) as m
					from posts
					inner join parsav_posts as p
						on p.id = posts.id
			)

			select extract(epoch from max(m))::bigint from maxes
		]];
	};

	post_react_simple = {
		params = {uint64, uint64, pstring}, sql = [[
			insert into parsav_acts (kind,actor,subject) values (
				$3::text, $1::bigint, $2::bigint
			) returning id
		]];
	};

	post_react_cancel = {
		params = {uint64, uint64, pstring}, cmd = true, sql = [[
			delete from parsav_acts where
................................................................................
				($2::bigint = 0 or subject = $2::bigint) and
				($3::text is null or kind  = $3::text  )
		]]
	};

	post_enum_author_uid = {
		params = {uint64,uint64,uint64,uint64, uint64}, sql = [[
			with ownposts as (
				select *, 0::bigint as rtid from parsav_posts as p
				where p.author = $5::bigint and
					($1::bigint = 0 or p.posted <= to_timestamp($1::bigint)) and
					($2::bigint = 0 or to_timestamp($2::bigint) < p.posted)
			),

			retweets as (
				select p.*, a.id as rtid from parsav_acts as a
					inner join parsav_posts as p on a.subject = p.id
				where a.actor = $5::bigint and
					  a.kind = 'rt' and
					  ($1::bigint = 0 or a.time <= to_timestamp($1::bigint)) and

					  ($2::bigint = 0 or to_timestamp($2::bigint) < a.time)
			),


			allposts as (select *, 0::bigint  as retweeter from ownposts
			      union  select *, $5::bigint as retweeter from retweets),

			counts as (
				select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
					inner join parsav_posts as p on p.id = a.subject
				group by a.kind, p.id
			)

			select a.origin is null,
				p.id, p.author, p.subject, p.acl, p.body,
				extract(epoch from p.posted    )::bigint,
				extract(epoch from p.discovered)::bigint,
				extract(epoch from p.edited    )::bigint,
				p.parent, p.convoheaduri, p.chgcount,
				coalesce(c.value,-1)::smallint,
				p.retweeter, p.rtid,
				coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
				coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer
			from allposts as p
				inner join parsav_actors as a on p.author = a.id
				left  join parsav_actor_conf_ints as c
					on c.key = 'ui-accent' and
					   c.uid = a.id
			order by (p.posted, p.discovered) desc
			limit case when $3::bigint = 0 then null
					   else $3::bigint end
			offset $4::bigint
		]]
	};

	-- maybe there's some way to unify these two, idk, im tired

	timeline_instance_fetch = {
		params = {uint64, uint64, uint64, uint64}, sql = [[
			with posts as (
				select true,
					p.id, p.author, p.subject, p.acl, p.body,
					extract(epoch from p.posted    )::bigint,
					extract(epoch from p.discovered)::bigint,
					extract(epoch from p.edited    )::bigint,
					p.parent, null::text, p.chgcount,
					coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint


				from parsav_posts as p
					inner join parsav_actors          as a on p.author = a.id
					left join  parsav_actor_conf_ints as c on c.uid    = a.id and c.key = 'ui-accent'
				where
					($1::bigint = 0 or p.posted <= to_timestamp($1::bigint)) and


					($2::bigint = 0 or to_timestamp($2::bigint) < p.posted) and
					(a.origin is null)
				order by (p.posted, p.discovered) desc


				limit case when $3::bigint = 0 then null
						   else $3::bigint end
				offset $4::bigint
			), counts as (
				select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
					inner join parsav_posts as p on p.id = a.subject
				group by a.kind, p.id


			)















			select *,
				coalesce((select ct from counts as c where kind = 'like' and c.subject = posts.id),0)::integer,
				coalesce((select ct from counts as c where kind = 'rt' and c.subject = posts.id),0)::integer
			from posts












		]]
	};

	artifact_instantiate = {
		params = {binblob, binblob, pstring}, sql = [[
			insert into parsav_artifacts (content,hash,mime) values (
				$1::bytea, $2::bytea, $3::text
			) on conflict do nothing returning id
................................................................................
			buf[2] = tycode
			buf[3] = sz
			for j=0,sz do buf[4 + j] = i.v6[j] end -- 😬
			return buf
		end
	end;
}










local con = symbol(&lib.pq.PGconn)
local prep = {}
local function sqlsquash(s) return s
	:gsub('%%include (.-)%%',function(f)
		return sqlsquash(lib.util.ingest('backend/schema/' .. f))
	end) -- include dependencies
	:gsub('%-%-.-\n','') -- remove disruptive line comments
	:gsub('%-%-.-$','') -- remove unnecessary terminal comments

	:gsub('%s+',' ') -- remove whitespace
	:gsub('^%s*(.-)%s*$','%1') -- chomp
end


















for k,q in pairs(queries) do
	local qt = sqlsquash(q.sql)
	local stmt = 'parsavpg_' .. k
	terra q.prep([con])
		var res = lib.pq.PQprepare([con], stmt, qt, [#q.params], nil)
		defer lib.pq.PQclear(res)
................................................................................
		lib.dbg(['searching for hashed password credentials in format SHA' .. tostring(hash)])
		var [out]
		[vdrs]
		lib.dbg(['could not find password hash'])
	end
end

local schema = sqlsquash(lib.util.ingest('backend/schema/pgsql.sql'))
local obliterator = sqlsquash(lib.util.ingest('backend/schema/pgsql-drop.sql'))

local privupdate = terra(
	src: &lib.store.source,
	ac: &lib.store.actor
): {}
	var pdef: lib.store.powerset pdef:clear()
	var map = array([privmap])
................................................................................
		return con
	end];

	close = [terra(src: &lib.store.source) lib.pq.PQfinish([&lib.pq.PGconn](src.handle)) end];

	tx_enter = txdo, tx_complete = txdone;

	conprep = [terra(src: &lib.store.source, mode: lib.store.prepmode.t)
		var [con] = [&lib.pq.PGconn](src.handle)
		if mode == lib.store.prepmode.full then [prep]
		elseif mode == lib.store.prepmode.conf or
		       mode == lib.store.prepmode.admin then 
			queries.conf_get.prep(con)
			queries.conf_set.prep(con)
			queries.conf_reset.prep(con)
			if mode == lib.store.prepmode.admin then 
			end
		else lib.bail('unsupported connection preparation mode') end
	end];

	dbsetup = [terra(src: &lib.store.source)
		var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), schema)
		if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
			lib.report('successfully instantiated schema in database')
			return true
		else
			lib.warn('backend pgsql - failed to initialize database: \n', lib.pq.PQresultErrorMessage(res))
			return false
		end
	end];

	obliterate_everything = [terra(src: &lib.store.source)
		var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), obliterator)
		if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
			lib.report('successfully wiped out everything parsav-related in database')
			return true
		else
			lib.warn('backend pgsql - failed to obliterate database: \n', lib.pq.PQresultErrorMessage(res))
			return false
................................................................................

			var a = row_to_actor(&r, 0)
			a.ptr.source = src

			var au = [lib.stat(lib.store.auth)] { ok = true }
			au.val.aid = aid
			au.val.uid = a.ptr.id
			if not r:null(0,14) then -- restricted?
				au.val.privs:clear()
				(au.val.privs.post    << r:bool(0,15)) 
				(au.val.privs.edit    << r:bool(0,16))
				(au.val.privs.account << r:bool(0,17))
				(au.val.privs.upload  << r:bool(0,18))
				(au.val.privs.moderate<< r:bool(0,19))
				(au.val.privs.admin   << r:bool(0,20))
			else au.val.privs:fill() end

			return au, a
		end

		::fail:: return [lib.stat   (lib.store.auth) ] { ok = false        },
			            [lib.mem.ptr(lib.store.actor)] { ptr = nil, ct = 0 }
................................................................................

	post_retweet = [terra(
		src: &lib.store.source,
		uid: uint64,
		post: uint64,
		undo: bool
	): {}

		if not undo then
			queries.post_react_simple.exec(src,uid,post,"rt")
		else
			queries.post_react_cancel.exec(src,uid,post,"rt")
		end
	end];
	post_like = [terra(
		src: &lib.store.source,
		uid: uint64,
		post: uint64,
		undo: bool
	): {}

		if not undo then
			queries.post_react_simple.exec(src,uid,post,"like")
		else
			queries.post_react_cancel.exec(src,uid,post,"like")
		end
	end];
	post_liked_uid = [terra(
		src: &lib.store.source,
		uid: uint64,
................................................................................
		-- check against default rights, insert records for wherever powers differ
		lib.dbg('created new actor, establishing powers')
		privupdate(src,ac)

		lib.dbg('powers established')
		return ac.id
	end];















































	actor_purge_uid = [terra(
		src: &lib.store.source,
		uid: uint64
	) queries.actor_purge_uid.exec(src,uid) end];

	auth_enum_uid = [terra(
................................................................................
	): {}
		var hash: uint8[lib.crypt.algsz.sha256]
		if lib.md.mbedtls_md(lib.md.mbedtls_md_info_from_type(lib.crypt.alg.sha256.id),
			[&uint8](pw.ptr), pw.ct, &hash[0]) ~= 0 then
			lib.bail('cannot hash password')
		end
		if reset then queries.auth_purge_type.exec(src, nil, uid, 'pw-%') end
		queries.auth_create_pw.exec(src, uid, binblob {ptr = &hash[0], ct = [hash.type.N]}, comment)
	end];

	auth_purge_pw = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}
		queries.auth_purge_type.exec(src, handle, uid, 'pw-%')
	end];

	auth_purge_otp = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}

			delete from parsav_config where
				key = $1::text 
		]];
	};

	actor_fetch_uid = {
		params = {uint64}, sql = [[
			select (pg_temp.parsavpg_translate_actor(a)).*






				from parsav_actors as a


				where a.id = $1::bigint
		]];
	};

	actor_fetch_xid = {
		params = {pstring}, sql = [[
			with txd as (
				select (pg_temp.parsavpg_translate_actor(a)).* from parsav_actors as a




			)








			select * from txd as a where $1::text = xid or

				  (a.origin is null and
					  $1::text = a.handle or
					  $1::text = (a.handle ||'@'||
				        (select value from parsav_config where key='domain')))
		]];
	};

	actor_purge_uid = {
		params = {uint64}, cmd = true, sql = [[
			with d as ( -- cheating
				delete from parsav_sanctions where victim = $1::bigint
................................................................................
	};

	actor_create = {
		params = {
			rawstring, rawstring, uint64, lib.store.timepoint,
			rawstring, rawstring, lib.mem.ptr(uint8),
			rawstring, uint16, uint32, uint32

		}, sql = [[
			insert into parsav_actors (
				nym,handle,
				origin,knownsince,
				bio,avataruri,key,
				epithet,rank,quota,
				invites,authtime
			) values ($1::text, $2::text,
				case when $3::bigint = 0 then null
				     else $3::bigint end,
				$4::bigint,
				$5::bigint, $6::bigint, $7::bytea,
				$8::text, $9::smallint, $10::integer,
				$11::integer,$4::bigint
			) returning id
		]];
	};

	actor_auth_pw = {
		params = {pstring,rawstring,pstring,lib.store.inet}, sql = [[
			select a.aid, a.uid, a.name from parsav_auth as a
................................................................................
		]];
	};

	actor_enum_local = {
		params = {}, sql = [[
			select id, nym, handle, origin, bio,
			       null::text, rank, quota, key, epithet,
			       knownsince::bigint,
					'@' || handle,
				   invites
			from parsav_actors where origin is null
			order by nullif(rank,0) nulls last, handle
		]];
	};

	actor_enum = {
		params = {}, sql = [[
			select (pg_temp.parsavpg_translate_actor(a)).*





				from parsav_actors as a


			order by nullif(a.rank,0) nulls last, a.handle, a.origin
		]];
	};

	actor_stats = {
		params = {uint64}, sql = [[
			with tweets as (
				select from parsav_posts where author = $1::bigint
			),
			follows as (
				select relatee as user from parsav_rels
					where relator = $1::bigint and kind = <rel:follow>
			),
			followers as (
				select relator as user from parsav_rels
					where relatee = $1::bigint and kind = <rel:follow>
			),
			mutuals as (
				select * from follows  intersect  select * from followers
			)

			values (
				(select count(tweets.*)::bigint from tweets),
				(select count(follows.*)::bigint from follows),
				(select count(followers.*)::bigint from followers),
				(select count(mutuals.*)::bigint from mutuals)
			)
		]]
	};

	actor_auth_how = {
		params = {rawstring, lib.store.inet}, sql = [[
		with mts as (select a.kind from parsav_auth as a
			left join parsav_actors as u on u.id = a.uid
			where (a.uid is null or u.handle = $1::text or (
................................................................................
				(select count(*) from mts where kind like 'challenge-%') > 0,
				(select count(*) from mts where kind = 'trust') > 0
		]]; -- cheat
	};

	actor_session_fetch = {
		params = {uint64, lib.store.inet, int64}, sql = [[
			select (pg_temp.parsavpg_translate_actor(a)).*,





			       au.restrict,
						array['post'    ] <@ au.restrict,
						array['edit'    ] <@ au.restrict,
						array['account' ] <@ au.restrict,
						array['upload'  ] <@ au.restrict,
						array['moderate'] <@ au.restrict,
						array['admin'   ] <@ au.restrict

			from      parsav_auth au
			left join parsav_actors a     on au.uid = a.id


			where au.aid = $1::bigint and au.blacklist = false and
				(au.netmask is null or au.netmask >> $2::inet) and
				($3::bigint = 0 or --slightly abusing the epoch time fmt here, but
					((a.authtime   is null or a.authtime   <= $3::bigint) and
					 (au.valperiod is null or au.valperiod <= $3::bigint)))
		]];
	};

	actor_powers_fetch = {
		params = {uint64}, sql = [[
			select key, allow from parsav_rights where actor = $1::bigint
		]]
................................................................................
	actor_power_delete = {
		params = {uint64,lib.mem.ptr(int8)}, cmd = true, sql = [[
			delete from parsav_rights where
				actor = $1::bigint and
				key = $2::text
		]]
	};

	actor_rel_create = {
		params = {uint16,uint64, uint64}, cmd = true, sql = [[
			insert into parsav_rels (kind,relator,relatee)
				values($1::smallint, $2::bigint, $3::bigint)
			on conflict do nothing
		]];
	};

	actor_rel_destroy = {
		params = {uint16,uint64, uint64}, cmd = true, sql = [[
			delete from parsav_rels where
				kind  = $1::smallint and
				relator = $2::bigint and
				relatee = $3::bigint
		]];
	};

	actor_rel_enum = {
		params = {uint64, uint64}, sql = [[
			select kind from parsav_rels where
				relator = $1::bigint and
				relatee = $2::bigint
		]];
	};

	actor_notice_enum = {
		params = {uint64}, sql = [[
			select (notice).* from pg_temp.parsavpg_notices
			where rcpt = $1::bigint
		]];
	};

	auth_sigtime_user_fetch = {
		params = {uint64}, sql = [[

			select authtime::bigint
			from parsav_actors where id = $1::bigint
		]];
	};

	auth_sigtime_user_alter = {
		params = {uint64,int64}, cmd = true, sql = [[
			update parsav_actors set
				authtime = $2::bigint
				where id = $1::bigint
		]];
	};

	auth_create_pw = {
		params = {uint64, binblob, int64, pstring}, cmd = true, sql = [[
			insert into parsav_auth (uid, name, kind, cred, valperiod, comment) values (
				$1::bigint,
				(select handle from parsav_actors where id = $1::bigint),
				'pw-sha256', $2::bytea,
				$3::bigint, $4::text
			)
		]]
	};

	auth_purge_type = {
		params = {rawstring, uint64, rawstring}, cmd = true, sql = [[
			delete from parsav_auth where
................................................................................
			rawstring, rawstring, rawstring;
		}, cmd = true, sql = [[
			update parsav_posts set
				subject = $4::text,
				acl = $5::text,
				body = $6::text,
				chgcount = $2::integer,
				edited = $3::bigint
			where id = $1::bigint
		]]
	};

	post_create = {
		params = {
			uint64, rawstring, rawstring, rawstring,
................................................................................
			insert into parsav_posts (
				author, subject, acl, body,
				parent, posted, discovered,
				circles, mentions, convoheaduri
			) values (
				$1::bigint, case when $2::text = '' then null else $2::text end,
				$3::text, $4::text, 
				$5::bigint, $6::bigint, $6::bigint,
				array[]::bigint[], array[]::bigint[], $7::text
			) returning id
		]]; -- TODO array handling
	};

	post_destroy_prepare = {
		params = {uint64}, cmd = true, sql = [[
................................................................................
		params = {uint64}, cmd = true, sql = [[
			delete from parsav_posts where id = $1::bigint
		]]
	};
	
	post_fetch = {
		params = {uint64}, sql = [[
			select (p.post).*
			from pg_temp.parsavpg_known_content as p

















				where (p.post).id = $1::bigint and (p.post).rtdby = 0
		]]
	};

	post_enum_parent = {
		params = {uint64}, sql = [[



















			select (p.post).*
			from pg_temp.parsavpg_known_content as p
				where (p.post).parent = $1::bigint and (p.post).rtdby = 0

				order by (p.post).posted, (p.post).discovered asc
		]];
	};

	thread_latest_arrival_calc = {
		params = {uint64}, sql = [[
			with recursive posts(id) as (
				select id from parsav_posts where parent = $1::bigint
			union
................................................................................
			maxes as (
				select unnest(array[max(p.posted), max(p.discovered), max(p.edited)]) as m
					from posts
					inner join parsav_posts as p
						on p.id = posts.id
			)

			select max(m)::bigint from maxes
		]];
	};

	post_react_simple = {
		params = {uint64, uint64, pstring, int64}, sql = [[
			insert into parsav_acts (kind,actor,subject,time) values (
				$3::text, $1::bigint, $2::bigint, $4::bigint
			) returning id
		]];
	};

	post_react_cancel = {
		params = {uint64, uint64, pstring}, cmd = true, sql = [[
			delete from parsav_acts where
................................................................................
				($2::bigint = 0 or subject = $2::bigint) and
				($3::text is null or kind  = $3::text  )
		]]
	};

	post_enum_author_uid = {
		params = {uint64,uint64,uint64,uint64, uint64}, sql = [[
			select (c.post).*
			from pg_temp.parsavpg_known_content as c








			where c.promoter = $5::bigint and


				($1::bigint = 0 or c.tltime   <= $1::bigint) and
				($2::bigint = 0 or $2::bigint <  c.tltime)

			order by c.tltime desc


























			limit case when $3::bigint = 0 then null
					   else $3::bigint end
			offset $4::bigint
		]];
	};

	-- maybe there's some way to unify these two, idk, im tired

	timeline_instance_fetch = {
		params = {uint64, uint64, uint64, uint64}, sql = [[

			select (c.post).*






			from pg_temp.parsavpg_known_content as c






			where (c.post).localpost = true and
				($1::bigint = 0 or c.tltime   <= $1::bigint) and
				($2::bigint = 0 or $2::bigint <  c.tltime)


			order by c.tltime desc

			limit case when $3::bigint = 0 then null
					   else $3::bigint end
			offset $4::bigint




		]];
	};

	timeline_actor_fetch = {
		params = {uint64, uint64, uint64, uint64, uint64}, sql = [[
			with followed as (
				select relatee from parsav_rels where
					kind = <rel:follow> and
					relator = $1::bigint
			), avoided as (
				select relatee as avoidee from parsav_rels where
					kind = <rel:avoid> or kind = <rel:mute> and
					relator = $1::bigint
				union select relator as avoidee from parsav_rels where
					kind = <rel:exclude> and
					relatee = $1::bigint
			)

			select (c.post).*



			from pg_temp.parsavpg_known_content as c

			where ($2::bigint = 0 or c.tltime   <= $2::bigint) and
				  ($3::bigint = 0 or $3::bigint <  c.tltime) and
				  (c.promoter in (table followed) or
				   c.promoter = $1::bigint) and
				  not ((c.post).author in (table avoided))
			order by c.tltime desc

			limit case when $4::bigint = 0 then null
					   else $4::bigint end
			offset $5::bigint
		]];
	};

	artifact_instantiate = {
		params = {binblob, binblob, pstring}, sql = [[
			insert into parsav_artifacts (content,hash,mime) values (
				$1::bytea, $2::bytea, $3::text
			) on conflict do nothing returning id
................................................................................
			buf[2] = tycode
			buf[3] = sz
			for j=0,sz do buf[4 + j] = i.v6[j] end -- 😬
			return buf
		end
	end;
}

local sqlvars = {}
for i, n in ipairs(lib.store.noticetype.members) do
	sqlvars['notice:' .. n] = lib.store.noticetype[n]
end

for i, n in ipairs(lib.store.relation.members) do
	sqlvars['rel:' .. n] = lib.store.relation.idvmap[n]
end

local con = symbol(&lib.pq.PGconn)

local function sqlsquash(s) return s
	:gsub('%%include (.-)%%',function(f)
		return sqlsquash(lib.util.ingest('backend/schema/' .. f))
	end) -- include dependencies
	:gsub('%-%-.-\n','') -- remove disruptive line comments
	:gsub('%-%-.-$','') -- remove unnecessary terminal comments
	:gsub('<(%g-)>',function(r) return tostring(sqlvars[r]) end)
	:gsub('%s+',' ') -- remove whitespace
	:gsub('^%s*(.-)%s*$','%1') -- chomp
end

-- to simplify queries and reduce development headaches in general, we
-- offload as much logic as possible into views. to avoid versioning
-- difficulties, these views are not part of the schema, but are rather
-- uploaded to the database at the start of a parsav connection, visible
-- only to the connecting parsav instance, stored in memory, and dropped
-- as soon as the connection session ends.

local tempviews = sqlsquash(lib.util.ingest 'backend/schema/pgsql-views.sql')
local prep = { quote
	var res = lib.pq.PQexec([con], tempviews)
	if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
		lib.dbg('uploading pgsql session views')
	else
		lib.bail('backend pgsql - failed to upload session views: \n', lib.pq.PQresultErrorMessage(res))
	end
end }

for k,q in pairs(queries) do
	local qt = sqlsquash(q.sql)
	local stmt = 'parsavpg_' .. k
	terra q.prep([con])
		var res = lib.pq.PQprepare([con], stmt, qt, [#q.params], nil)
		defer lib.pq.PQclear(res)
................................................................................
		lib.dbg(['searching for hashed password credentials in format SHA' .. tostring(hash)])
		var [out]
		[vdrs]
		lib.dbg(['could not find password hash'])
	end
end

local schema = sqlsquash(lib.util.ingest 'backend/schema/pgsql.sql')
local obliterator = sqlsquash(lib.util.ingest 'backend/schema/pgsql-drop.sql')

local privupdate = terra(
	src: &lib.store.source,
	ac: &lib.store.actor
): {}
	var pdef: lib.store.powerset pdef:clear()
	var map = array([privmap])
................................................................................
		return con
	end];

	close = [terra(src: &lib.store.source) lib.pq.PQfinish([&lib.pq.PGconn](src.handle)) end];

	tx_enter = txdo, tx_complete = txdone;

	conprep = [terra(src: &lib.store.source, mode: lib.store.prepmode.t): {}
		var [con] = [&lib.pq.PGconn](src.handle)
		if mode == lib.store.prepmode.full then [prep]
		elseif mode == lib.store.prepmode.conf or
		       mode == lib.store.prepmode.admin then 
			queries.conf_get.prep(con)
			queries.conf_set.prep(con)
			queries.conf_reset.prep(con)
			if mode == lib.store.prepmode.admin then 
			end
		else lib.bail('unsupported connection preparation mode') end
	end];

	dbsetup = [terra(src: &lib.store.source): bool
		var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), schema)
		if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
			lib.report('successfully instantiated schema in database')
			return true
		else
			lib.warn('backend pgsql - failed to initialize database: \n', lib.pq.PQresultErrorMessage(res))
			return false
		end
	end];

	obliterate_everything = [terra(src: &lib.store.source): bool
		var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), obliterator)
		if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
			lib.report('successfully wiped out everything parsav-related in database')
			return true
		else
			lib.warn('backend pgsql - failed to obliterate database: \n', lib.pq.PQresultErrorMessage(res))
			return false
................................................................................

			var a = row_to_actor(&r, 0)
			a.ptr.source = src

			var au = [lib.stat(lib.store.auth)] { ok = true }
			au.val.aid = aid
			au.val.uid = a.ptr.id
			if not r:null(0,13) then -- restricted?
				au.val.privs:clear()
				(au.val.privs.post    << r:bool(0,14)) 
				(au.val.privs.edit    << r:bool(0,15))
				(au.val.privs.account << r:bool(0,16))
				(au.val.privs.upload  << r:bool(0,17))
				(au.val.privs.moderate<< r:bool(0,18))
				(au.val.privs.admin   << r:bool(0,19))
			else au.val.privs:fill() end

			return au, a
		end

		::fail:: return [lib.stat   (lib.store.auth) ] { ok = false        },
			            [lib.mem.ptr(lib.store.actor)] { ptr = nil, ct = 0 }
................................................................................

	post_retweet = [terra(
		src: &lib.store.source,
		uid: uint64,
		post: uint64,
		undo: bool
	): {}
		var time = lib.osclock.time(nil)
		if not undo then
			queries.post_react_simple.exec(src,uid,post,"rt",time)
		else
			queries.post_react_cancel.exec(src,uid,post,"rt")
		end
	end];
	post_like = [terra(
		src: &lib.store.source,
		uid: uint64,
		post: uint64,
		undo: bool
	): {}
		var time = lib.osclock.time(nil)
		if not undo then
			queries.post_react_simple.exec(src,uid,post,"like",time)
		else
			queries.post_react_cancel.exec(src,uid,post,"like")
		end
	end];
	post_liked_uid = [terra(
		src: &lib.store.source,
		uid: uint64,
................................................................................
		-- check against default rights, insert records for wherever powers differ
		lib.dbg('created new actor, establishing powers')
		privupdate(src,ac)

		lib.dbg('powers established')
		return ac.id
	end];

	actor_rel_create = [terra(
		src: &lib.store.source,
		kind:    uint16,
		relator: uint64,
		relatee: uint64
	): {} queries.actor_rel_create.exec(src,kind,relator,relatee) end];

	actor_rel_destroy = [terra(
		src: &lib.store.source,
		kind:    uint16,
		relator: uint64,
		relatee: uint64
	): {} queries.actor_rel_destroy.exec(src,kind,relator,relatee) end];

	actor_rel_calc = [terra(
		src: &lib.store.source,
		relator: uint64,
		relatee: uint64
	): lib.store.relationship
		var r = lib.store.relationship {
			agent = relator, patient = relatee
		} r.rel:clear()
		  r.recip:clear()

		var res = queries.actor_rel_enum.exec(src,relator,relatee)
		var recip = queries.actor_rel_enum.exec(src,relatee,relator)

		if res.sz > 0 then defer res:free()
			for i = 0, res.sz do
				var bit = res:int(uint16, i, 0)-1
				if bit < [#lib.store.relation.members] then r.rel:setbit(bit, true)
					else lib.warn('unknown relationship type in database') end
			end
		end

		if recip.sz > 0 then defer recip:free()
			for i = 0, recip.sz do
				var bit = recip:int(uint16, i, 0)-1
				if bit < [#lib.store.relation.members] then r.recip:setbit(bit, true)
					else lib.warn('unknown relationship type in database') end
			end
		end

		return r
	end];

	actor_purge_uid = [terra(
		src: &lib.store.source,
		uid: uint64
	) queries.actor_purge_uid.exec(src,uid) end];

	auth_enum_uid = [terra(
................................................................................
	): {}
		var hash: uint8[lib.crypt.algsz.sha256]
		if lib.md.mbedtls_md(lib.md.mbedtls_md_info_from_type(lib.crypt.alg.sha256.id),
			[&uint8](pw.ptr), pw.ct, &hash[0]) ~= 0 then
			lib.bail('cannot hash password')
		end
		if reset then queries.auth_purge_type.exec(src, nil, uid, 'pw-%') end
		queries.auth_create_pw.exec(src, uid, binblob {ptr = &hash[0], ct = [hash.type.N]}, lib.osclock.time(nil), comment)
	end];

	auth_purge_pw = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}
		queries.auth_purge_type.exec(src, handle, uid, 'pw-%')
	end];

	auth_purge_otp = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}